SQL 예제 풀이(Leetcode - Confirmation Rate)

https://leetcode.com/problems/confirmation-rate/description/

LeetCode - The World's Leading Online Programming Learning Platform

 

---

 

Signups와 Confirmations 두 테이블이 있다.

두 테이블을 가지고서 각 유저별 confirm받은 비율을 구하는 것이 문제의 요구사항이다.

confirm받을 비율을 구하기 위해서는

각 유저별로 Confirmations 테이블의 action 컬럼 속성값이 confirmed로 적혀있는 수를

전체 action 횟수로 나눠주면된다.

예를 들어

user_id 2의 경우 confirm된건 한 번이고,

전체 action은 두 번이기때문에

1 / 2 해서 confirmation ratio는 0.5가 된다.

 

---

 

전체 action의 수는 전체 테이블을 user_id로 그룹화해서 user_id를 count하면 될 것 같다. 

문제는 confirm받은 action의 횟수인데

조건을 걸어야하기때문에 별도의 테이블을 만들어 준다.

 * 본 테이블에 조건을 걸면 전체 action의 수를 구할 수 없음

 

SELECT SI.USER_ID,
	   COUNT(CON.ACTION)
FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID
WHERE CON.ACTION = 'CONFIRMED'
GROUP BY SI.USER_ID

이렇게 SIGNUPS 테이블과 CONFIRMATIONS 테이블을 LEFT JOIN하여

ACTION 컬럼의 속성값이 CONFIRMED인 USER의 수를 COUNT한다.

 * 이 쿼리의 별명은 앞으로 CNT다.

 

그런 다음

이 쿼리와 전체의 경우를 볼 수 있는 쿼리를 FROM절에서 JOIN한 후 비율을 구한다.

전체의 경우를 볼 수 있는 쿼리는 아래와 같다.

 * 이 쿼리는 편의상 ALL이라고 표현하겠다.

SELECT *
FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID
GROUP BY SI.USER_ID

 

모든 USER_ID가 포함되어야하기때문에 ALL을 왼쪽에 두고 LEFT JOIN한다.

SELECT *
FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID LEFT JOIN
(
    SELECT SI.USER_ID,
           COUNT(CON.ACTION) CON_CNT
    FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID
    WHERE CON.ACTION = 'CONFIRMED'
    GROUP BY SI.USER_ID
) CNT ON SI.USER_ID = CNT.USER_ID

 

이렇게 쿼리를 작성했을때의 결과값은 아래와 같다.

전체 USER_ID와 CONFIRM된 횟수 모두 확인할 수 있다.

 * 왼쪽 빨간 테두리 = 전체 USER_ID / 오른쪽 빨간 테두리 = CONFIRM된 횟수

 

CONFIRM된 횟수는 이미 COUNT되어졌으니 CNT.CON_COUNT 그대로 사용하면 되고

그 수를 전체 유저수를 COUNT한 COUNT(SI.USER_ID)로 나눠주면된다.

이를 반영한 쿼리와 결과값은 아래와 같으며

문제에서 CONFIRMATION RATE를 소수점 둘째자리까지만 표시하라고 했기때문에

ROUND함수를 적용해주면 된다.

SELECT SI.USER_ID,
       IF(CNT.CON_CNT / COUNT(SI.USER_ID) IS NOT NULL, ROUND((CNT.CON_CNT / COUNT(SI.USER_ID)), 2), 0) CONFIRMATION_RATE
FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID LEFT JOIN
(
    SELECT SI.USER_ID,
           COUNT(CON.ACTION) CON_CNT
    FROM SIGNUPS SI LEFT JOIN CONFIRMATIONS CON ON SI.USER_ID = CON.USER_ID
    WHERE CON.ACTION = 'CONFIRMED'
    GROUP BY SI.USER_ID
) CNT ON SI.USER_ID = CNT.USER_ID
GROUP BY SI.USER_ID

 

* SI.USER_ID로 그룹화해주는 것도 잊어서는 안 된다.