1

[woneuy01]

Ads table: +-------+---------+---------+ | ad_id | user_id | action | +-------+---------+---------+ | 1 | 1 | Clicked | | 2 | 2 | Clicked | | 3 | 3 | Viewed | | 5 | 5 | Ignored | | 1 | 7 | Ignored | | 2 | 7 | Viewed | | 3 | 5 | Clicked | | 1 | 4 | Viewed | | 2 | 11 | Viewed | | 1 | 2 | Clicked | +-------+---------+---------+ Result table: +-------+-------+ | ad_id | ctr | +-------+-------+ | 1 | 66.67 | | 3 | 50.00 | | 2 | 33.33 | | 5 | 0.00 | +-------+-------+ for ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67

Write your MySQL query statement below

with actions as ( select ad_id, sum(CASE when action="Clicked" then 1 Else 0 End) as clicked,

sum(CASE when action="Viewed" then 1 Else 0 End) as viewed from Ads group by 1 )

select ad_id, round(coalesce(actions.clicked/(actions.clicked+actions.viewed)*100,0),2) as ctr from actions order by ctr DESC

[woneuy01]

Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+

Result table: +-----------+-------------+ | player_id | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+

select player_id, min(event_date) as first_login from Activity group by player_id

[woneuy01]

Departments table: +------+--------------------------+ | id | name | +------+--------------------------+ | 1 | Electrical Engineering | | 7 | Computer Engineering | | 13 | Bussiness Administration | +------+--------------------------+

Students table: +------+----------+---------------+ | id | name | department_id | +------+----------+---------------+ | 23 | Alice | 1 | | 1 | Bob | 7 | | 5 | Jennifer | 13 | | 2 | John | 14 | | 4 | Jasmine | 77 | | 3 | Steve | 74 | | 6 | Luis | 1 | | 8 | Jonathan | 7 | | 7 | Daiana | 33 | | 11 | Madelynn | 1 | +------+----------+---------------+

Result table: +------+----------+ | id | name | +------+----------+ | 2 | John | | 7 | Daiana | | 4 | Jasmine | | 3 | Steve | +------+----------+

select id, name from Students where department_id not in (select id from Departments)

[woneuy01]

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2),
PRIMARY KEY (ID, NAME) );

[woneuy01]

Warehouse table: +------------+--------------+-------------+ | name | product_id | units | +------------+--------------+-------------+ | LCHouse1 | 1 | 1 | | LCHouse1 | 2 | 10 | | LCHouse1 | 3 | 5 | | LCHouse2 | 1 | 2 | | LCHouse2 | 2 | 2 | | LCHouse3 | 4 | 1 | +------------+--------------+-------------+

Products table: +------------+--------------+------------+----------+-----------+ | product_id | product_name | Width | Length | Height | +------------+--------------+------------+----------+-----------+ | 1 | LC-TV | 5 | 50 | 40 | | 2 | LC-KeyChain | 5 | 5 | 5 | | 3 | LC-Phone | 2 | 10 | 10 | | 4 | LC-T-Shirt | 4 | 10 | 20 | +------------+--------------+------------+----------+-----------+

Result table: +----------------+------------+ | warehouse_name | volume | +----------------+------------+ | LCHouse1 | 12250 | | LCHouse2 | 20250 | | LCHouse3 | 800 | +----------------+------------+

select w.name as WAREHOUSE_NAME, sum(WidthLengthHeight*units) as VOLUME from warehouse w left join Products p on w.product_id=p.product_id group by 1 order by w.name, w.product_id

[woneuy01]

Patients +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 1 | Daniel | YFEV COUGH | | 2 | Alice | | | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | | 5 | Alain | DIAB201 | +------------+--------------+--------------+

Result table: +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | +------------+--------------+--------------+ Bob and George both have a condition that starts with DIAB1.

select patient_id, patient_name, conditions from patients where conditions LIKE '%DIAB1%'

[woneuy01]

Employee Table: +-------------+------------+ | employee_id | team_id | +-------------+------------+ | 1 | 8 | | 2 | 8 | | 3 | 8 | | 4 | 7 | | 5 | 9 | | 6 | 9 | +-------------+------------+ Result table: +-------------+------------+ | employee_id | team_size | +-------------+------------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | | 4 | 1 | | 5 | 2 | | 6 | 2 | +-------------+------------+

