[LeetCode] SQL 50 - 1934. Confirmation Rate (MySQL)

1934. Confirmation Rate

 

문제 설명

- 테이블 명 : Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+

- 테이블 설명 :

user_id는 이 테이블의 고유 값 입니다.
time_stamp에는 각 사용자의 가입 시간에 대한 정보가 포함되어 있습니다.


- 테이블 명 : Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+

- 테이블 설명 :

(user_id, time_stamp)는 이 테이블의 기본 키 입니다.
user_id는 Signups 테이블에 대한 외래 키 입니다.
action은 ('confirmed', 'timeout') 유형의 범주 타입(ENUM)입니다.
이 테이블의 각 행은 ID가 user_id인 사용자가 time_stamp에 확인 메시지를 요청했으며, 해당 메시지가 확인(' confirmed')되었거나 시간 초과('timeout')되었음을 나타냅니다.


- 문제 :

사용자의 확인 비율은 'confirmed' 메시지 수를 전체 메시지 수로 나눈 값입니다. 메시지를 요청하지 않은 사용자의 비율은 0입니다.

소수점 이하 두 자리에서 반올림해서, 각 사용자의 확인 비율을 구하는 솔루션을 작성하세요.

순서 상관 없이 결과를 반환 합니다.

 

입출력 예시

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

- 설명 :

6번 사용자는 확인 메시지를 요청하지 않았습니다. 확인률은 0입니다.
3번 사용자는 2개의 요청을 했으나 둘 다 시간 초과되었습니다. 확인률은 0입니다.
7번 사용자는 3개의 요청을 했고 모두 확인되었습니다. 확인률은 1입니다.
2번 사용자는 2개의 요청을 했는데 하나는 확인되었고 다른 하나는 시간 초과되었습니다. 확인률은 1/2 = 0.5입니다.

 

풀이

-- confirmation_rate는 'confirmed' 개수의 평균(confirmed 수/action 수)을 구하는 것

select s.user_id, round(avg(if(c.action="confirmed",1,0)), 2) as confirmation_rate
from Signups s
left join Confirmations c
using(user_id)
group by s.user_id
;​

 

다른 풀이

-- case then 사용

select s.user_id, case when c.action is null then 0 else round(sum(c.action="confirmed")/count(*),2) end as confirmation_rate
from Signups s
left join Confirmations c
using(user_id)
group by s.user_id
;

 

 

출처 : https://leetcode.com/problems/confirmation-rate/description/?envType=study-plan-v2&envId=top-sql-50