SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 219 of 250

Hard

Measure next-month buyer retention

Your task

For each first-purchase month from January through March 2025, return cohort_month in YYYY-MM format, first_time_buyers, returning_buyers, and retention_pct. Count each returning customer once and round the 0–100 percentage to one decimal. Sort by cohort month. Treat April as a complete observation month with zero orders.

A buyer joins a cohort in the calendar month of their first completed order. They count as returning if they complete an order in the next calendar month, regardless of the gaps between individual purchases.

Sample data

Table:shop_orders
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

cohort_monthfirst_time_buyersreturning_buyersretention_pct
2025-012150
2025-022150
2025-03100
Show a hint

Find the start of each customer’s first purchase month. Check for a completed order between the start of the following month, inclusive, and the start of the month after that, exclusive.

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…