Customers who stopped ordering

Easy

Question

The shop looks at its customers on the fixed day 2026-06-30 (use this date in your query). A customer has churned when their latest order is more than 30 days before 2026-06-30. A latest order exactly 30 days before is not yet churn. A customer who never ordered has not churned, because they were never active.

Return name, last_order_date (the date of the latest order) and days_inactive (the number of days from the latest order to 2026-06-30) for every churned customer. Sort by days_inactive from high to low, then by name.

The order of the rows matters for this question.

join
group by
having
max
date
julianday
churn

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

2 tables

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.

customers2 columns · 8 rowsnot run yet

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

Example rows of the customers table
id	name
1	Aarav
2	Bhumika
3	Chetan
4	Deepa
5	Eshwar
6	Farida
7	Gopal
8	Hema

orders4 columns · 11 rowsnot run yet

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

Example rows of the orders table
id	customer_id	amount	ordered_at
1	1	400	2026-06-20
2	1	250	2026-04-02
3	2	300	2026-05-31
4	3	520	2026-05-30
5	3	130	2026-01-12
6	4	800	2026-03-01
7	4	640	2026-02-10
8	6	150	2026-01-15
9	6	220	2026-06-25
10	7	910	2026-04-02
11	8	100	2026-06-30

About this SQL

SQLite has no DATEDIFF. Dates are text, and julianday(x) turns a date into a number of days, so julianday(a) - julianday(b) is the number of days between them. date('2026-06-30', '-30 days') gives the date 30 days earlier. In HAVING you can use aggregates such as MAX(). Text in the form YYYY-MM-DD compares in date order.

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