SQL-Leetcode/1934-ConfirmationRate.sql at main · ninad-moree/SQL-Leetcode · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/*
    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.

    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 s.user_id, ROUND(AVG(IF(c.action='confirmed',1,0)),2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id