Display completed amounts in currency units
Your task
Return id and amount_units for each completed order, converting cents to currency units by dividing by 100. Sort by ID.
Divide integer cents by a decimal value to preserve the fractional currency units.
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
| id | amount_units |
|---|---|
| 201 | 25 |
| 202 | 18 |
| 203 | 32 |
| 205 | 27 |
| 206 | 40 |
| 208 | 17 |
| 209 | 29 |
| 210 | 31 |
Show a hint
Use total_cents / 100.0.
Customer and sales analytics guide
Define which orders count
This part of the record shop database tracks customer orders separately from the earlier album sales table. A completed order contributes its full price in cents. Refunded orders are fully refunded, and cancelled orders contribute zero. Decide whether you need to keep all statuses for a report or count only completed orders.
SELECT status, COUNT(*) AS orders
FROM shop_orders
GROUP BY status
ORDER BY status;Group at the customer level first
A customer can have several orders. Summarize by customer ID before counting customers or averaging customer-level values. To include customers with no completed orders, start with customers and put the completion filter in the LEFT JOIN condition.
SELECT c.id, COUNT(o.id) AS completed_orders
FROM customers AS c
LEFT JOIN shop_orders AS o
ON o.customer_id = c.id AND o.status = 'completed'
GROUP BY c.id
ORDER BY c.id;Choose a date boundary
Order and signup dates are stored as YYYY-MM-DD. A half-open date range includes its start and excludes its end. The example counts January orders by including January 1 and stopping before February 1.
SELECT COUNT(*) AS january_orders
FROM shop_orders
WHERE ordered_on >= '2025-01-01' AND ordered_on < '2025-02-01';Worked solution
SELECT id,total_cents/100.0 AS amount_units FROM shop_orders WHERE status='completed' ORDER BY id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
