AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 2
SQL ยท Lesson 99 of 126

Time Between Orders

LAG with PARTITION BY.

โฑ 2 min readโ–ถ Video at 2:19:58๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Time Between Orders
From the video: Time Between Orders
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

The numberevery 105 days
The questionso what?
The actionremind at 90 days
The habitalways ask what's next

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
Next up: Retention โ€” do new customers come back?