Compare order values by signup source
Your task
Return source, completed_orders, and avg_order_cents for sources with completed orders. Round the average to two decimals and sort by source.
Average each completed order’s amount when the metric is order value, even if a customer placed several orders.
Sample data
| id | name | signup_date | source |
|---|---|---|---|
| 1 | Avery | 2025-01-02 | organic |
| 2 | Casey | 2025-01-05 | |
| 3 | Jordan | 2025-01-12 | organic |
| 4 | Morgan | 2025-02-01 | |
| 5 | Riley | 2025-02-10 | social |
| 6 | Sam | 2025-02-20 | organic |
| 7 | Taylor | 2025-03-01 | social |
| 8 | Devon | 2025-02-07 |
Expected output
| source | completed_orders | avg_order_cents |
|---|---|---|
| 5 | 2740 | |
| organic | 3 | 2733.33 |
Show a hint
Join customers to completed orders and aggregate AVG(total_cents) by customers.source.
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 c.source,COUNT(*) AS completed_orders,ROUND(AVG(o.total_cents),2) AS avg_order_cents FROM customers c JOIN shop_orders o ON o.customer_id=c.id WHERE o.status='completed' GROUP BY c.source ORDER BY c.source;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
