Lag and Lead

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Free to practice.

Days since previous event per user via lag. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

To see how often a user comes back, you can compare each event with the one before it. The function lag(ts) gives you the ts of the previous row inside the same window, and datediff counts the days between two dates. For each user_id, in time order, add the column time_since_last_event: the number of days between this event and the previous event of the same user. The first event of a user has no previous event, so its value is empty (None). Return user_id, ts and time_since_last_event, sorted by user_id and ts. The editor creates the table df and ends with result = df. Change the last line to build your answer.

Requirements

• Use a window partitioned by user_id and ordered by ts.
• Get the previous timestamp with F.lag("ts", 1).over(window).
• Compute the difference in days with F.datediff.
• Return user_id, ts, time_since_last_event, sorted by user_id, ts, in result.

Constraints

• The first event of every user has time_since_last_event equal to None.
• datediff counts calendar days, not hours.
• The window must be ordered by ts, or lag would look at a random row.

Examples

Input: timestamps per user Output: user_id | ts | time_since_last_event 1 | 2024-01-01 10:00:00 | None 1 | 2024-01-01 12:00:00 | 0 1 | 2024-01-01 15:00:00 | 0 2 | 2024-01-02 09:00:00 | None 2 | 2024-01-02 10:00:00 | 0 Same calendar day yields datediff 0; first rows stay null.

Topics: lakebench, pyspark, lag, datediff.

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