1939.(Easy)Users That Actively Request Confirmation Messages

Table: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
user_id is the primary key 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 for this table.
user_id is a foreign key with a reference to the Signups table.
action is an ENUM 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').

Write an SQL query to find the IDs of the users that requested a confirmation message twice within a 24-hour window. Two messages exactly 24 hours apart are considered to be within the window. The action does not affect the answer, only the request time.

Return the result table in any order.

The query result format is in the following example:

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-01-06 03:37:45 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 11:57:30 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-01-23 00:00:00 | timeout   |
| 6       | 2021-10-23 14:14:14 | confirmed |
| 6       | 2021-10-24 14:14:13 | timeout   |
+---------+---------------------+-----------+

Result table
+---------+
| user_id |
+---------+
| 2       |
| 3       |
| 6       |
+---------+

User 2 requested two messages within exactly 24 hours of each other, so we include them.
User 3 requested two messages within 6 minutes and 59 seconds of each other, so we include them.
User 6 requested two messages within 23 hours, 59 minutes, and 59 seconds of each other, so we include them.
User 7 requested two messages within 24 hours and 1 second of each other, so we exclude them from the answer.

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/users-that-actively-request-confirmation-messages

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

考察 TIMESTAMPDIFF(unit,ts1,ts2)函数的用法。

SELECT  DISTINCT c1.user_id
FROM Confirmations c1 INNER JOIN Confirmations c2 on 
	c1.user_id=c2.user_id and TIMESTAMPDIFF(SECOND,c1.time_stamp,c2.time_stamp) BETWEEN 1 AND 86400;

Schema

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-01-06 03:37:45', '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 11:57:30', '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-01-23 00:00:00', 'timeout');
insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-23 14:14:14', 'confirmed');
insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-24 14:14:13', 'timeout');

---
 Truncate table Signups;
insert into Signups (user_id, time_stamp) values (6, "2020-11-14 13:43:07");
insert into Signups (user_id, time_stamp) values (7, "2020-12-19 04:30:34");
insert into Signups (user_id, time_stamp) values (5, "2020-12-06 03:57:51");
insert into Signups (user_id, time_stamp) values (2, "2020-03-18 17:27:09");
Truncate table Confirmations;
Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-07-13 07:13:43", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-03-28 07:23:07", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-09-03 05:23:24", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-12-28 12:10:45", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-02-11 22:05:41", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(5, "2021-07-09 02:52:11", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-07-09 06:43:09", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-03-01 02:42:40", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-05-09 15:14:55", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-04-11 21:46:46", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-08-15 23:04:21", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-08-12 16:33:26", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-08-22 13:44:57", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-10-02 21:58:19", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-07-31 16:31:44", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-10-12 11:58:10", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2021-07-12 19:37:27", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-04-14 22:34:04", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-09-25 12:15:58", "timeout");
Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-06-19 12:46:58", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-12-11 09:22:27", "confirmed");
Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-08-03 08:55:49", "timeout");

Previous1934.(Medium)Confirmation Rate Next剑指offer

Last updated 4 years ago

Was this helpful?

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-01-06 03:37:45', '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 11:57:30', '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-01-23 00:00:00', 'timeout'); insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-23 14:14:14', 'confirmed'); insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-24 14:14:13', 'timeout'); --- Truncate table Signups; insert into Signups (user_id, time_stamp) values (6, "2020-11-14 13:43:07"); insert into Signups (user_id, time_stamp) values (7, "2020-12-19 04:30:34"); insert into Signups (user_id, time_stamp) values (5, "2020-12-06 03:57:51"); insert into Signups (user_id, time_stamp) values (2, "2020-03-18 17:27:09"); Truncate table Confirmations; Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-07-13 07:13:43", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-03-28 07:23:07", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-09-03 05:23:24", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-12-28 12:10:45", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-02-11 22:05:41", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(5, "2021-07-09 02:52:11", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-07-09 06:43:09", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-03-01 02:42:40", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-05-09 15:14:55", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-04-11 21:46:46", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-08-15 23:04:21", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-08-12 16:33:26", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-08-22 13:44:57", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-10-02 21:58:19", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-07-31 16:31:44", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(7, "2020-10-12 11:58:10", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2021-07-12 19:37:27", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(2, "2020-04-14 22:34:04", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-09-25 12:15:58", "timeout"); Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-06-19 12:46:58", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(5, "2020-12-11 09:22:27", "confirmed"); Insert into Confirmations (user_id, time_stamp, action) values(6, "2020-08-03 08:55:49", "timeout");