Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 50 of 50

Project: reports for an online store

Put the course to work: answer ten real business questions about the shop database with joins, grouping, CTEs, window functions, a view and a procedure.

30 minAdvanced and practicalUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCombine joins, aggregation, subqueries, CTEs, window functions, views and routines to answer realistic business questions from one database.

The brief

The shop owner has ten questions. In this project you answer them with the skills from the whole course, using only the shop database. Try each one yourself before opening the answer: write the query, run it, and check it against the output shown.

Business rule for everything below: revenue counts every order whose status is not cancelled. A line's value is quantity * unit_price.

1. What is our revenue, how many orders, and what is the average order value?

This needs the value of each order first, then a summary of those values. A CTE keeps the steps readable:

sql
USE shop;
WITH order_values AS (
  SELECT o.id, SUM(oi.quantity * oi.unit_price) AS value
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id
)
SELECT COUNT(*) AS orders, SUM(value) AS revenue, ROUND(AVG(value), 2) AS average_order_value
FROM order_values;
result
+--------+---------+---------------------+
| orders | revenue | average_order_value |
+--------+---------+---------------------+
|     18 | 1972.32 |              109.57 |
+--------+---------+---------------------+

Remember this total. Every other revenue report should add up to it.

2. Who are our five best customers?

Join customers to their orders and lines, group by customer, and sort by revenue:

sql
SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer, c.city,
       COUNT(DISTINCT o.id) AS orders, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.first_name, c.last_name, c.city
ORDER BY revenue DESC
LIMIT 5;
result
+--------------+----------+--------+---------+
| customer     | city     | orders | revenue |
+--------------+----------+--------+---------+
| Asha Verma   | Delhi    |      4 |  502.58 |
| Jack Miller  | New York |      2 |  351.47 |
| Chloe Dubois | Paris    |      2 |  274.99 |
| Farid Khan   | Mumbai   |      3 |  245.96 |
| Daniel Kim   | Seoul    |      2 |  192.84 |
+--------------+----------+--------+---------+

COUNT(DISTINCT o.id) matters. Joining to the lines repeats each order once per line, so a plain COUNT(*) would count lines instead of orders.

3. Which products sell best?

sql
SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.id, p.name
ORDER BY revenue DESC, p.name
LIMIT 5;
result
+-----------------------------+------------+---------+
| name                        | units_sold | revenue |
+-----------------------------+------------+---------+
| Noise-Cancelling Headphones |          3 |  599.97 |
| Mechanical Keyboard         |          3 |  268.50 |
| SQL Basics Handbook         |          6 |  179.70 |
| USB-C Hub                   |          4 |  156.00 |
| Ceramic Mug Set             |          7 |  131.25 |
+-----------------------------+------------+---------+

4. What share of revenue comes from each category?

A window function can compute each category's percentage of the total in the same query as the category totals:

sql
SELECT cat.name AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       ROUND(100 * SUM(oi.quantity * oi.unit_price) / SUM(SUM(oi.quantity * oi.unit_price)) OVER (), 1) AS percent_of_total
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.id, cat.name
ORDER BY revenue DESC;
result
+-------------+---------+------------------+
| category    | revenue | percent_of_total |
+-------------+---------+------------------+
| Electronics | 1149.42 |             58.3 |
| Books       |  338.70 |             17.2 |
| Home        |  282.25 |             14.3 |
| Sports      |  201.95 |             10.2 |
+-------------+---------+------------------+

The window SUM(...) OVER () adds up the category totals, giving the denominator without a second query. The percentages should add up to about 100.

5. How does revenue change month by month?

sql
WITH monthly AS (
  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS growth_percent
FROM monthly
ORDER BY month;
result
+---------+---------+---------------+----------------+
| month   | revenue | running_total | growth_percent |
+---------+---------+---------------+----------------+
| 2024-01 |  156.89 |        156.89 |           NULL |
| 2024-02 |  392.48 |        549.37 |          150.2 |
| 2024-03 |  200.97 |        750.34 |          -48.8 |
| 2024-04 |  487.92 |       1238.26 |          142.8 |
| 2024-05 |  223.98 |       1462.24 |          -54.1 |
| 2024-06 |  191.35 |       1653.59 |          -14.6 |
| 2024-07 |  318.73 |       1972.32 |           66.6 |
+---------+---------+---------------+----------------+

The last running total equals the revenue from question 1, which is the cross-check we wanted.

6. Which customers have never ordered, and which products never sold?

Marketing wants the first list, purchasing wants the second. Both are "no matching row" questions:

sql
SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
result
+------------+-----------+----------+
| first_name | last_name | country  |
+------------+-----------+----------+
| Isabel     | Santos    | Portugal |
| Liam       | O'Brien   | Ireland  |
+------------+-----------+----------+
sql
SELECT p.name, p.stock
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;
result
+--------------------+-------+
| name               | stock |
+--------------------+-------+
| Puzzle 1000 Pieces |     0 |
+--------------------+-------+

7. What is in the order pipeline?

A pivot with CASE shows each status as a column, with the value tied up in each:

sql
SELECT COUNT(*) AS all_orders,
       SUM(status = 'pending') AS pending,
       SUM(status = 'shipped') AS shipped,
       SUM(status = 'delivered') AS delivered,
       SUM(status = 'cancelled') AS cancelled
FROM orders;
result
+------------+---------+---------+-----------+-----------+
| all_orders | pending | shipped | delivered | cancelled |
+------------+---------+---------+-----------+-----------+
|         20 |       3 |       4 |        11 |         2 |
+------------+---------+---------+-----------+-----------+

In MySQL a comparison returns 1 or 0, so SUM(status = 'pending') is a short way to count rows.

8. How many customers are repeat buyers?

Count customers with two or more non-cancelled orders against all who ordered:

sql
WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT COUNT(*) AS customers_who_ordered,
       SUM(orders >= 2) AS repeat_customers,
       ROUND(100 * SUM(orders >= 2) / COUNT(*), 1) AS repeat_percent
FROM per_customer;
result
+-----------------------+------------------+----------------+
| customers_who_ordered | repeat_customers | repeat_percent |
+-----------------------+------------------+----------------+
|                     9 |                6 |           66.7 |
+-----------------------+------------------+----------------+

9. Make the definition reusable: a view

Questions 1 to 5 all repeat the same join and filter. Give it a name once, so every analyst uses the identical definition of a sale:

sql
CREATE OR REPLACE VIEW v_sales_lines AS
SELECT o.id AS order_id, o.customer_id, o.order_date, p.id AS product_id, p.name AS product,
       cat.name AS category, oi.quantity, oi.unit_price, oi.quantity * oi.unit_price AS line_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT category, COUNT(DISTINCT order_id) AS orders, SUM(line_value) AS revenue
FROM v_sales_lines
GROUP BY category
ORDER BY revenue DESC;
result
+-------------+--------+---------+
| category    | orders | revenue |
+-------------+--------+---------+
| Electronics |      9 | 1149.42 |
| Books       |      5 |  338.70 |
| Home        |      6 |  282.25 |
| Sports      |      4 |  201.95 |
+-------------+--------+---------+

The category figures match question 4, but the query is now three lines.

10. Make it repeatable: a stored procedure

The owner wants the figures for any date range, so wrap the report in a procedure with two parameters:

sql
DROP PROCEDURE IF EXISTS sales_report;
DELIMITER //
CREATE PROCEDURE sales_report(IN p_from DATE, IN p_to DATE)
BEGIN
  SELECT category, COUNT(DISTINCT order_id) AS orders, SUM(quantity) AS units, SUM(line_value) AS revenue
  FROM v_sales_lines
  WHERE order_date >= p_from AND order_date < p_to
  GROUP BY category
  ORDER BY revenue DESC;
END //
DELIMITER ;

CALL sales_report('2024-04-01', '2024-07-01');
result
+-------------+--------+-------+---------+
| category    | orders | units | revenue |
+-------------+--------+-------+---------+
| Electronics |      3 |     6 |  467.97 |
| Books       |      2 |     6 |  199.30 |
| Home        |      2 |     6 |  139.00 |
| Sports      |      2 |     5 |   96.98 |
+-------------+--------+-------+---------+

The half-open range (>= from AND < to) means a report for April to June is '2024-04-01' to '2024-07-01', the date after the end, which also works for datetime columns.

Checking your work

A good analyst never trusts a number once. Run cross-checks:

sql
SELECT (SELECT SUM(line_value) FROM v_sales_lines) AS from_view,
       (SELECT SUM(oi.quantity * oi.unit_price) FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled') AS from_tables;
result
+-----------+-------------+
| from_view | from_tables |
+-----------+-------------+
|   1972.32 |     1972.32 |
+-----------+-------------+

The two figures must be equal. When they are not, look for a join that multiplied rows or a filter that differs.

Where to go next

You can now design a database, fill it, query it and make it fast. Good next steps are building a small application on top of it with PHP, Python or Node.js, learning an ORM, practising on public datasets, and reading about replication and partitioning when data grows. The final exam checks the whole course.

Common mistakes

  • Counting lines when you meant orders, because joins repeat rows.
  • Different filters in different reports, so totals never match.
  • Forgetting to exclude cancelled orders.
  • Rounding too early in a chain of calculations.
  • Not cross-checking totals between queries.

Exercise

Extend the project with three more questions, using the v_sales_lines view: (1) revenue per country of the customer (join customers), (2) the single best-selling product in each category by units, using a window function, and (3) the average number of days between a customer's first and last non-cancelled order, for customers with at least two orders.

Show one possible answer
sql
SELECT c.country, SUM(v.line_value) AS revenue
FROM v_sales_lines v
JOIN customers c ON c.id = v.customer_id
GROUP BY c.country
ORDER BY revenue DESC, c.country;

WITH units AS (
  SELECT category, product, SUM(quantity) AS units
  FROM v_sales_lines
  GROUP BY category, product
), ranked AS (
  SELECT category, product, units, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product) AS rn
  FROM units
)
SELECT category, product, units FROM ranked WHERE rn = 1 ORDER BY category;

SELECT ROUND(AVG(DATEDIFF(last_order, first_order)), 1) AS average_days_between
FROM (
  SELECT customer_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
  HAVING COUNT(*) >= 2
) AS spans;
result
+-------------+---------+
| country     | revenue |
+-------------+---------+
| India       |  833.52 |
| USA         |  351.47 |
| France      |  274.99 |
| South Korea |  192.84 |
| UK          |  136.00 |
| Japan       |  128.50 |
| Nigeria     |   55.00 |
+-------------+---------+
+-------------+---------------------+-------+
| category    | product             | units |
+-------------+---------------------+-------+
| Books       | SQL Basics Handbook |     6 |
| Electronics | Wireless Mouse      |     5 |
| Home        | Ceramic Mug Set     |     7 |
| Sports      | Water Bottle        |     6 |
+-------------+---------------------+-------+
+----------------------+
| average_days_between |
+----------------------+
|                117.2 |
+----------------------+

Key points

  • Real reporting questions combine several skills: joins to connect tables, aggregates to summarise, and filters to exclude cancelled orders.
  • Decide the business rule first (for example, cancelled orders do not count as revenue) and apply it consistently.
  • Wrap repeated logic in a view or a stored procedure so every report uses the same definition.
  • Check your answers: totals from different queries should agree with each other.

Check yourself

1. Why does the revenue report exclude orders with status cancelled?
2. Which tool answers 'who has never ordered' most safely?
3. What is the main benefit of putting the sales-line join into a view?
4. Two reports give different revenue totals. What should you do first?

Final exam and certificate

Finish all 50 lessons to unlock a 15-question exam on MySQL. Pass it and you get a certificate with a public link you can share.

Sign in to take the exam