SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 212 of 250

Moderate

Find customers who have never bought

Your task

Return each customer with zero completed orders. Show their ID as customer_id and their name. Sort by customer ID ascending.

A customer may have no orders at all, or only cancelled or refunded orders. Both cases count as having no completed purchase.

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

customer_idname
3Jordan
5Riley
7Taylor
Show a hint

Use NOT EXISTS to test whether the current customer has any order with status = 'completed'.

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…