QCC 4회차

1번

• 제출한 쿼리

with filtered as (
  select region_name
  from stores
  group by region_name
  having count(1) >= 2
)

select
    REGION_NAME,
    SALES
from
  (select
    REGION_NAME,
    SALES,
    rank() over(partition by region_name order by sales desc) rnk
  from stores) a
where 
  rnk = 1
  and region_name in (
                  select region_name
                  from filtered
                  )
order by 1

 

⬇︎

• 수정) rank 필요 없단다 ...

select
  region_name, max(sales)
from stores
group by 1
having count(1) >=2 
order by 1

---

2번

• (뒤늦게) 작성한 쿼리

select
   distinct c1.name name_X,
  c2.name name_Y,
  count(1) orders
from cart_products c1, cart_products c2
where
    c1.cart_id = c2.cart_id
    and c1.name <> c2.name
group by 1,2
order by 1,2

 

이 문제의 핵심인 셀프조인까지는 생각했는데

distinct 때문에 계속 고민하다가 결국 제출을 못 했다.

해설 전에 어떻게든 풀어보고 싶었는데 다행히 성공 ..

근데 튜터님 쿼리랑 조금 다른 부분이 있다.

• 튜터님 쿼리

SELECT
    A.NAME AS name_x, -- 여기서 distinct를 거는 게 아니라
    B.NAME AS name_y,
    COUNT(DISTINCT A.CART_ID) as orders -- 여기서 걸어줌
FROM cart_products A
JOIN cart_products B
ON A.CART_ID = B.CART_ID AND A.NAME <> B.NAME
GROUP BY A.NAME, B.NAME
ORDER BY A.NAME, B.NAME

 

내 쿼리랑 결과는 같다. 왜지..

• 이유 
  1. 어차피 groupby 해주기 때문에 name 컬럼에 distinct 해주는 거 무의미
  2. 하나의 장바구니에 제품 (X,Y) 1 쌍이 중복으로 들어갈 경우는 없기 때문에 사실 count(1)해도 상관 없음 (amount 개념)

---

3번 

• 제출한 쿼리 (오답)

방법1. 조건마다 테이블 만들고 union all

with final as( 
  select count(distinct user_id) cnt
  from
      (select 
      	o.user_id, 
      	o.order_date,
        min(o.order_date) over(partition by o.id) min_date,  
        -- 1. 첫 '결제일'을 보고싶다. 첫 '주문일'이 아니라 .. p.pay_date로 변경
        -- 2. 사용자partition by 기준컬럼도 id가 아니라 user_id로 변경
        -- -> min(p.pay_date) over(partition by p.user_id) min_date
        p.pay_date  --> 필요없음
      from orders o
      left join payments p
      on o.user_id = p.user_id) a
where min_date > order_date
union all
select count(1) cnt  --> 순수한 user만 카운팅해야 하니까 count(distinct o.user_id)
from orders o
left join payments p on o.user_id = p.user_id
where p.pay_date is null) --> 이것도 맞긴 하지만 p.user_id가 좀 더 사고의 일관성을 보여줄 듯

select sum(cnt) cnt
from final

⬇︎

• 주석 처리한 부분 수정한 최종 쿼리

with final as( 
  select count(distinct user_id) cnt
  from
      (select 
      	o.user_id, 
      	o.order_date,
        min(p.pay_date) over(partition by p.user_id) min_date
      from orders o
      left join payments p
      on o.user_id = p.user_id) a
  where min_date > order_date
union all
  select count(distinct o.user_id) cnt 
  from orders o
  left join payments p on o.user_id = p.user_id
  where p.pay_date is null
) 

select sum(cnt) cnt
from final

 

• 해설 강의 듣고 다른 방법으로 다시 풀어본 쿼리

방법2.  조건 한 개만 CTE 만들고 left join

with first_paydate as (
    select 
    	user_id, 
    	min(pay_date) min_date
    from payments
    group by user_id
)

select 
    count(distinct o.user_id) cnt
from orders o
left join payments p 
on o.user_id = p.user_id
left join first_paydate f  -- left join 해야하는 이유: first_paydate에는 결제 한 user_id만 있음
on o.user_id = f.user_id
where 
  p.user_id is null
  or o.order_date < f.min_date

⬇︎

• 튜터님 쿼리

WITH first_payment AS (
    SELECT
        USER_ID,
        MIN(PAY_DATE) AS FIRST_PAY_DATE
    FROM payments
    GROUP BY USER_ID
)
SELECT
   COUNT(DISTINCT o.USER_ID) cnt
FROM orders o
LEFT JOIN first_payment fp
ON o.USER_ID = fp.USER_ID
WHERE fp.USER_ID IS NULL
OR o.ORDER_DATE < fp.FIRST_PAY_DATE

• payments 테이블 조인 안 해줘도 됨. 이미 first_paydate 자체가 payments 테이블에서 파생된 거임