Average days between orders

Medium

Question

The orders table has the date of each order (ordered_on, like 2026-03-15) and the customer who made it.

For every customer, look at their orders in date order and find the number of days between each order and the order before it. Then take the average of those gaps. Two orders on the same day have a gap of 0 days, and both are real orders. If two orders have the same date, the one with the smaller id comes first.

Customers with only one order have no gap, so leave them out. Return customer_id and avg_gap_days (the average gap in days, rounded to 1 decimal). Sort by customer_id from low to high.

The order of the rows matters for this question.

window function
lag
julianday
date
avg
round
partition by
gaps

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders4 columns · 12 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, ordered_on TEXT NOT NULL, amount INTEGER NOT NULL);

Example rows of the orders table
id	customer_id	ordered_on	amount
1	1	2026-01-01	500
2	1	2026-01-11	300
3	2	2026-01-03	800
4	3	2026-01-20	450
5	2	2026-01-03	120
6	1	2026-01-16	700
7	4	2026-03-01	250
8	2	2026-02-02	640
9	4	2026-03-08	900
10	1	2026-02-05	410
11	5	2026-01-10	300
12	5	2026-03-11	380

About this SQL

SQLite has no DATEDIFF. julianday(date) turns a date text into a day number, so the difference of two julianday values is the number of days between the dates. LAG(column) OVER (PARTITION BY ... ORDER BY ...) gives the value of the row before in the same group, and NULL for the first row of the group. AVG skips NULL values. ROUND returns a decimal number.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready