QCC 6회차

문제 1

각 성별 기준으로 시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수 출력
성별, 순위 오름차순 정렬
두 학생이 동점일 경우 나이가 많은 학생이 더 순위가 높다.

-- 성별기준, 시험점수 상위 3명
-- 성별, 이름, 점수 반환
-- 성별, 순위 asc, 동점처리 나이많은순
with rnk_list as(
  select 
    *,
    row_number() over(partition by gender order by score desc, age desc) as rnk
  from 
    students
  )
select
  gender,
  name,
  score
from 
  rnk_list
where rnk between 1 and 3
order by gender, rnk asc

문제 2

-- 제목, 미결제 금액, 결제완료 금액 출력
-- 미결제 금액 = 아직 결제 안된 총액, 정수 (paid_date = null)
-- 결제완료 금액 = 정수
-- 제목 asc
-- books (도서ID) = book_order_items (도서ID)(주문ID) = book_orders(주문ID)
select 
  b.title,
  coalesce(round(sum(case when paid_date is null then line_total else 0 end)), 0) as due,
  coalesce(round(sum(case when paid_date is not null then line_total else 0 end)), 0) as paid
from 
  books b
join 
  book_order_items boi
on 
  b.id = boi.book_id
join 
  book_orders bo
on 
  boi.order_id = bo.id
group by 
  b.title
order by 
  b.title asc

문제 3

0개월부터 6개월 후까지 얼마나 주문했는지 월 단위로 재구매한 고객의 수를 집계하시오.
6개월 후까지니까 최종적으로 months_after는 0~6까지만!

-- 2023-01 ~ 2023-06 사이 첫 주문이 속한 월 기준
-- 0~6개월 후 재주문을 월 단위로 재구매 고객 수 집계
-- months_after = 몇 개월 뒤 재구매 했는지
-- active_users = 해당 월 주문 고객 수

with first_order as (
  select 
    *,
    min(order_date) over(partition by user_id) as first_order_date
  from 
    orders
  where
    2022-12-31 < order_date < 2023-07-01
  )
select first_order_month,
  months_after,
  count(user_id) as active_users
from 
  (select
  *,
  date_format(first_order_date, '%Y-%m') as first_order_month,
  timestampdiff(month, first_order_date, order_date) as months_after
from 
  first_order
  ) as months
group by first_order_month, months_after
order by first_order_month, months_after