250523 TIL

수이·2025년 5월 27일

Data_6기 sql 내일배움캠프

🟡 TIL

목록 보기

57/60

개인스터디

QCC 6회차

문제 1 ) 성별 기준 상위 3명 출력 🟢

풀이

SELECT gender, name, score
FROM(SELECT gender, name, age, score, 
            ROW_NUMBER() OVER (PARTITION BY gender ORDER BY score DESC, age DESC) AS rn     
    FROM students) AS rnk
WHERE rn <= 3

1️⃣ 해설

with ranking as ( 
    select *,
      row_number() over (partition by gender order by score desc, age desc) rn  
    from students
)
select gender, name, score 
from ranking 
where rn <= 3
order by 1, rn

문제 2 ) 도서 미결제 금액, 결제완료 금액 🔴

풀이

SELECT b.title,
       ROUND(SUM(CASE WHEN o.paid_date IS NULL THEN i.line_total ELSE 0 END)) AS DUE,
       ROUND(SUM(CASE WHEN o.paid_date IS NOT NULL THEN i.line_total ELSE 0 END)) AS PAID
FROM book_order_items i
INNER JOIN books b ON i.book_id = b.id
INNER JOIN book_orders o ON i.order_id = o.id
GROUP BY b.title
ORDER BY b.title

inner join > left join
coalsce 처리

이렇게 해줬으면 정답인데.. 아쉬버라

끝나갈때쯤 null인 경우 0으로 처리해달라는 공지를 보고 나름대로는 한번 더 확인했던 것 같은데 inner join 이어서 애초에 전제가 잘못됐다

2️⃣ 해설

select b.title, 
  round(sum(coalesce((paid_date is null) * boi.line_total, 0)),0) as due, 
  round(sum(coalesce((paid_date is not null) * boi.line_total, 0)),0) as paid
from books b 
left join book_order_items boi on b.id = boi.book_id 
left join book_orders bo on boi.order_id = bo.id
group by 1
order by 1

문제 3 ) 코호트 분석 🟢

풀이

-- 첫주문 구하기
WITH first_order AS(
  SELECT user_id, MIN(order_date) AS first_order_date
  FROM orders 
  WHERE order_date BETWEEN '2023-01-01' AND '2023-07-01'
  GROUP BY user_id
  ),
-- 몇 개월 지나고 다시 구매했는지 구하기 + 6개월까지만 필터링 
cohort AS(
  SELECT o.user_id, DATE_FORMAT(fo.first_order_date, '%Y-%m') AS first_order_month, 
       TIMESTAMPDIFF(MONTH, fo.first_order_date, o.order_date) AS months_after
  FROM orders o 
  INNER JOIN first_order fo ON o.user_id = fo.user_id
  WHERE TIMESTAMPDIFF(MONTH, fo.first_order_date, o.order_date) BETWEEN 0 AND 6
)

SELECT first_order_month, months_after, COUNT(DISTINCT user_id) AS active_users
FROM cohort
GROUP BY 1,2
ORDER BY 1,2

3️⃣ 해설

with cohort as (
  select user_id
    , min(order_date) first_order_date
    , min(date_format(order_date, '%Y-%m')) first_order_month 
  from orders
  group by 1
) 
select 
    c.first_order_month
    , timestampdiff(month, c.first_order_date, o.order_date) months_after
    , count(distinct o.user_id) active_user
from cohort c 
join orders o 
on c.user_id = o.user_id
where timestampdiff(month, c.first_order_date, o.order_date) between 0 and 6 
and c.first_order_month between '2023-01' and '2023-06'
group by 1, 2
order by 1, 2