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.
Loading the reference solution for Pro.Why it works
Run your query to see the result set.
Submit to run the checks.
Why it works
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