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 That Actively Request Confirmation MessagesConfirmations Tableuser_idtime_stampaction310:16confirmed308:30+1timeout714:00confirmedSELF JOINTIME DIFF ≤ 24hUser 3: 22h 14m difference ✓User 7: Only 1 request ✗Resultuser_id3
Understanding the Visualization
1
Input Tables
Signups and Confirmations data
2
Self-Join
Compare user's requests with each other
3
Output
Users with requests within 24 hours
Key Takeaway
🎯 Key Insight: Self-joins are perfect for comparing rows within the same table based on conditions like time windows

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