QCC 6회차

1번

• 제출한 쿼리 (오답)

(select 
  gender,
  name,
  score
from students
where gender = 'F'
order by score desc limit 3)
union
(select 
  gender,
  name,
  score
from students
where gender = 'M'
order by score desc limit 3)

 

→ 틀린 부분 수정) age 조건을 안 넣어줬다..

(select 
  gender,
  name,
  score
from students
where gender = 'F'
order by score desc, age desc limit 3)
union
(select 
  gender,
  name,
  score
from students
where gender = 'M'
order by score desc, age desc limit 3)

⬇︎

• 다른 방법으로 풀어본 쿼리

-- 성별 기준으로 점수 순위 부여한 CTE
with scoring as (
  select
    gender,
    name,
    score,
    rank() over(partition by gender order by score desc, age desc) rnk
  from students
  )

-- 상위 3명 필터링
select
    gender,
    name,
    score
from scoring
where rnk <= 3
order by gender, rnk

 

• 튜터님 쿼리

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

 

튜터님은 안전하게 row_number 사용하셨다.

** age 조건까지 order by 조건으로 넣어줬기 때문에 dense_rank 도 가능함

---

2번

• 제출한 쿼리 (오답)

select
    due.title,
    due.a DUE,
    paid.b PAID
from
    (select 
      title,
      round(sum(line_total),0) a
    from books b 
    join book_order_items boi on b.id = boi.book_id
    join book_orders bo on boi.order_id = bo.id
    where bo.paid_date is null -- 미결제
    group by title) due
join
    (select 
      title,
      round(sum(line_total),0) b
    from books b 
    join book_order_items boi on b.id = boi.book_id
    join book_orders bo on boi.order_id = bo.id
    where bo.paid_date is not null -- 미결제
    group by title) paid
on due.title = paid.title
order by due.title

 

→ 틀린 부분 수정 1) DUE 또는 PAID 값이 NULL일 경우 0으로 처리하는 조건이 시험 중간에 추가됐다.. 공지를 못 보고 반영을 못함

→ 틀린 부분 수정 2) book 이랑 book_order_items 조인 할 때 LEFT JOIN 해줘야 한다. '모든 도서'에 대해 출력해야 하므로.

select
    due.title,
    ifnull(due.a,0) DUE,
    ifnull(paid.b,0) PAID
from
  (select 
    b.title,
    round(sum(boi.line_total),0) a
  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
  where bo.paid_date is null -- 미결제
  group by title) due
join
  (select 
    b.title,
    round(sum(boi.line_total),0) b
  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
  where bo.paid_date is not null -- 미결제
  group by title) paid
on due.title = paid.title
order by due.title

 

→ 여전히 틀린 부분 ⭐️⭐️⭐️
중간에 JOIN하면 중복값만 조인되므로 전체 데이터를 구할 수 없다.

full outer join하면 되겠지만 Mysql에서는 지원 X

( left join과 right join을 각각 시켜준 다음 union 해야 함 )

 

비효율적이다.

⬇︎

• 다른 방법으로 풀어본 쿼리
  • if문으로 컬럼을 각각 만들어 주면 된다.

select
  b.title,
  round(sum(coalesce(if(bo.paid_date is null, 1,0) * boi.line_total)),0) as DUE,
  round(sum(coalesce(if(bo.paid_date is not null, 1,0) * boi.line_total)),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 b.title
order by b.title

 

• 튜터님 쿼리
  • boolean은 1로 반환되는 거 활용해서 if문 생략

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

 

‼️ coalesce 위치 차이

-- 1번
coalesce(round(sum((paid_date is null) * boi.line_total),0),0) as DUE

-- 2번
round(sum(coalesce((paid_date is null) * boi.line_total, 0)),0) as DUE,

어차피 null인 값은 sum에서 연산 제외되기 때문에 결국 결과값은 같다.
하지만 조건절이 추가되거나 복잡한 연산이 될 때 차이가 생길 수 있으므로
2번 코드처럼 먼저 null값을 처리한 후에 계산하는 게 안전하다.

---

3번

• (해설 듣고) 다시 작성한 쿼리 

-- 1. 가장 먼저 구해야 하는 user별 cohort_month 계산하는 CTE 
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 user_id
  )

-- 2. cohort_month로부터 0~6개월 뒤에 몇 명 user 있는지 계산
select
  first_order_month,
  timestampdiff(month, first_order_date, order_date) months_after,
  count(distinct c.user_id) active_users
from orders o
join cohort c on o.user_id = c.user_id
where 
  first_order_month between '2023-01' and '2023-06'
  and timestampdiff(month, first_order_date, order_date) <= 6
group by 1,2
order by 1,2

• 튜터님 쿼리

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

 

timestampdiff(month, 기준일, 계산일)
→ 계산일이 기준일로부터 '몇 개월' 지났는지 반환해주는 함수

[ 단위 ] 
SECOND : 초
MINUTE : 분
HOUR : 시
DAY : 일
WEEK : 주
MONTH : 월
QUARTER : 분기
YEAR : 연

datediff(날짜1, 날짜2)
→ 날짜1 - 날짜2 (두 날짜 간 '일 차이'를 알려주는 함수)