9. Customer lifetime summary
Asked in screens shaped like: Airbnb, Booking, Expedia
Build the customer dimension the CRM team keeps asking for: order count, completed revenue, and first and last order timestamps. Customers with no orders must still appear, with zero revenue rather than NULL.
Requirements
- Columns: customer_id, name, orders, completed_revenue, first_order_at, last_order_at
- Every customer appears, even with no orders
- orders counts order rows; completed_revenue only sums completed orders and is 0 when there are none
- Order by completed_revenue descending, then customer_id
Expected output
customer_id, name, orders, completed_revenue, first_order_at, last_order_at
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
customers
~12 rows
Customer dimension. Every customer has at least one order in this seed.
- customer_idTEXT— primary key
- nameTEXT
- countryTEXT
- signup_dateTEXT— YYYY-MM-DD
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Start from customers and LEFT JOIN orders so unmatched customers survive.
Hint 2
COUNT(o.order_id) skips NULLs; COUNT(*) would wrongly return 1 for a customer with no orders.
Hint 3
Wrap the conditional SUM in COALESCE(..., 0).
SELECT c.customer_id, c.name,
COUNT(o.order_id) AS orders,
ROUND(COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.amount END), 0), 2) AS completed_revenue,
MIN(o.ordered_at) AS first_order_at,
MAX(o.ordered_at) AS last_order_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY completed_revenue DESC, c.customer_id;Why it works
The two details that get flagged in review: COUNT(column) instead of COUNT(*) so zero-order customers read 0, and COALESCE so downstream arithmetic never hits a NULL.
Run your query to see the result set.
Submit to run the checks.
Why it works
The two details that get flagged in review: COUNT(column) instead of COUNT(*) so zero-order customers read 0, and COALESCE so downstream arithmetic never hits a NULL.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems