Average Days Between Purchases

Difficulty: Medium · Topics: lag, time-between-events, aggregation · Asked at: Amazon, Starbucks, Chewy

Problem

For each customer with at least two purchases, return customer_id, purchases, avg_days_between (average gap between consecutive purchase dates, 1 decimal) and max_gap_days. Order by customer_id.

Schema and sample data

CREATE TABLE purchases (customer_id INTEGER, purchase_date TEXT);
INSERT INTO purchases VALUES
(1,'2026-01-01'),(1,'2026-01-11'),(1,'2026-01-31'),
(2,'2026-02-01'),
(3,'2026-01-05'),(3,'2026-01-06'),(3,'2026-03-07'),(3,'2026-03-08');

Expected output

Hints

Solution

WITH g AS (
  SELECT customer_id,
         julianday(purchase_date) - julianday(LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date)) AS gap
  FROM purchases
)
SELECT customer_id, COUNT(*) AS purchases,
       ROUND(AVG(gap), 1) AS avg_days_between,
       CAST(MAX(gap) AS INTEGER) AS max_gap_days
FROM g
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Explanation

Shortcut worth mentioning: the average consecutive gap equals (last − first) / (n − 1) (customer 1: 30/2 = 15). The mean hides bimodal behaviour (customer 3: three gaps of 1, 60, 1 days → mean 20.7), so max_gap_days or a median is often more useful for churn definitions.