Time Between Orders
LAG with PARTITION BY.
How often do customers come back? Another popular question. For each order, we need the same customer's previous order. That's lag, with partition by customer.

- How often do they come back?
- Each customer's previous order
- LAG + PARTITION BY
Days between orders
Lag, partitioned by customer, and ordered by date, brings each customer's previous order date onto the same row. Julian day turns dates into days, so we can subtract. Run it. On average, customers come back about every hundred and five days. Roughly every three and a half months.
WITH visits AS (
SELECT customer_id,
order_date,
LAG(order_date) OVER (PARTITION BY customer_id
ORDER BY order_date) AS previous
FROM orders
)
SELECT ROUND(AVG(julianday(order_date) - julianday(previous)), 0)
AS avg_days_between_orders
FROM visits
WHERE previous IS NOT NULL;โโโโโโโโโโโโโโโโโโโโโโโโโโโ โ avg_days_between_orders โ โโโโโโโโโโโโโโโโโโโโโโโโโโโค โ 105.0 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโ
Using the answer
A number on its own isn't much use, so always ask, what would we do with it? If customers come back every hundred and five days, Helen could send a reminder at around ninety days, just before they'd normally return. That turns a SQL answer into a business action.
Summary
Lag with partition by compares each row with the previous row for the same person. Next, the question every subscription company asks.
Key takeaways- LAG per customer
- julianday() for gaps
- Average the gaps