Find Active Customers in Last 30 Days
Return one row per customer with an order in the inclusive 30-day window ending on the latest order_date in the data.
- Joins
- Subqueries
- Aggregation
- Date analysis
- Filtering
Exercise brief
Understand the request
Customer engagement analyst A deterministic re-engagement report must identify customers active near the end of the available data.
A deterministic re-engagement report must identify customers active near the end of the available data. Return one row per customer with an order in the inclusive 30-day window ending on the latest order_date in the data.
Return
- Return customer_id, customer_name, last_order_date in this exact left-to-right order.
Constraints
- Anchor the window to MAX(order_date), not the current clock.
- Return the customer’s latest qualifying order date.
- Order by last_order_date descending, then customer_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
customers
customer_idINTEGERcustomer_nameTEXTsignup_dateDATE
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Anchor 'recent' to (SELECT MAX(order_date) FROM orders) so results never drift over time.
Hint 2
SQLite shifts dates with date(d, '-30 days'); other engines use INTERVAL or DATEADD.
Hint 3
Group by the customer to collapse multiple orders into one last_order_date.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS last_order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= date((SELECT MAX(order_date) FROM orders), '-30 days') GROUP BY c.customer_id, c.customer_name ORDER BY last_order_date DESC, c.customer_id;Why this works
Rolling-window activity ("last N days") is a churn/engagement staple. Anchoring to MAX(order_date) instead of CURRENT_DATE keeps the exercise reproducible. The date-shift idiom is where engines diverge: SQLite date-modifiers, Postgres/MySQL INTERVAL arithmetic, SQL Server DATEADD.
Success check
Only customers with qualifying activity are returned once, including an order exactly 30 days before the anchor.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_id | customer_name | last_order_date |
|---|---|---|
| 5 | David Brown | 2024-12-05 |
| 1 | John Smith | 2024-11-20 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.