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

Project 2: The Customer Report

Who buys, and who stopped.

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

Helen's second question is about people. Who are my customers? Who buys again and again? And who has quietly stopped coming back? Keeping an old customer is much cheaper than finding a new one. So this report could save Helen a lot of money.

Project 2: The Customer Report
From the video: Project 2: The Customer Report
  • Who are my customers?
  • Who keeps buying?
  • Who stopped coming back?

A profile for every customer

We start with a profile. One row per customer, with how many orders they've placed, and when they first and last ordered. We use a left join, so customers with no orders are included, with a count of zero. Run it. Customer four has never ordered, so the dates are empty.

query.sqlsql
SELECT c.customer_id,
       c.first_name,
       COUNT(o.order_id)  AS orders,
       MIN(o.order_date)  AS first_order,
       MAX(o.order_date)  AS last_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customer_id โ”‚ first_name โ”‚ orders โ”‚ first_order โ”‚ last_order โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1           โ”‚ Tara       โ”‚ 2      โ”‚ 2024-11-24  โ”‚ 2025-03-06 โ”‚
โ”‚ 2           โ”‚ Grace      โ”‚ 3      โ”‚ 2024-06-22  โ”‚ 2025-02-19 โ”‚
โ”‚ 3           โ”‚ Finn       โ”‚ 1      โ”‚ 2025-02-05  โ”‚ 2025-02-05 โ”‚
โ”‚ 4           โ”‚ Daniel     โ”‚ 0      โ”‚             โ”‚            โ”‚
โ”‚ 5           โ”‚ Daniel     โ”‚ 0      โ”‚             โ”‚            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

When did they first buy?

When did each customer place their very first order? Min of the order date gives it. Then we count first orders by year. Run it. Two hundred and seventy one customers first bought in twenty twenty four. And one hundred and ninety two were brand new in twenty twenty five.

query.sqlsql
WITH firsts AS (
    SELECT customer_id, MIN(order_date) AS first_order
    FROM orders
    GROUP BY customer_id
)
SELECT strftime('%Y', first_order) AS first_year,
       COUNT(*) AS new_customers
FROM firsts
GROUP BY first_year;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_year โ”‚ new_customers โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024       โ”‚ 271           โ”‚
โ”‚ 2025       โ”‚ 192           โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

How long until they buy?

Customers sign up, but they don't always buy straight away. Julian day turns a date into a number of days, so subtracting gives the gap. Run it. On average, about two hundred and forty days pass between joining and the first order. That's eight months. Helen could send a welcome offer to speed that up.

query.sqlsql
SELECT ROUND(AVG(julianday(f.first_order) - julianday(c.joined)), 0)
         AS avg_days_to_first_order
FROM customers AS c
JOIN (SELECT customer_id, MIN(order_date) AS first_order
      FROM orders
      GROUP BY customer_id) AS f
  ON f.customer_id = c.customer_id;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ avg_days_to_first_order โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 239.0                   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Why customer reports matter

Why do businesses care so much about this? Because finding a new customer costs far more than keeping an existing one. A customer report shows who's loyal, who's new, and who's drifting away. Every one of those groups needs a different message. And it all starts with one simple idea. One row per customer.

New customerscost a lot to find
Loyal customersworth looking after
Drifting customerswin them back
Starts withone row per customer

Summary

A left join plus group by gives every customer a profile. Next, sorting them into segments.

Key takeaways
  • One row per customer
  • LEFT JOIN keeps zeros
  • First and last order
Next up: Customer Segments โ€” active, cooling, lapsed