SQL | LeetCode 1934. 사용자 확인률 계산하기 (LEFT JOIN과 COALESCE, AVG()의 조합)

 

Table: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.

 

Table: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').

 

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 decimalplaces.

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              |
+---------+-------------------+
Explanation: 
User 6 did not request any confirmation messages. The confirmation rate is 0.
User 3 made 2 requests and both timed out. The confirmation rate is 0.
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.

사용자가 사이트에 가입한 후, 이메일 혹은 기타 방법으로 확인 메시지를 받습니다. 이 확인 요청은 'confirmed'되거나 'timeout'되어 실패할 수 있습니다.

 

이 문제는 각 사용자별로 다음을 계산해야 합니다:

• 요청한 확인 메시지 수 (총 row 수)
• 그 중 ‘confirmed’된 메시지 수
• 확인률 = confirmed 수 / 전체 요청 수
• 요청한 적이 없는 사용자는 확인률이 0

LEFT JOIN 으로 가입자에 대한 모든 인증 요청 붙이기

SELECT *
FROM signups s
LEFT JOIN confirmations c ON s.user_id = c.user_id;

| user_id | time_stamp          | user_id | time_stamp          | action    |
| ------- | ------------------- | ------- | ------------------- | --------- |
| 3       | 2020-03-21 10:16:13 | 3       | 2021-07-14 14:00:00 | timeout   |
| 3       | 2020-03-21 10:16:13 | 3       | 2021-01-06 03:30:46 | timeout   |
| 7       | 2020-01-04 13:57:59 | 7       | 2021-06-14 13:59:27 | confirmed |
| 7       | 2020-01-04 13:57:59 | 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2020-01-04 13:57:59 | 7       | 2021-06-12 11:57:29 | confirmed |
| 2       | 2020-07-29 23:09:44 | 2       | 2021-02-28 23:59:59 | timeout   |
| 2       | 2020-07-29 23:09:44 | 2       | 2021-01-22 00:00:00 | confirmed |
| 6       | 2020-12-09 10:39:37 | null    | null                | null      |

 c.action = 'confirmed' 로 boolean 계산

SELECT 
  s.user_id, 
  c.action = 'confirmed' AS is_confirmed
FROM 
  signups s
LEFT JOIN 
  confirmations c 
ON 
  s.user_id = c.user_id;
  
| user_id | is_confirmed |
| ------- | ------------ |
| 3       | 0            |
| 3       | 0            |
| 7       | 1            |
| 7       | 1            |
| 7       | 1            |
| 2       | 0            |
| 2       | 1            |
| 6       | null         |

 

AVG()로 평균 구하기 + COALESCE 로 NULL → 0 처리

SELECT 
  s.user_id, 
  ROUND(COALESCE(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate
FROM 
  signups s
LEFT JOIN 
  confirmations c 
ON 
  s.user_id = c.user_id
GROUP BY 
  s.user_id;
  
| user_id | confirmation_rate |
| ------- | ----------------- |
| 3       | 0                 |
| 7       | 1                 |
| 2       | 0.5               |
| 6       | 0                 |

 

COALESCE(..., 0) 로 NULL을 0으로 대체해줍니다.