Objective

Write a SQL query to identify users who confirmed their accounts on the second day after signing up.

Table Schema:

• signups

• confirmations

Task Requirements:

• Join signups with confirmations data
• Filter users who confirmed exactly on the second day after signup
• Exclude users who confirmed on the first day
• Sort results by user ID in ascending order
  
  

Output columns: user_id

SELECT 
    s.user_id
FROM signups s
JOIN confirmations c ON s.user_id = c.user_id
WHERE DATE(c.confirmation_date) = DATE(s.signup_date) + INTERVAL '1 day'
ORDER BY s.user_id ASC;

Explanation

Step 1: Understanding the Date Comparison

We need to find users whose confirmation date is exactly one day after their signup date.

Step 2: Joining Signups and Confirmations

FROM signups s
JOIN confirmations c ON s.user_id = c.user_id

An INNER JOIN connects signup records with confirmation records on user_id, ensuring we only consider users who have both signed up and confirmed.

Step 3: Filtering Second Day Confirmations

WHERE DATE(c.confirmation_date) = DATE(s.signup_date) + INTERVAL '1 day'

DATE() extracts just the date portion (ignoring time); adding INTERVAL '1 day' to signup date gives us the target confirmation date to compare against.

Step 4: Selecting and Ordering

SELECT s.user_id
ORDER BY s.user_id ASC

Returns user IDs sorted in ascending order.