[leetcode] 1934. Confirmation Rate

[문제]

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Signups table:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+
Confirmations table:
+---------+---------------------+-----------+
| user_id | time_stamp          | action    |
+---------+---------------------+-----------+
| 3       | 2021-01-06 03:30:46 | timeout   |
| 3       | 2021-07-14 14:00:00 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2021-06-14 13:59:27 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-02-28 23:59:59 | timeout   |
+---------+---------------------+-----------+
Output: 
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+

 

[내코드]

SELECT 
    DISTINCT S.user_id,
    SUM(IF(action='confirmed',1,0)) OVER (PARTITION BY S.user_id) /    
    COUNT(*) OVER (PARTITION BY S.user_id) AS confirmation_rate
FROM Signups S LEFT JOIN Confirmations C
ON S.user_id = C.user_id

 

- 윈도우 함수를 사용해 확정된 예약 비율을 user_id별로 계산했다.

- 다른 방법으로는 cte를 이용하거나 서브쿼리를 이용하는 방법이 존재했다. 

 

[다른 해답 코드]

WITH cte AS (SELECT s.user_id, c.time_stamp,
CASE 
    WHEN c.action= 'confirmed' THEN 1
    ELSE 0 END AS confirmation_number
FROM signups s 
LEFT JOIN confirmations c 
ON s.user_id=c.user_id)

SELECT user_id, ROUND(SUM(confirmation_number)/ COUNT(confirmation_number),2) AS confirmation_rate
FROM cte
GROUP BY user_id

- CASE WHEN 과 CTE(Common Table Expression)과 LEFT JOIN을 이용해 풀 수 있어 직관적이었다.