medium30 ptsWindow Functions Dates

Repeat Buyers Within 30 Days

A customer is a quick repeat buyer if any of their orders was placed at most 30 days after the order immediately before it. Two orders on the same day count (0 days apart). Orders on the same date are ordered by order_id.

Return columns customer_id, listing each qualifying customer once. Row order does not matter.

Schema

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  amount REAL NOT NULL
);

Sample data

orders

order_id	customer_id	order_date	amount
1	11	2026-01-01	50
2	11	2026-03-15	75
3	12	2026-01-10	20
4	12	2026-02-05	35
5	13	2026-02-01	90
6	11	2026-04-20	15
7	14	2026-01-01	60
8	14	2026-03-01	10
9	14	2026-03-31	12

Hint 1

Use LAG to put each order next to the same customer's previous order.

Hint 2

julianday(a) - julianday(b) is the gap in days.

Good practice for: Data Analyst, Digital Marketing Specialist, Product Manager, Data Scientist

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.