PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Date Functions. About 16 minutes. Part of the Pro drill bank.
Parse dd/MM/yyyy strings and compute each customer's average gap in days between consecutive orders. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
orders has customer and order_date_str, a date written as dd/MM/yyyy (so 05/01/2024 is 5 January 2024). For each customer return avg_gap_days: the average number of days between each order and the customer's previous order (orders ordered by date). Two orders on the same day have a gap of 0. A customer with a single order has NULL. Columns: customer, avg_gap_days. Assign the DataFrame to result.
Input: orders customer | order_date_str c1 | 15/01/2024 c1 | 05/01/2024 c1 | 01/02/2024 c2 | 10/03/2024 c3 | 03/01/2024 c3 | 01/01/2024 c3 | 03/01/2024 Output: customer | avg_gap_days c1 | 13.5 c2 | NULL c3 | 1 c1 orders on 5 Jan, 15 Jan and 1 Feb: gaps 10 and 17, average 13.5. c2 has one order so NULL. c3 orders on 1 Jan, 3 Jan and 3 Jan: gaps 2 and 0, average 1.
Topics: lakebench, pyspark, to_date, lag, datediff.
More PySpark interview questions · All interview problems · Learn data engineering
Interview-style drill: Parse dd/MM/yyyy strings and compute each customer's average gap in days between consecutive orders.
`orders` has `customer` and `order_date_str`, a date written as `dd/MM/yyyy` (so `05/01/2024` is 5 January 2024). For each customer return `avg_gap_days`: the average number of days between each order and the customer's previous order (orders ordered by date). Two orders on the same day have a gap of 0. A customer with a single order has NULL. Columns: `customer`, `avg_gap_days`. Assign the DataFrame to `result`.