with tem as( select team_id,count(employee_id) as team_size from Employee group by team_id ) select e.employee_id, tem.team_size from employee e left join tem on e.team_id=tem.team_id

[woneuy01]

sell_date | product | +------------+-------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+-------------+

Result table: +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask |

Write your MySQL query statement below

select sell_date, count(distinct(product)) as num_sold, group_concat(distinct(product)) as products from Activities group by sell_date order by sell_date, num_sold desc, product

[woneuy01]

Prices table: +------------+------------+------------+--------+ | product_id | start_date | end_date | price | +------------+------------+------------+--------+ | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | +------------+------------+------------+--------+

UnitsSold table: +------------+---------------+-------+ | product_id | purchase_date | units | +------------+---------------+-------+ | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | +------------+---------------+-------+

Result table: +------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+

select p.product_id, round(sum(p.price*u.units)/ sum(u.units),2) as average_price from prices p left join unitssold u on p.product_id=u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date group by 1

[woneuy01]

Orders +----------+------------+-------------+------------+ | order_id | order_date | customer_id | invoice | +----------+------------+-------------+------------+ | 1 | 2020-09-15 | 1 | 30 | | 2 | 2020-09-17 | 2 | 90 | | 3 | 2020-10-06 | 3 | 20 | | 4 | 2020-10-20 | 3 | 21 | | 5 | 2020-11-10 | 1 | 10 | | 6 | 2020-11-21 | 2 | 15 | | 7 | 2020-12-01 | 4 | 55 | | 8 | 2020-12-03 | 4 | 77 | | 9 | 2021-01-07 | 3 | 31 | | 10 | 2021-01-15 | 2 | 20 | +----------+------------+-------------+------------+

Result table: +---------+-------------+----------------+ | month | order_count | customer_count | +---------+-------------+----------------+ | 2020-09 | 2 | 2 | | 2020-10 | 1 | 1 | | 2020-12 | 2 | 1 | | 2021-01 | 1 | 1 |

select DATE_FORMAT(order_date, '%Y-%m') as month, count(customer_id) as order_count, count(distinct customer_id) as customer_count %Y 대문자일대는 연도가 4자리 1998, %y 소문자일때는 2자리 98 from orders where invoice>20 group by 1

[woneuy01]

Department table: +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+

Result table: +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+

Note that the result table has 13 columns (1 for the department id + 12 for the months).'

select id, sum(case when month = 'Jan' then revenue else null end) as Jan_Revenue, sum(case when month = 'Feb' then revenue else null end) as Feb_Revenue, sum(case when month = 'Mar' then revenue else null end) as Mar_Revenue, sum(case when month = 'Apr' then revenue else null end) as Apr_Revenue, sum(case when month = 'May' then revenue else null end) as May_Revenue, sum(case when month = 'Jun' then revenue else null end) as Jun_Revenue, sum(case when month = 'Jul' then revenue else null end) as Jul_Revenue, sum(case when month = 'Aug' then revenue else null end) as Aug_Revenue, sum(case when month = 'Sep' then revenue else null end) as Sep_Revenue, sum(case when month = 'Oct' then revenue else null end) as Oct_Revenue, sum(case when month = 'Nov' then revenue else null end) as Nov_Revenue, sum(case when month = 'Dec' then revenue else null end) as Dec_Revenue
from Department

[woneuy01]

If the preferred delivery date of the customer is the same as the order date then the order is called immediate otherwise it's called scheduled.

Write an SQL query to find the percentage of immediate orders in the table, rounded to 2 decimal places.

The query result format is in the following example:

Delivery table: +-------------+-------------+------------+-----------------------------+ | delivery_id | customer_id | order_date | customer_pref_delivery_date | +-------------+-------------+------------+-----------------------------+ | 1 | 1 | 2019-08-01 | 2019-08-02 | | 2 | 5 | 2019-08-02 | 2019-08-02 | | 3 | 1 | 2019-08-11 | 2019-08-11 | | 4 | 3 | 2019-08-24 | 2019-08-26 | | 5 | 4 | 2019-08-21 | 2019-08-22 | | 6 | 2 | 2019-08-11 | 2019-08-13 | +-------------+-------------+------------+-----------------------------+

Result table: +----------------------+ | immediate_percentage | +----------------------+ | 33.33 | +----------------------+

with del as ( select delivery_id, case when order_date=customer_pref_delivery_date then 1 else 0 end as deli from delivery )

