Time since previous login

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

A retention analyst wants to know how long users stay away between visits. For each login, find how many seconds have passed since that user's previous login. From logins_ts, return user_id, login_time and time_since_last_login, which is the number of whole seconds since the same user's previous login. For a user's first login there is no previous one, so the value is NULL. Sort by user_id, then login_time.

Requirements

• For each login, find the previous login of the same user.
• time_since_last_login is the difference in whole seconds between the two.
• A user's first login gets NULL.
• Sort by user_id and login_time.

Constraints

• The table is logins_ts with the columns user_id and login_time (a timestamp).
• Use LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time) and a date difference in seconds.
• Return only these 3 columns, in this order: user_id, login_time, time_since_last_login.

Examples

Input: logins_ts (U1 slice) user_id | login_time U1 | 2024-01-01 09:00:00 U1 | 2024-01-01 18:00:00 Output: user_id | login_time | time_since_last_login U1 | 2024-01-01 09:00:00 | NULL U1 | 2024-01-01 18:00:00 | 32400 Why this passes: 18:00 - 09:00 = 9 hours = 32400 seconds.

Topics: lakebench, sql, lag, lead.

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