Find purchases that set a customer record
Your task
Return customer_id, id as order_id, and total_cents for completed orders that set a new record. Include the first completed order. Sequence by date then ID and sort the result by customer ID, date, then order ID.
A purchase sets a record when its amount exceeds every earlier completed purchase by that customer.
Sample data
shop_orders| id | customer_id | ordered_on | channel | status | total_cents |
|---|---|---|---|---|---|
| 201 | 1 | 2025-01-04 | online | completed | 2500 |
| 202 | 2 | 2025-01-10 | store | completed | 1800 |
| 203 | 2 | 2025-01-10 | store | completed | 3200 |
| 204 | 3 | 2025-01-18 | marketplace | refunded | 5000 |
| 205 | 4 | 2025-02-02 | store | completed | 2700 |
| 206 | 1 | 2025-02-04 | online | completed | 4000 |
| 207 | 5 | 2025-02-18 | online | cancelled | 2400 |
| 208 | 6 | 2025-02-28 | online | completed | 1700 |
| 209 | 4 | 2025-03-16 | online | completed | 2900 |
| 210 | 8 | 2025-03-09 | store | completed | 3100 |
Expected output
| customer_id | order_id | total_cents |
|---|---|---|
| 1 | 201 | 2500 |
| 1 | 206 | 4000 |
| 2 | 202 | 1800 |
| 2 | 203 | 3200 |
| 4 | 205 | 2700 |
| 4 | 209 | 2900 |
| 6 | 208 | 1700 |
| 8 | 210 | 3100 |
Show a hint
Exclude rows for which an earlier completed order exists with an equal or greater amount.
Subqueries and CTEs guide
Use a query as a value
An aggregate without GROUP BY produces one row. A single-column subquery can therefore provide a scalar value for a comparison. Here each price is compared with an average calculated across the whole table.
SELECT title, price
FROM albums
WHERE price < (SELECT AVG(price) FROM albums);Ask whether a related row exists
An EXISTS subquery only needs to produce one row to be true. It refers to a.id from the outer query, so the test is evaluated for the current album. The expression in the inner SELECT is immaterial to the existence test.
SELECT a.title
FROM albums AS a
WHERE EXISTS (
SELECT 1
FROM sales AS s
WHERE s.album_id = a.id AND s.quantity > 1
);Name intermediate results with WITH
A common table expression gives an intermediate query a name for a single statement. Here the CTE summarizes sales before joining them to albums. Read the CTE on its own first, then read the final SELECT to see how the summary is used.
WITH totals AS (
SELECT album_id, SUM(quantity) AS copies
FROM sales
GROUP BY album_id
)
SELECT a.title, COALESCE(t.copies, 0) AS copies
FROM albums AS a
LEFT JOIN totals AS t ON t.album_id = a.id;Worked solution
SELECT o.customer_id,o.id AS order_id,o.total_cents FROM shop_orders o WHERE o.status='completed' AND NOT EXISTS (SELECT 1 FROM shop_orders p WHERE p.customer_id=o.customer_id AND p.status='completed' AND (p.ordered_on<o.ordered_on OR (p.ordered_on=o.ordered_on AND p.id<o.id)) AND p.total_cents>=o.total_cents) ORDER BY o.customer_id,o.ordered_on,o.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
