Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Time since previous login

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

From logins_ts, for each login return: user_id, login_time, time_since_last_login: seconds since that user's previous login (NULL for the first). Order by user_id, login_time.

Requirements

  • First login per user has NULL delta.

Constraints

  • Express the delta in whole seconds.

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

intermediate

Time since previous login

Interview-style drill: LAG to compute seconds since previous login per user.

From `logins_ts`, for each login return: `user_id`, `login_time`, `time_since_last_login`: seconds since that user's previous login (NULL for the first). Order by `user_id`, `login_time`.