SQL 코테 준비 (4월 3주)(리트코드)

4.10

1) Managers with at least 5 Direct Reports

select e2.name
from employee as e1
join employee as e2 on e1.managerId = e2.id
group by e1.managerId
having count(*) >= 5

2) Investments in 2016

select round(sum(tiv_2016),2) as tiv_2016
from insurance
where tiv_2015 in (select tiv_2015 from insurance group by tiv_2015 having count(*) >= 2)
and (lat, lon) in (select lat, lon from insurance group by lat, lon having count(*) = 1)

4.11

1) Friends Request II : Who has the most friends

SELECT id, COUNT(*) AS num
FROM (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id FROM RequestAccepted
) AS total
GROUP BY id
ORDER BY num DESC
LIMIT 1

2) Tree node

select id,
       case    
            when p_id IS NULL THEN 'Root'
            when id NOT IN (select DISTINCT p_id
                            from tree
                            where p_id IS NOT NULL) THEN 'Leaf'
            ELSE 'Inner'
        END as type
from tree

4.12

1) Exchange seats

with s as (
    select *
         , max(id) over () as last_id
    from seat
)

select case
            when mod(id, 2) = 1 and last_id != id then id+1
            when mod(id, 2) = 1 and last_id = id then id
            else id-1
        end as id
        , student
from s
order by id

2) Customers who bought all products

select customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(*) from product)

4.13

1) Product Sales Analysis

SELECT product_id, 
       year AS first_year, 
       quantity, 
       price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year)
    FROM Sales
    GROUP BY product_id
)

2) Marktet Analysis I

select u.user_id as buyer_id,
       u.join_date,
       sum(case when o.order_date 
                between '2019-01-01' and '2019-12-31' then 1
                else 0 end) as orders_in_2019
from users u
left join orders o on u.user_id = o.buyer_id
group by u.user_id

4.14

1) Prouct Price at a Given date

select distinct product_id, 10 as price 
from Products 
where product_id not in 
    (select distinct product_id from Products where change_date <='2019-08-16' )
union 
select product_id, new_price as price 
from Products 
where (product_id, change_date) in 
    (select product_id , max(change_date) as date from Products 
    where change_date <='2019-08-16' group by product_id)

4.15

1) Immediate Food Delivery II

select round(avg(customer_pref_delivery_date = order_date)*100,2) as immediate_percentage 
from delivery
where (customer_id, order_date) in 
    (select customer_id, min(order_date) from delivery group by 1)

4.16

1) Monthly Transactions I

select date_format(trans_date, '%Y-%m') as month
        , country
        , count(*) as trans_count
        , sum(case when state = 'approved' then 1 else 0 end) as approved_count
        , sum(amount) as trans_total_amount
        , sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from transactions
group by month, country