How many days between each customer's orders?

How many days were there between each customer's orders? LAG brings the customer's previous order date onto this row, and one date minus another gives days.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

LAG on dates

SELECT customer_id,
       order_date,
       order_date - LAG(order_date) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
       ) AS days_since_last
FROM orders

Result:

customer_id	order_date	days_since_last
C101	2026-02-03
C101	2026-02-11	8
C101	2026-03-02	19

Step by step

A customer's first order has no previous one: empty.

SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders

No join.

SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders

Every order counts.

SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders

LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date).

SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders

Practice

Try each one in your head first, then open the answer.

1. How many days were there between each customer's orders? Show customer_id, order_date and days_since_last.

SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders

2. What's each customer's average gap between orders, in days? Show customer_id and avg_gap.

WITH g AS (SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders) SELECT customer_id, AVG(days_since_last) AS avg_gap FROM g WHERE days_since_last IS NOT NULL GROUP BY customer_id

3. How many days were there between each store's orders? Show store, order_date and days_since_last.

SELECT store, order_date, order_date - LAG(order_date) OVER (PARTITION BY store ORDER BY order_date) AS days_since_last FROM orders

4. Which repeat orders came more than 20 days after the customer's last one? Show customer_id, order_date and days_since_last.

WITH g AS (SELECT customer_id, order_date, order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last FROM orders) SELECT customer_id, order_date, days_since_last FROM g WHERE days_since_last > 20

5. How many days until each customer's next order? Show customer_id, order_date and days_to_next.

SELECT customer_id, order_date, LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) - order_date AS days_to_next FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed