Project: analyzing a shop

Lesson 21 of 21 · Part 5 · Changing data and tables · about 20 minutes

Apart from DATE_FORMAT in "Orders per month", which takes the year and month out of a date, there's no new syntax in this lesson. Like the shop's data analyst, we ask a series of questions and answer each with one query, from easy to hard.

First, two rules that every query below follows:

• Sold means in an order that wasn't cancelled: status <> 'cancelled'.
• An item's amount is quantity * unit_price (the price when it was ordered, not the product's price today).

Try writing each one yourself before reading ours. There's more than one way to write them; a right result is what counts.

Warm-up: stock

Which products are running low? Label them with CASE:

1SELECT name, stock,2    CASE3        WHEN stock = 0 THEN 'sold out'4        WHEN stock < 20 THEN 'running low'5        ELSE 'enough'6    END AS level7FROM products8WHERE stock < 309ORDER BY stock;

Total sales

What state are the orders in?

1SELECT status, COUNT(*) AS orders2FROM orders3GROUP BY status4ORDER BY orders DESC;

How much has the shop sold in all? Join the orders to their items, and leave out the cancelled ones:

1SELECT SUM(oi.quantity * oi.unit_price) AS revenue2FROM orders AS o3JOIN order_items AS oi ON oi.order_id = o.id4WHERE o.status <> 'cancelled';

Best sellers

How many of each product sold, and for how much? Rank them by the number sold and take the top three:

1WITH sales AS (2    SELECT p.id, p.name,3        SUM(oi.quantity) AS sold,4        SUM(oi.quantity * oi.unit_price) AS revenue5    FROM products AS p6    JOIN order_items AS oi ON oi.product_id = p.id7    JOIN orders AS o ON o.id = oi.order_id8    WHERE o.status <> 'cancelled'9    GROUP BY p.id, p.name10),11ranked AS (12    SELECT id, name, sold, revenue,13        RANK() OVER (ORDER BY sold DESC) AS place14    FROM sales15)16SELECT place, name, sold, revenue17FROM ranked18WHERE place <= 319ORDER BY place, id;

The "top three" turned out to be 5 products: three of them sold 3 each and share 3rd place. With ORDER BY sold DESC LIMIT 3, two of those three would be dropped at random; RANK() keeps all the ties.

The other way round: which products never sold at all? After a LEFT JOIN, they're the ones with no match:

1SELECT p.id, p.name2FROM products AS p3LEFT JOIN order_items AS oi ON oi.product_id = p.id4WHERE oi.product_id IS NULL5ORDER BY p.id;

Revenue per city

Customers → orders → items: join the three tables and group by city:

1SELECT c.city, SUM(oi.quantity * oi.unit_price) AS revenue2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5WHERE o.status <> 'cancelled'6GROUP BY c.city7ORDER BY revenue DESC;

1SELECT DISTINCT city FROM customers;

Returning customers

A customer who bought two or more times is a returning customer. Count each customer's orders that weren't cancelled, and keep those with at least 2:

1SELECT c.id, c.name, COUNT(*) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4WHERE o.status <> 'cancelled'5GROUP BY c.id, c.name6HAVING COUNT(*) >= 27ORDER BY orders DESC, c.id;

David Kim (customer 4) did place two orders, but one was cancelled, so he doesn't count. WHERE removes the cancelled orders first, then HAVING removes the customers without enough orders: the two together, just as in lesson 9.

Orders per month

To group by month, first take the "year-month" out of the date. MySQL does it with DATE_FORMAT(order_date, '%Y-%m'), which turns 2025-10-15 into 2025-10 (other databases write it differently: TO_CHAR(order_date, 'YYYY-MM') in PostgreSQL, strftime('%Y-%m', order_date) in SQLite). Work out each order's amount first, then sum up by month:

1WITH totals AS (2    SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    WHERE o.status <> 'cancelled'6    GROUP BY o.id, o.order_date7)8SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,9    COUNT(*) AS orders,10    SUM(total) AS revenue,11    ROUND(AVG(total), 2) AS avg_order12FROM totals13GROUP BY DATE_FORMAT(order_date, '%Y-%m')14ORDER BY month;

October had more than twice as many orders as September, but the average order was a little smaller.

Top customers

How much did each customer spend in all, and what percent of all sales is that? Write "all sales" next to every row with SUM() OVER (), and the division is easy:

1WITH spending AS (2    SELECT c.id, c.name, SUM(oi.quantity * oi.unit_price) AS spent3    FROM customers AS c4    JOIN orders AS o ON o.customer_id = c.id5    JOIN order_items AS oi ON oi.order_id = o.id6    WHERE o.status <> 'cancelled'7    GROUP BY c.id, c.name8)9SELECT name, spent,10    ROUND(100 * spent / SUM(spent) OVER (), 1) AS percent11FROM spending12ORDER BY spent DESC;

The top two customers together make up more than half.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it. Each one is harder than the last.

1SELECT c.name, c.city2FROM customers AS c;

1SELECT category2FROM products;

1SELECT c.city, c.name2FROM customers AS c3ORDER BY c.city;

What you learned

• Settle the rules first (what counts as "sold", how an amount is worked out), and have every query follow them.
• "Each … per …" usually means joining with JOIN, then GROUP BY; "at least …" means HAVING.
• "Never …" means LEFT JOIN with IS NULL; a plain JOIN quietly drops the rows with no match.
• For a "top N" with ties, RANK() is more honest than LIMIT; for "the top of each group", rank with PARTITION BY and keep number 1.
• Build a long analysis step by step with WITH; for shares of a total, SUM() OVER () gives the total.
• DATE_FORMAT(date, '%Y-%m') takes out the year and month (MySQL's way).