Customers with No Orders in 90 Days - Medium SQL Date Functions Interview Problem

Problem

The retention team wants to identify at-risk customers for a win-back campaign before they switch to a competitor. Using the customers and orders tables, return id, name, and last_order_date for customers whose most recent order was placed more than 90 days ago, ordered by last_order_date ascending (longest inactive first).

Schema

customers

column	type
id	INTEGER
name	TEXT
email	TEXT
created_at	DATE

orders

column	type
id	INTEGER
customer_id	INTEGER
amount	NUMERIC
created_at	DATE

Sample Data

customers

id	name	email	created_at
1	Alice	alice@example.com	CURRENT_DATE - 365 days
2	Bob	bob@example.com	CURRENT_DATE - 60 days
3	Carol	carol@example.com	CURRENT_DATE - 200 days

orders

id	customer_id	amount	created_at
1	1	100.00	CURRENT_DATE - 200 days
2	2	50.00	CURRENT_DATE - 10 days
3	3	75.00	CURRENT_DATE - 100 days

Expected Output

id	name	last_order_date
1	Alice	(CURRENT_DATE - 200 days)
3	Carol	(CURRENT_DATE - 100 days)

Alice's last order was 200 days ago and Carol's was 100 days ago — both exceed the 90-day threshold. Bob ordered only 10 days ago and is excluded.

Hints