SQL 문제풀이 복습
문제 링크
recursive cte를 써야 해서 약간 까다로운 문제.
이 문제의 킥은
cte에서 week_no를 만들 때 row_number() over()를 쓸 줄 알아야 하는 점. over() 안에 partition by나 order by를 쓰지 않고도 그냥 row_number가 1부터 자동으로 생성된다.
date_add(DATE, interval 7 day)를 쓸 줄 알아야 하는 점. 이 때 7 days가 아니라 그냥 7 day다. 이걸 몰라서 계속 오류로 머리를 싸매고 있었(...)
cte만 잘 만들 수 있으면
그 뒤부터는 join만 하면 돼서 크게 어렵지 않다.
WITH recursive all_friday
AS
(
SELECT row_number() over() AS "week_no",
'2023-11-03' AS "purchase_date"
UNION ALL
SELECT week_no+1,
date_add(purchase_date, INTERVAL 7 DAY)
FROM all_friday
WHERE purchase_date <= '2023-11-17')
SELECT a.week_no AS "week_of_month",
p.purchase_date,
SUM(amount_spend) AS "total_amount"
FROM Purchases p
JOIN all_friday a
ON p.purchase_date = a.purchase_date
GROUP BY 1
ORDER BY 1;
문제 링크
위의 문제와 cte까지는 완전히 똑같은데,
amount_spend가 없는 주는 0으로 표기해야 하므로
inner가 아니라 left join으로 변경해야 하고,
join의 순서도 (inner가 아니므로) cte를 기준으로 두고
Puchases를 left join하는 식으로 변경해야 한다.
WITH recursive all_friday
AS
(
SELECT row_number() over() AS "week_no",
'2023-11-03' AS "purchase_date"
UNION ALL
SELECT week_no+1,
date_add(purchase_date, INTERVAL 7 DAY)
FROM all_friday
WHERE purchase_date <= '2023-11-17')
SELECT a.week_no AS "week_of_month",
a.purchase_date,
ifnull(SUM(amount_spend),0) AS "total_amount"
FROM all_friday a
LEFT JOIN Purchases p
ON p.purchase_date = a.purchase_date
GROUP BY 1
ORDER BY 1;
문제 링크
단계별로 나눠서 해결하면 어렵지 않다.
rank()를 써서 세션 순서별로 번호를 매긴다.
1번 세션(가장 빠른)의 session_type이 Viewer인 유저의 user_id를 뽑는다.
그걸 다시 Sessions 테이블과 inner join해서 2번에서 뽑은 user_id의 데이터만 남긴다.
이제 session_type이 Streamer인 경우만 추려서 가짓수를 세면 끝. (이미 첫 세션이 Viewer인 것은 2번까지의 과정을 통해 식별되어 있고, 해당하는 user_id만 넘어온 상태이므로)
SELECT s.user_id,
Count(session_id) AS "sessions_count"
FROM Sessions s
JOIN (SELECT user_id
FROM (SELECT user_id,
session_start,
session_type,
RANK()
OVER(
partition BY user_id
ORDER BY session_start) AS "ranking"
FROM Sessions) a
WHERE session_type = 'Viewer'
AND ranking = '1') b
ON s.user_id = b.user_id
WHERE session_type = 'Streamer'
GROUP BY 1
ORDER BY 2 DESC,
1 DESC;
컨디션이 회복되니 medium, hard 문제로만 풀었는데도
시간이 오래 걸리지 않네. 컨디션 관리 잘 하자 앞으로도!