Watch the video solution for Users That Actively Request Confirmation Messages, a easy level problem involving Database. This walkthrough by Everyday Data Science has 1,476 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Signups
+----------------+----------+ | Column Name | Type | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ user_id is the column with 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').
Write a solution 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 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-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 | +---------+---------------------+-----------+ Output: +---------+ | user_id | +---------+ | 2 | | 3 | | 6 | +---------+ Explanation: 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.
Problem Overview: You are given a table that records confirmation message requests by users with a timestamp. The task is to identify users who requested confirmation messages more than once within a 24‑hour window.
Approach 1: Self Join on Timestamp Difference (O(n log n) time, O(1) extra space)
The straightforward SQL solution compares each confirmation request with other requests from the same user. Perform a self join on the table using user_id as the join condition, then filter pairs where the timestamp difference is within 24 hours. Use TIMESTAMPDIFF(HOUR, c1.time_stamp, c2.time_stamp) (or seconds) to measure the gap and exclude the same row by ensuring timestamps differ. Finally return distinct user_id. This approach works well because relational databases are optimized for joins and can leverage indexes on user_id and time_stamp. The query essentially checks whether any pair of requests from the same user occurs within the allowed window.
Approach 2: Window Function with LAG (O(n log n) time, O(1) extra space)
A cleaner solution uses a window function to compare each request with the previous request from the same user. Partition the table by user_id and order rows by time_stamp. Then apply LAG(time_stamp) to retrieve the previous request time. If the difference between the current timestamp and the previous one is ≤ 24 hours, that user qualifies. This avoids a self join and makes the logic easier to read. Window functions are commonly used in SQL and database interview questions where comparisons between adjacent rows are needed.
The key insight is that only requests from the same user matter. Once requests are ordered by time, checking consecutive events is enough to detect whether two requests fall within a 24‑hour window. Window functions like LAG from window functions simplify that comparison.
Recommended for interviews: The window function approach is usually preferred. It expresses the logic directly: compare each request with the previous request from the same user. The self join still demonstrates strong SQL fundamentals and works in databases that lack advanced window functions. Both approaches run in roughly O(n log n) time due to sorting or join planning, but the window function version is cleaner and easier to maintain.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join with Timestamp Comparison | O(n log n) | O(1) | Works in most SQL engines and when window functions are unavailable |
| Window Function with LAG | O(n log n) | O(1) | Preferred modern SQL solution; clearer logic and fewer joins |