Row Number for Deduplication

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

Keep the earliest row per user_id + event_type with row_number. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Duplicate rows are common in event data. Here a user can have several events of the same type, and you only want the earliest one for each user and event type. Number the rows inside each group of (user_id, event_type) with row_number(), ordered by ts from the oldest to the newest. Keep only the rows where the number is 1. Then drop the helper column. Sort the final table by user_id and then event_type. The editor creates the table df and ends with result = df. Change the last line so result has 1 row per user and event type.

Requirements

• Use row_number() over a window partitioned by user_id and event_type.
• Order the window by ts ascending, so the earliest event is number 1.
• Keep only rn == 1 and drop the rn column.
• Sort by user_id, event_type and store the DataFrame in result.

Constraints

• df has the columns user_id, event_type, amount and ts.
• ts is a text timestamp in a format that sorts correctly as text.
• The result has the same columns as df. The rn helper column is not in the output.

Examples

Input: duplicate keyed events Output: user_id | event_type | amount | ts 1 | purchase | 40.0 | 2024-01-01 10:00:00 1 | view | 10.0 | 2024-01-01 09:00:00 2 | purchase | 50.0 | 2024-01-02 08:00:00 Earliest ts wins inside each partition.

Topics: lakebench, pyspark, row_number, window.

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