SQL data engineering interview problem. Difficulty: intermediate. Pattern: Date Functions. About 12 minutes. Part of the Pro drill bank.
From logins, find pairs of consecutive calendar days for the same user. Return user_id, login_date (first day), next_login_date (the following day). A pair counts when next_login_date = login_date + 1. Order by user_id, login_date.
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 two 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
Interview-style drill: Users who logged in on two consecutive calendar days.
From `logins`, find pairs of consecutive calendar days for the same user. Return `user_id`, `login_date` (first day), `next_login_date` (the following day). A pair counts when `next_login_date = login_date + 1`. Order by `user_id`, `login_date`.