20260410 TIL

[Today I Learn]

- SQL codekata

---

[SQL codekata]

- 문제 1.

1. 문제 링크: https://leetcode.com/problems/managers-with-at-least-5-direct-reports/submissions/1974255891/

2. 정답 코드 

select name 
from employee 
where id in (
    select managerId
    from employee 
    group by managerId
    having count(*)>=5
)

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

with over_five as (
    select managerId 
    from employee 
    group by managerId
    having count(*) >= 5 
) 
select name 
from employee 
where id in ( 
    select managerId 
    from over_five
)

 

- 문제 2.

1. 문제 링크: https://leetcode.com/problems/confirmation-rate/description/

2. 정답 코드

with rate as ( 
    select
        user_id, 
        sum(case action when 'confirmed' then 1 else 0 end) as confirm_cnt, 
        count(*) as total_cnt 
    from confirmations 
    group by user_id
) 
select
    s.user_id, 
    ifnull(round(r.confirm_cnt/r.total_cnt,2),0) as confirmation_rate 
from signups as s
left join rate as r
on s.user_id = r.user_id

with rate as ( 
    select
        user_id, 
        case action when 'confirmed' then 1 else 0 end as is_confirm
    from confirmations 
) 
select
    s.user_id, 
    ifnull(round(avg(is_confirm),2),0) as confirmation_rate 
from signups as s
left join rate as r
on s.user_id = r.user_id
group by s.user_id

select
    s.user_id, 
    ifnull(round(avg(case c.action when 'confirmed' then 1 else 0 end),2),0) as confirmation_rate 
from signups as s
left join confirmations as c
on s.user_id = c.user_id
group by s.user_id

• avg() 집계함수를 활용해 0과 1로 구성된 컬럼의 평균을 구함으로써 해당하는 건의 '비율' 값 획득
• 굳이 cte를 활용하지 않아도 될 땐 해당 select 인자를 직접 함수 안에 넣음으로써 깔끔하게 쿼리 정리

 

- 문제 3.

1. 문제 링크: https://leetcode.com/problems/average-selling-price/

2. 정답 코드: 

with filtered as ( 
    select 
        p.product_id, 
        p.price, 
        u.units 
    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)
)
select product_id, ifnull(round(sum(price*units)/sum(units),2),0) as average_price
from filtered  
group by product_id

• left join을 한뒤 on 절에서 조건절을 and로 추가하는 것과 where 절에서 조건절을 추가하는 것은 다르다!
  • where절에 조건절을 추가하면 그 조건에 해당하지 않는 건을 추출되지 않는다
  • on절에서 and로 추가하면 그 조건에 해당하지 않는 건은 Null로 추출된다
  • 이 문제에선 Null대신 0값으로 결과가 출력되어야 하므로 ifnull 함수 사용

- 문제 4.

1. 문제 링크: https://leetcode.com/problems/product-sales-analysis-iii/description/

2. 정답 코드

with first_year as ( 
    select 
        product_id, 
        year,
        rank() over (partition by product_id order by year asc) as year_order,
        quantity,
        price
    from sales
)
select product_id, year as first_year, sum(quantity) as quantity, price
from first_year
where year_order=1
group by 1,2,4

 

- 문제 5.

1. 문제 링크: https://leetcode.com/problems/customers-who-bought-all-products/description/

2. 정답 코드 

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

 

- 문제 6

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/131124

2. 정답 코드 

with top_reviewer as ( 
    select member_id, count(*) as cnt 
    from rest_review 
    group by member_id
    order by cnt desc 
    limit 1
)
select
    m.member_name, 
    r.review_text, 
    date_format(r.review_date, '%Y-%m-%d') as review_date 
from member_profile m
join rest_review r
on m.member_id = r.member_id
where exists ( 
    select 1 
    from top_reviewer t
    where t.member_id = m.member_id
)
order by 3 asc, 2 asc

 

- 문제 7

1. 문제 링크: https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/description/

2. 정답 코드

with reported as ( 
    select reports_to, count(*) as reports_count, avg(age) as average_age
    from employees 
    group by reports_to
)
select e.employee_id, e.name, r.reports_count, round(r.average_age,0) as average_age
from employees e 
join reported r 
on e.employee_id = r.reports_to 
order by e.employee_id