Project 2: The Customer Report
Who buys, and who stopped.
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.

- 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.
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;โโโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ 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.
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;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโ โ 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.
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;โโโโโโโโโโโโโโโโโโโโโโโโโโโ โ 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.
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