Average days between purchases

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.

Marketing wants to know how often a customer comes back. The table 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, find the number of days between each order and the previous order of the same customer, then take the average. Return customer and avg_gap_days. 2 orders on the same day have a gap of 0. A customer with only one order has no gap, so the average is NULL. The editor creates the table orders and ends with result = orders. Change the last line to build your answer. First turn the text into a real date with F.to_date(column, "dd/MM/yyyy").

Requirements

• Convert order_date_str to a date using the format dd/MM/yyyy.
• Use lag over a window ordered by the date to get the previous order.
• Compute the gap with datediff and average it for each customer.
• Return customer and avg_gap_days in result.

Constraints

• The date format is day first, then month, then year.
• The first order of a customer has no previous order, so its gap is NULL and is ignored by avg.
• Do not sort the text. Sort the real dates.

Examples

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