[240102] SQL: 코드카타 89-90 & 파이썬: 코드카타 5

1. 피벗테이블 복습을 활용한 문제 풀이  

1934. Confirmation Rate https://leetcode.com/problems/confirmation-rate/description/

 

1) 어떤 문제가 있었나

유저별 확정률을 구하는 문제에서, 비율을 계산하기 위해 표를 어떻게 가공하면 좋을지 깔끔하게 떠오르지 않음

 

2) 내가 시도해본 건 무엇인가

left 조인 및 셀프 조인 등으로 테이블을 합치고, if나 case when 절로 조건을 추가해보고자 했으나 표가 정리되지 않음 

 

3) 어떻게 해결했나

if절 활용을 고민하다가 피벗테이블 형식으로 표를 구성하면 비율을 구할 수 있다는 점을 깨닫고,

기존 피벗테이블 생성에 대해 공부했던 내용을 복습하여 본 문제에 적용하여 문제 해결함 

with table1 as (select s.user_id, c.action, count(*) cnt
        from Signups s
        left join Confirmations c on s.user_id = c.user_id 
        group by 1, 2 with rollup)

select user_id, round(confirmed/total,2) confirmation_rate 
from (
select user_id, 
        max(if( action is null, cnt, 0)) total ,
        max(if(action = 'confirmed', cnt, 0)) confirmed
        from table1
where user_id is not null
group by 1) table2

 

4) 무엇을 새롭게 알았나

- 동일한 항목 내에서 2개 이상의 속성 값에 대한 연산이 필요할 때는 피벗 테이블 형식을 구성하는 것도 좋은 방법

- 하기 다른 사람의 답안과 같이 IFNULL을 활용해 NULL값은 0으로 치환해 더 짧고 간결한 쿼리 작성도 가능 

└ IFNULL, COALESCE 등 NULL값 활용 함수에 대해 공부가 필요할 듯 

SELECT s.user_id, 
       IFNULL(ROUND(SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(c.user_id), 2), 0) AS confirmation_rate
FROM signups s
LEFT JOIN confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;