Active Users - Video Solutions

Problem Overview: The task is to find users who logged into the system for five or more consecutive days. The Logins table stores login dates for each user, and the final result must return the user id and name from the Accounts table for users meeting the consecutive activity requirement.

Approach 1: Self Join for Consecutive Dates (O(n^2) time, O(1) space)

A direct approach checks consecutive days by joining the Logins table with itself multiple times. For each login record, you attempt to match entries for the next four days using date arithmetic like login_date + INTERVAL 1 DAY. If all five rows exist for the same user, the user qualifies as active. This approach relies entirely on joins and date comparisons, which makes the logic straightforward but inefficient when the table grows. Multiple joins on the same dataset lead to quadratic behavior, making it impractical for large datasets.

Approach 2: Window Functions with ROW_NUMBER (O(n log n) time, O(n) space)

The optimized solution uses a window function to detect consecutive date sequences. Partition the Logins table by user_id and assign a ROW_NUMBER() ordered by login_date. For each row, compute a grouping key such as DATE_SUB(login_date, INTERVAL row_number DAY). Consecutive dates produce the same key because the offset between the row number and the date stays constant. Group by user_id and this derived key, then count the rows in each group. Any group with COUNT(*) >= 5 represents five consecutive login days. Finally, join the result with Accounts to return the user names.

This pattern is common in SQL problems that involve detecting consecutive events. The window function eliminates complex joins and transforms the problem into grouping sequences after sorting. MySQL performs an internal sort for the window operation, which leads to O(n log n) time complexity and O(n) additional space for intermediate results.

Recommended for interviews: The window function approach is the expected solution. Interviewers want to see that you recognize the consecutive-sequence pattern and solve it using window functions and grouping. Mentioning the self-join method shows you understand the brute-force logic, but using ROW_NUMBER() demonstrates stronger SQL skills and familiarity with advanced database querying techniques.