Consecutive login day pairs

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Date Functions. About 12 minutes. Part of the Pro drill bank.

A product team wants to know when users come back on consecutive days. In the logins table, each row is a user and a day they logged in. Find pairs of consecutive days for the same user: a day and the very next calendar day. Return user_id, login_date (the first day of the pair) and next_login_date (the day after). A pair counts only when next_login_date = login_date + 1. Sort by user_id, then login_date.

Requirements

• Find rows of the same user where one login is exactly 1 day after another.
• Return user_id, login_date and next_login_date.
• The second date must be the first date plus 1 day, not just a later date.
• Sort by user_id and login_date.

Constraints

• The table is logins with the columns user_id and login_date.
• Join the table to itself, matching the same user_id and b.login_date = a.login_date + 1.
• Return only these 3 columns, in this order: user_id, login_date, next_login_date.

Examples

Input: logins user_id | login_date U1 | 2024-01-01 U1 | 2024-01-02 U1 | 2024-01-03 U1 | 2024-01-05 U2 | 2024-01-01 U2 | 2024-01-03 Output: user_id | login_date | next_login_date U1 | 2024-01-01 | 2024-01-02 U1 | 2024-01-02 | 2024-01-03 Why this passes: U2's dates are 2 days apart, so no consecutive pair. U1's gap after the 3rd also breaks the streak.

Topics: lakebench, sql, self-join, consecutive.

More SQL interview questions · All interview problems · Learn data engineering