First and Last Value

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

First and last event_type per user ordered by ts. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Sometimes you want to show, next to each event, the type of the first event of the user and the type of the latest event seen so far. The window functions first and last do this. For each user_id, in order of ts, add 2 columns: first_event_type, the event_type of the user's first row, and last_event_type, the event_type of the last row up to and including the current row. Return user_id, ts, event_type, first_event_type and last_event_type, 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. Use one window with partitionBy("user_id") and orderBy("ts").

Requirements

• Use a window partitioned by user_id and ordered by ts.
• Add first_event_type with F.first("event_type").over(w).
• Add last_event_type with F.last("event_type").over(w).
• Return the 5 columns, sorted by user_id, ts, in result.

Constraints

• With an orderBy and no frame, the window runs from the first row to the current row.
• Because of that frame, last is the current row's own value, and it changes as you move down.
• ts is a text timestamp that sorts in time order.

Examples

Input: events for user 1 Output: user_id | ts | event_type | first_event_type | last_event_type 1 | 2024-01-01 09:00:00 | view | view | view 1 | 2024-01-01 10:00:00 | purchase | view | purchase 1 | 2024-01-01 11:00:00 | purchase | view | purchase first stays at the earliest event_type; last tracks through the ordered frame.

Topics: lakebench, pyspark, first, last.

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