Users That Actively Request Confirmation Messages

Users That Actively Request Confirmation Messages - Problem

Database Easy

Find Users with Frequent Confirmation Requests

You're building a security system that monitors user behavior for suspicious activity. Your task is to identify users who are making frequent confirmation requests within a short time window.

Given two tables:

Signups - Contains user registration information
Confirmations - Contains confirmation request logs with timestamps and outcomes

Your goal is to find all user IDs that requested confirmation messages twice within a 24-hour window. The outcome of the confirmation (confirmed/timeout) doesn't matter - only the timing of requests.

Key Points:

Two messages exactly 24 hours apart are considered within the window
Only request timestamps matter, not the action results
A user can have multiple valid pairs of requests

Input & Output

Basic Example - Multiple Users

$ Input: Signups: | 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: | user_id | time_stamp | action | |---------|--------------------|-----------| | 3 | 2021-01-06 03:30:46 | timeout | | 3 | 2021-01-06 20:30:46 | confirmed | | 7 | 2021-06-12 11:57:29 | confirmed | | 7 | 2021-06-13 11:57:30 | timeout |

› Output: | user_id | |---------| | 3 | | 7 |

💡 Note: User 3 requested confirmations at 03:30:46 and 20:30:46 on the same day (17 hours apart, within 24-hour window). User 7 requested confirmations on June 12th and 13th at almost the same time (exactly 24 hours and 1 second apart, which is within the 24-hour window since we include exactly 24 hours).

Edge Case - Exactly 24 Hours Apart

$ Input: Signups: | user_id | time_stamp | |---------|--------------------| | 1 | 2020-01-01 00:00:00 | | 2 | 2020-01-01 00:00:00 | Confirmations: | user_id | time_stamp | action | |---------|--------------------|-----------| | 1 | 2021-01-01 12:00:00 | confirmed | | 1 | 2021-01-02 12:00:00 | timeout | | 2 | 2021-01-01 12:00:00 | confirmed | | 2 | 2021-01-02 12:00:01 | timeout |

› Output: | user_id | |---------| | 1 |

💡 Note: User 1 has requests exactly 24 hours apart (included). User 2 has requests 24 hours and 1 second apart (excluded, since it exceeds the 24-hour window).

No Active Users

$ Input: Signups: | user_id | time_stamp | |---------|--------------------| | 1 | 2020-01-01 00:00:00 | | 2 | 2020-01-01 00:00:00 | Confirmations: | user_id | time_stamp | action | |---------|--------------------|-----------| | 1 | 2021-01-01 12:00:00 | confirmed | | 1 | 2021-01-03 12:00:00 | timeout | | 2 | 2021-01-01 12:00:00 | confirmed |

› Output: | user_id | |---------| (empty result)

💡 Note: User 1 has requests 48 hours apart (exceeds 24-hour window). User 2 has only one confirmation request. No users qualify as actively requesting confirmation messages.

Constraints

1 ≤ Number of signups ≤ 1000
1 ≤ Number of confirmations ≤ 10⁴
user_id is a positive integer
time_stamp is a valid datetime
action is either 'confirmed' or 'timeout'
Each user can have multiple confirmation requests
Two messages exactly 24 hours apart are considered within the window

Visualization

Tap to expand

Understanding the Visualization

Collect All Requests

Gather all confirmation requests with timestamps

Group by User

Organize requests by user_id to analyze individual patterns

Sort Chronologically

Order each user's requests by timestamp

Apply Sliding Window

Check if any two consecutive requests fall within 24-hour window

Identify Active Users

Flag users with requests meeting the time criteria

Key Takeaway

🎯 Key Insight: Use SQL window functions to efficiently compare consecutive timestamps within each user's request history, eliminating expensive cross-joins while maintaining accuracy.

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

The optimal approach uses SQL window functions like LAG() to compare each confirmation timestamp with the previous one for the same user. This eliminates the need for expensive self-joins while maintaining O(n log n) time complexity due to sorting requirements.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function (Optimal)	O(n log n)	O(n)	Use window functions to efficiently find consecutive requests within time window
Brute Force (Cross Join)	O(n²)	O(n²)	Compare every confirmation with every other confirmation for same user
Self Join with Window	O(n²)	O(n)	Use self join to efficiently compare user's confirmations within time window

Window Function (Optimal) — Algorithm Steps

Order confirmations by user_id and timestamp
Use LAG() window function to get previous timestamp
Calculate time difference with previous request
Filter users where any difference is ≤ 24 hours
Return distinct user_ids

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by User

Group all confirmations by user_id

Order by Time

Sort each user's confirmations chronologically

Apply LAG Function

Get previous timestamp for each row

Calculate Differences

Find time gaps between consecutive requests

Filter Results

Keep users with gaps ≤ 24 hours

Code -

solution.c — C

// SQL solution using window functions
int* findActiveUsersOptimal(MYSQL *conn, int *count) {
    const char* query = 
        "SELECT DISTINCT user_id "
        "FROM ( "
        "    SELECT user_id, time_stamp, "
        "           LAG(time_stamp) OVER (PARTITION BY user_id ORDER BY time_stamp) as prev_time "
        "    FROM Confirmations "
        ") t "
        "WHERE TIMESTAMPDIFF(HOUR, prev_time, time_stamp) <= 24 "
        "ORDER BY user_id";
    
    if (mysql_query(conn, query)) {
        fprintf(stderr, "Query failed: %s\n", mysql_error(conn));
        *count = 0;
        return NULL;
    }
    
    MYSQL_RES *result = mysql_store_result(conn);
    if (result == NULL) {
        *count = 0;
        return NULL;
    }
    
    *count = mysql_num_rows(result);
    int *users = malloc(sizeof(int) * (*count));
    if (users == NULL) {
        mysql_free_result(result);
        *count = 0;
        return NULL;
    }
    
    MYSQL_ROW row;
    int i = 0;
    while ((row = mysql_fetch_row(result)) && i < *count) {
        users[i] = atoi(row[0]);
        i++;
    }
    
    mysql_free_result(result);
    return users;
}

// Helper function to free allocated memory
void freeUserArray(int* users) {
    if (users != NULL) {
        free(users);
    }
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window function, then linear scan

⚡ Linearithmic

Space Complexity

O(n)

Space for sorting and window function computation

⚡ Linearithmic Space

41.3K Views

Medium Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler