Interview Question Bank · LAG · date subtraction · AVG over a window result
14. Average days between a customer's orders
MediumE-commerceQuestion #278
Concept: LAG · date subtraction · AVG over a window resultshow
LAG(order_date) OVER (PARTITION BY customer ORDER BY order_date) gives each order the date of the same customer's previous order, and subtracting two dates gives whole days. A customer's first order has no previous one, so its gap is NULL, and AVG skips NULLs. Aggregates can't wrap a window function directly, so compute the gaps in a CTE and average them outside.
Example (different data):
WITH g AS (
SELECT machine_id,
serviced_on - LAG(serviced_on) OVER (PARTITION BY machine_id ORDER BY serviced_on) AS days_since
FROM maintenance_log
)
SELECT machine_id, ROUND(AVG(days_since), 1) AS avg_days
FROM g
GROUP BY machine_id;Your task
Brindlewood Market's lifecycle team times reminder emails to how often each customer usually reorders. They want every repeat customer's average gap between completed orders.
For every customer with at least two completed orders, return name, completed_orders and avg_gap_days (the average number of days between consecutive completed orders, rounded to 1 decimal). Cancelled orders don't count. Sort by avg_gap_days, then name.
Starting Postgres in your browser…
Checking your access…