Find buyers who purchased through multiple channels
Your task
Return customer_id, name, completed_channels, and spent_cents for buyers with completed purchases in at least two distinct channels. Sort by customer ID.
One buyer may place several orders in a single channel. Count the channels independently of the order total.
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
| customer_id | name | completed_channels | spent_cents |
|---|---|---|---|
| 4 | Morgan | 2 | 5600 |
Show a hint
Group completed orders by customer and use HAVING COUNT(DISTINCT channel) >= 2.
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.id AS customer_id,c.name,COUNT(DISTINCT o.channel) AS completed_channels,SUM(o.total_cents) AS spent_cents FROM customers c JOIN shop_orders o ON o.customer_id=c.id AND o.status='completed' GROUP BY c.id,c.name HAVING COUNT(DISTINCT o.channel)>=2 ORDER BY c.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
