Users That Actively Request Confirmation Messages - Problem

You are given two tables: Signups and Confirmations.

The Signups table contains information about user signup times, and the Confirmations table records confirmation message requests with their timestamps and actions.

Write a SQL query to find the IDs of 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.

Important notes:

  • The action ('confirmed' or 'timeout') does not affect the answer
  • Only the request time matters for determining the 24-hour window
  • Return the result in any order

Table Schema

Signups
Column Name Type Description
user_id PK int Unique user identifier
time_stamp datetime User signup timestamp
Primary Key: user_id
Confirmations
Column Name Type Description
user_id PK int User identifier (foreign key to Signups)
time_stamp PK datetime Confirmation request timestamp
action ENUM('confirmed', 'timeout') Result of confirmation request
Primary Key: (user_id, time_stamp)

Input & Output

Example 1 — Users with Multiple Requests
Input Tables:
Signups
user_id time_stamp
3 2020-03-21 10:16:13
7 2020-01-04 13:57:59
Confirmations
user_id time_stamp action
3 2020-03-21 10:16:13 confirmed
3 2020-03-22 08:30:00 timeout
7 2020-01-05 03:30:17 confirmed
Output:
user_id
3
💡 Note:

User 3 requested confirmation messages at 2020-03-21 10:16:13 and 2020-03-22 08:30:00. The time difference is approximately 22 hours and 14 minutes, which is within the 24-hour window. User 7 only has one confirmation request, so they don't qualify.
Example 2 — No Active Users
Input Tables:
Signups
user_id time_stamp
1 2020-01-01 12:00:00
2 2020-01-02 12:00:00
Confirmations
user_id time_stamp action
1 2020-01-01 15:00:00 confirmed
2 2020-01-05 10:00:00 timeout
Output:
user_id
💡 Note:

Each user only has one confirmation request, so no user qualifies as having requested confirmation messages twice within a 24-hour window. The result is empty.
Example 3 — Exactly 24 Hours Apart
Input Tables:
Signups
user_id time_stamp
5 2020-02-01 10:00:00
Confirmations
user_id time_stamp action
5 2020-02-01 12:00:00 timeout
5 2020-02-02 12:00:00 confirmed
Output:
user_id
5
💡 Note:

User 5 has confirmation requests exactly 24 hours apart (2020-02-01 12:00:00 and 2020-02-02 12:00:00). Since requests exactly 24 hours apart are considered within the window, user 5 qualifies.

Constraints

  • 1 ≤ user_id ≤ 1000
  • action is either 'confirmed' or 'timeout'
  • Each user can have multiple confirmation requests
  • Two messages exactly 24 hours apart are within the window

Visualization

Tap to expand
Users Requesting Confirmations Within 24 Hours INPUT Signups Table user_id time_stamp 3 2020-03-21 7 2020-01-04 2 2020-07-29 Confirmations Table user_id time_stamp action 3 2021-01-06 03:30 timeout 3 2021-01-06 14:00 timeout 7 2021-06-12 11:57 confirmed 7 2021-06-13 12:58 confirmed 2 2021-01-22 00:00 confirmed 2 2021-01-22 23:59 timeout = Within 24hr window ALGORITHM STEPS 1 Self-Join Table Join Confirmations with itself on same user_id 2 Calculate Time Diff TIMESTAMPDIFF between c1.time and c2.time 3 Filter 24-Hour Window WHERE diff BETWEEN 0 AND 24 hours 4 Select Distinct Users DISTINCT user_id from matching pairs Time Difference Check: User 3: 14:00 - 03:30 = 10.5 hrs [OK] User 2: 23:59 - 00:00 = 23.98 hrs [OK] FINAL RESULT Users Found: 3 Requested at 03:30, 14:00 10.5 hrs apart 2 Requested at 00:00, 23:59 23.98 hrs apart Output: user_id: 3, 2 Both had 2 requests within 24 hours Key Insight: Use a SELF-JOIN on the Confirmations table to compare each user's requests with their other requests. TIMESTAMPDIFF(SECOND, c1.time_stamp, c2.time_stamp) calculates the exact time difference. Filter where difference is between 0 and 86400 seconds (24 hours). Action type is irrelevant! TutorialsPoint - Users That Actively Request Confirmation Messages | Optimal Solution

Related Problems

Asked in
Facebook 28 Microsoft 22 Amazon 18
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen