AI Hints
Homeโ€บSQL Tutorialโ€บProject: The Customer Report
SQL ยท Lesson 86 of 126

Repeat Buyers

How loyal are customers?.

โฑ 2 min readโ–ถ Video at 2:01:46๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

The last question for the customer report. Do people come back? If most customers only buy once, Helen has a problem. If they come back again and again, she's doing something right.

Repeat Buyers
From the video: Repeat Buyers
  • Do people come back?
  • Once, or again and again?
  • A loyalty check

Orders per customer

First, count orders per customer. Then count how many customers have each number of orders. That's a group by on top of a group by, so we use a CTE. Run it. Ninety nine customers bought only once. Fifty three customers have bought eight times.

query.sqlsql
WITH counts AS (
    SELECT customer_id, COUNT(*) AS orders
    FROM orders
    GROUP BY customer_id
)
SELECT orders, COUNT(*) AS customers
FROM counts
GROUP BY orders;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ orders โ”‚ customers โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1      โ”‚ 99        โ”‚
โ”‚ 2      โ”‚ 100       โ”‚
โ”‚ 3      โ”‚ 88        โ”‚
โ”‚ 4      โ”‚ 43        โ”‚
โ”‚ 5      โ”‚ 41        โ”‚
โ”‚ 6      โ”‚ 39        โ”‚
โ”‚ 8      โ”‚ 53        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

One-time versus repeat

Your turn. Using the same CTE, show two numbers in one row. How many customers ordered exactly once, and how many ordered more than once. Pause and try. Here's a solution. Case inside sum, twice. Three hundred and sixty four repeat customers, against ninety nine one time buyers. Helen's customers are loyal.

โœ๏ธ Exercise: One-time versus repeat

One row: one-time buyers vs repeat buyers

Show hint

SUM(CASE WHEN orders = 1 ...), SUM(CASE WHEN orders > 1 ...)

Show solution
solution.sqlsql
WITH counts AS (
    SELECT customer_id, COUNT(*) AS orders
    FROM orders
    GROUP BY customer_id
)
SELECT SUM(CASE WHEN orders = 1 THEN 1 ELSE 0 END) AS one_time,
       SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeat
FROM counts;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ one_time โ”‚ repeat โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 99       โ”‚ 364    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

You built a full customer report. Profiles, segments, and loyalty. Next project, keeping the shelves stocked.

Key takeaways
  • Profiles
  • Segments
  • Repeat buyers
Next up: Project 3: The Stock Alert โ€” which books to reorder