1934. Confirmation Rate

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

 

# 풀이
select user_id
     , ifnull(round(confirmed/cnt, 2), 0) as confirmation_rate
  from (
    select s.user_id
         , count(c.time_stamp) as cnt
         , sum(case when c.action = 'confirmed' then 1 else 0 end) as confirmed
      from Signups s
      left join Confirmations c
        on s.user_id = c.user_id
      group by s.user_id
       ) as base_table;

 

이 문제에서의 핵심은, 값이 없다면 null이 아닌 0으로 처리해야 한다는 것(예시의 user_id 6번같은 경우)

=> 1. 안쪽 select문에서 confirmed를 구할 때 case when 구문으로 값이 없을 땐 0으로 나오게끔 해야 하고,

     2 .바깥쪽 select문에서 confirmation_rate를 구할 때 ifnull로 null값일 때엔 0으로 나오게끔 해야 한다. 

 

그 외엔 평이한 문제였다.

 

 

 

 

뜬금없지만, 두어달 전 까지만해도 SQL의 select문이나 간신히 쓸 줄 알았던 본인이 지금은 이런 문제 정도는 가볍게 슥슥슥 풀 수 있을 정도가 되었다는 것에, 새삼스레 감개가 무량하다 ...

계속해서 공부해 나간다면 더 어려운 문제들도 척척 풀 수 있게 되겠지.