TIL_[SQL] Confirmation Rate + Average Selling Price

Confirmation Rate에서 의문이었던 점

1. AVG(Case when) 으로 sum/count 와 같은 결과를 낼 수 있다?
2. signups 테이블에는 있지만 confirmations 테이블에는 없는 id=6이 어떻게 null값이 안나올 수 있는지

(내가 쓴 쿼리)
with cnt as (select user_id, sum(action = 'timeout') "timeout",
            sum(action = 'confirmed') "confirmed",
            sum(action = 'timeout' or action = 'confirmed') "both"
            from confirmations
            group by 1)

select a.user_id,
        ifnull(round(confirmed/`both`,2), 0) "confirmation_rate"
from signups a left join cnt b
on a.user_id=b.user_id
group by 1

---
(다른 사람 쿼리)
SELECT 
  s.user_id,
  ROUND(AVG(CASE WHEN c.action="confirmed" THEN 1 ELSE 0 END),2) AS confirmation_rate
FROM signups AS s
LEFT JOIN confirmations AS c
  ON s.user_id = c.user_id
GROUP BY 1

• 튜터님이 excel로 직접 case when을 만들어서 예제를 만들어주신 덕분에, avg(case when)으로 평균값을 만들 수 있다는 걸 확인할 수 있었다.
• 내가 쓴 쿼리에선 confirmations 테이블에 없는 ID = 6 의 값이 Null로 나오는데 다른 쿼리에선 어떻게 Null이 아닌 0으로 나왔을까.
  이는 마찬가지로 case when에 의해 id=6의 action 값이 Null 이지만 action=confirmed가 아니면 0을 반환하는 조건에 의해 자동으로 Null이 아닌 0으로 데이터가 들어가졌기 때문에 IFNULL로 변환할 필요가 없었다.

---

Average Selling Price에서 의문이었던 점

1.
with total_price as (
    select a.product_id,
            a.price * b.units "sales",
            units
    from prices a left join unitssold b
    on a.product_id=b.product_id and b.purchase_date between a.start_date and a.end_date
)
    -------
2.
with total_price as (
    select a.product_id,
            a.price * b.units "sales",
            units
    from prices a left join unitssold b
    on a.product_id=b.product_id
    where b.purchase_date between a.start_date and a.end_date
)

1. 1은 left join하고 on으로 product_id가 같은지, purchase_date가 일정 기간 사이에 있는지를 확인한다.
   그런데 1의 결과를 보면 prices 테이블에는 있고 UnitsSold 테이블에는 없는 ID = 3 이 조회된다.
2. 2번의 경우 left join 후 on에는 product_id, purchase_date에 대한 조건은 where절에 걸었다. 하지만 결과는 ID = 3 의 데이터는 조건절에 의해 참이 아닌 경우 걸러져서 보여지지 않았다.
3. 2번에서 where절에 의해 참이 아니기 때문에 ID = 3 이 안보이는건 이해가 가는데, 왜 1번의 경우에는 ID = 3 의 결과값이 나오는지 의문이었다.

JOIN의
ON절 : 조인 전 각 테이블에 있는 데이터에 조건 걸음
WHERE절 : 조인 후의 데이터에 조건을 거는 것

예시 데이터

1. Left join ON

select t1.col1, t1.col2, t2.col1, t2.col2
from table1 t1
left join table2 t2
on t1.col1 = t2.col1
and t2.col2 = '일'

• table1의 전체 row와
  table2에서 col2 칼럼 값이 '일'인 로우만 뽑은 row 들을 left join
• ON 조건은 JOIN을 하기 전 필터링이 되는 기준

2. Left join WHERE

select t1.col1, t1.col2, t2.col1, t2.col2
from table1 t1
left join table2 t2
on t1.col1 = t2.col1
where t2.col2 = '일'

• table1의 전체 row와
  table2의 전체 row를 left join 한 후,
  col2 칼럼 값이 '일'인 로우만 뽑음
• WHERE 조건은 JOIN을 한 이후에 필터링 하는 기준이 됨

따라서

left join unitssold b
    on a.product_id=b.product_id and b.purchase_date between a.start_date and a.end_date

• where 가 아닌 on 절에 and로 묶어줬기 때문에,
  각 id 당 prices 테이블의 id 2개 x UnitsSold 테이블의 id 2개 = 총 4개의 row 가 나오는게 아닌
  join 전 필터링을 거쳐 구매 날짜가 start_date, end_date에 포함하는 데이터가 매칭되어 join될 수 있었고,
• Left join 이기 때문에 왼쪽의 테이블은 매칭 되는게 없으면 기준 데이터는 살고 join 테이블 값만 Null로 되는 것이기 때문에 Id= 3의 행이 살아 있을 수 있는 거였다!!

---

Leetcode 는 문법을 제대로 파악해야지만 이해할 수 있는 문제가 많다.
문제의 컨셉은 간단하지만, 원하는 결과물을 만들어내는 쿼리는 결코 얉은 지식으로는 풀기 어려운 문제가 많아 푸는데 시간이 많이 걸리고 왜 이렇게 나오는지도 해석이 오래걸렸다 😭