select round(sum(del.deli)/count(del.delivery_id)*100,2) as immediate_percentage from del

[woneuy01]

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.

Write a query to find the shortest distance between two points in these points.

x
-1
0
2

The shortest distance is '1' obviously, which is from point '-1' to '0'. So the output is as below:

shortest
1

select min(abs(a.x-b.x)) shortest from point a join point b where a.x!=b.x

[woneuy01]

Write an SQL query to get the names of products with greater than or equal to 100 units ordered in February 2020 and their amount.

Return result table in any order.

The query result format is in the following example:

Products table: +-------------+-----------------------+------------------+ | product_id | product_name | product_category | +-------------+-----------------------+------------------+ | 1 | Leetcode Solutions | Book | | 2 | Jewels of Stringology | Book | | 3 | HP | Laptop | | 4 | Lenovo | Laptop | | 5 | Leetcode Kit | T-shirt | +-------------+-----------------------+------------------+

Orders table: +--------------+--------------+----------+ | product_id | order_date | unit | +--------------+--------------+----------+ | 1 | 2020-02-05 | 60 | | 1 | 2020-02-10 | 70 | | 2 | 2020-01-18 | 30 | | 2 | 2020-02-11 | 80 | | 3 | 2020-02-17 | 2 | | 3 | 2020-02-24 | 3 | | 4 | 2020-03-01 | 20 | | 4 | 2020-03-04 | 30 | | 4 | 2020-03-04 | 60 | | 5 | 2020-02-25 | 50 | | 5 | 2020-02-27 | 50 | | 5 | 2020-03-01 | 50 | +--------------+--------------+----------+

Result table: +--------------------+---------+ | product_name | unit | +--------------------+---------+ | Leetcode Solutions | 130 | | Leetcode Kit | 100 | +--------------------+---------+

Write your MySQL query statement below

select p.product_name PRODUCT_NAME, sum(o.unit) UNIT from products p left join orders o on p.product_id = o.product_id where order_date between '2020-02-01' and '2020-02-29' group by 1 having UNIT >=100

[woneuy01]

+-------------+---------------+ | session_id | duration | +-------------+---------------+ | 1 | 30 | | 2 | 199 | | 3 | 299 | | 4 | 580 | | 5 | 1000 | +-------------+---------------+

Result table: +--------------+--------------+ | bin | total | +--------------+--------------+ | [0-5> | 3 | | [5-10> | 1 | | [10-15> | 0 | | 15 or more | 1 | +--------------+--------------+

Write your MySQL query statement below

WITH cte AS ( SELECT duration/60 AS mins FROM sessions )

select '[0-5>' as bin, sum(case when mins >=0 and mins<5 then 1 else 0 end) as total from cte union all select '[5-10>' as bin, sum(case when cte.mins >=5 and mins<10 then 1 else 0 end) as total from cte union all select '[10-15>' as bin, sum(case when cte.mins >=10 and mins<15 then 1 else 0 end) as total from cte union all select '15 or more' as bin, sum(case when cte.mins >15 then 1 else 0 end) as total from cte

[woneuy01]

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

After running your update statement, the above salary table should have the following rows:	id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

update salary set sex= case sex when 'm' then 'f' else 'm' end;

[woneuy01]

SELECT customer_id, name FROM ( SELECT c.customer_id, c.name, SUM(CASE WHEN LEFT(o.order_date, 7) = '2020-06' THEN p.priceo.quantity ELSE 0 END) AS t1, SUM(CASE WHEN LEFT(o.order_date, 7) = '2020-07' THEN p.priceo.quantity ELSE 0 END) AS t2 FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN product p ON p.product_id = o.product_id GROUP BY 1 ) tmp WHERE t1 >= 100 AND t2 >= 100

[woneuy01]

Query the customer_number from the orders table for the customer who has placed the largest number of orders.

order_number	customer_number	order_date	required_date	shipped_date	status	comment
1	1	2017-04-09	2017-04-13	2017-04-12	Closed
2	2	2017-04-15	2017-04-20	2017-04-18	Closed
3	3	2017-04-16	2017-04-25	2017-04-20	Closed
4	3	2017-04-18	2017-04-28	2017-04-25	Closed

Sample Output

customer_number
3

with con as (select customer_number, count(customer_number) num from orders group by 1 )

select con.customer_number as customer_number from con where con.num=(select max(con.num) from con)