This repository was archived by the owner on Apr 6, 2025. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path1934.sql
More file actions
59 lines (43 loc) · 1.55 KB
/
Copy path1934.sql
File metadata and controls
59 lines (43 loc) · 1.55 KB
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
48
49
50
51
52
53
54
55
56
57
58
59
USE leetcode;
# Table: Signups, Confirmations
CREATE TABLE IF NOT EXISTS Signups
(
user_id INT,
time_stamp DATETIME
);
CREATE TABLE IF NOT EXISTS Confirmations
(
user_id INT,
time_stamp DATETIME,
action ENUM ('confirmed','timeout')
);
TRUNCATE TABLE Signups;
INSERT INTO Signups (user_id, time_stamp)
VALUES ('3', '2020-03-21 10:16:13');
INSERT INTO Signups (user_id, time_stamp)
VALUES ('7', '2020-01-04 13:57:59');
INSERT INTO Signups (user_id, time_stamp)
VALUES ('2', '2020-07-29 23:09:44');
INSERT INTO Signups (user_id, time_stamp)
VALUES ('6', '2020-12-09 10:39:37');
TRUNCATE TABLE Confirmations;
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('3', '2021-01-06 03:30:46', 'timeout');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('3', '2021-07-14 14:00:00', 'timeout');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('7', '2021-06-12 11:57:29', 'confirmed');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('7', '2021-06-13 12:58:28', 'confirmed');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('7', '2021-06-14 13:59:27', 'confirmed');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('2', '2021-01-22 00:00:00', 'confirmed');
INSERT INTO Confirmations (user_id, time_stamp, action)
VALUES ('2', '2021-02-28 23:59:59', 'timeout');
# Solution
SELECT s.user_id,
ROUND(AVG(IF(c.action = 'confirmed', 1, 0)), 2) confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;