SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 224 of 250

Moderate

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

Choose a table
idnamesignup_datesource
1Avery2025-01-02organic
2Casey2025-01-05email
3Jordan2025-01-12organic
4Morgan2025-02-01email
5Riley2025-02-10social
6Sam2025-02-20organic
7Taylor2025-03-01social
8Devon2025-02-07email

Expected output

sourcecompleted_ordersavg_order_cents
email52740
organic32733.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';
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…