Project: Sales Report Queries

Time to put everything together. Imagine you have just joined the shop as its first data analyst. On Monday morning the owner, Meera, sends you a message: "Before the review meeting, can you tell me how we are doing? Total sales, how each month went, who our best customers are, which products sell, where the money comes from, and which customers we should win back."

Every one of those questions can be answered with SQL on the practice database. In this project you will build the report step by step, one query per question, using joins, grouping, subqueries, CTEs and window functions. Each step runs in the browser, so try every query, then change it and see what happens.

Step 1: Agree what "revenue" means

Before writing any report, agree on the definition of its main number. Three decisions shape every query in this project:

• Revenue comes from order_items, as quantity * unit_price. The unit_price is the price the customer actually paid, which may differ from today's products.price (see Normalisation & Table Design).
• Cancelled orders do not count. Order 105 was cancelled, so every query filters with o.status <> 'cancelled'. Pending and shipped orders are included, because the customer has committed to them. A different business might decide otherwise; what matters is that every query uses the same rule.
• Count orders with COUNT(DISTINCT o.order_id). Joining orders to their items repeats each order once per item, so a plain COUNT(*) would count items, not orders.

With those rules, the headline numbers are one query:

SELECT COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       ROUND(1.0 * SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled';

orders | revenue | avg_order_value
-------+---------+----------------
14     | 104474  | 7462.43
(1 row)

The shop has taken 14 orders worth ₹1,04,474, an average of about ₹7,462 per order. The 1.0 * makes sure the division keeps its decimals in every database; in MySQL the revenue is a DECIMAL anyway and would print as 104474.00.

Step 2: Revenue per month

To see the trend, group by month. The order date 2025-04-18 becomes the month 2025-04 with SUBSTR(order_date, 1, 7), which works in MySQL and in the browser:

SELECT SUBSTR(o.order_date, 1, 7) AS month,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY SUBSTR(o.order_date, 1, 7)
ORDER BY month;

month   | orders | revenue
--------+--------+--------
2025-01 | 3      | 23544
2025-02 | 2      | 12947
2025-03 | 3      | 7697
2025-04 | 3      | 43691
2025-05 | 3      | 16595
(5 rows)

The text form YYYY-MM sorts correctly, because the year comes first and the month always has two digits. April is clearly the best month. In a MySQL-only report you could also write DATE_FORMAT(o.order_date, '%Y-%m'), or DATE_FORMAT(o.order_date, '%b %Y') for labels such as Apr 2025 (see String, Number & Date Functions). Be careful with the second form: Apr 2025 sorts alphabetically, before Jan 2025, so keep sorting by the YYYY-MM value.

Step 3: Running total and month-on-month change

Meera wants to know how the year is adding up, and whether each month was better or worse than the one before. Put the monthly query in a CTE, then add two window functions:

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY SUBSTR(o.order_date, 1, 7)
)
SELECT month, revenue,
       SUM(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_last_month
FROM monthly
ORDER BY month;

month   | revenue | running_total | change_vs_last_month
--------+---------+---------------+---------------------
2025-01 | 23544   | 23544         | NULL
2025-02 | 12947   | 36491         | -10597
2025-03 | 7697    | 44188         | -5250
2025-04 | 43691   | 87879         | 35994
2025-05 | 16595   | 104474        | -27096
(5 rows)

• SUM(revenue) OVER (ORDER BY month ...) adds up all months so far, so the last row equals the total from Step 1. That is a useful check that the two queries agree.
• LAG(revenue) reads the previous month's revenue. January has no previous month, so its change is NULL.

The story for the meeting: sales fell for two months, jumped in April thanks to one large monitor order, and settled in May. That single April order is worth asking about; one big customer can make a month look better than the business really is.

Step 4: Top customers

Next, who spends the most? Join customers to their orders and items, group by customer, and sort by total:

SELECT c.first_name, c.last_name, c.city,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC
LIMIT 5;

first_name | last_name | city     | orders | total_spent
-----------+-----------+----------+--------+------------
Sneha      | Gupta     | Delhi    | 1      | 32797
Priya      | Verma     | Jaipur   | 2      | 16899
Aarav      | Sharma    | Rohtak   | 3      | 14944
Rohan      | Mehta     | Mumbai   | 2      | 14397
Isha       | Kapoor    | Gurugram | 1      | 8695
(5 rows)

Group by customer_id, not just by name. Two customers can share a first name, and grouping by the key keeps them apart. The other columns in GROUP BY are there because MySQL requires every selected column to be grouped or aggregated (see GROUP BY).

Sneha is the top customer from a single order, while Aarav comes third with three orders. Both are valuable in different ways: Sneha buys big, Aarav comes back.

Step 5: Best-selling products, and one that never sells

For products, Meera wants both units and revenue. A product can sell many units at a low price or a few at a high one:

SELECT p.product_name, p.category,
       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.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.product_id, p.product_name, p.category
ORDER BY revenue DESC
LIMIT 5;

SELECT p.product_name, p.stock
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE oi.product_id = p.product_id
    AND o.status <> 'cancelled'
);

product_name                | category    | units_sold | revenue
----------------------------+-------------+------------+--------
27-inch Monitor             | Displays    | 3          | 47997
Noise-Cancelling Headphones | Audio       | 2          | 17998
Mechanical Keyboard         | Accessories | 2          | 6998
1TB External SSD            | Storage     | 1          | 6499
USB-C Hub                   | Accessories | 3          | 5697
(5 rows)

product_name    | stock
----------------+------
Graphics Tablet | 12
(1 row)

The monitor earns the most by far. The second query uses NOT EXISTS (see EXISTS, ANY & ALL) to find stock that is not moving: twelve Graphics Tablets, about ₹55,000 of stock at today's price, have never sold. That is a clear action point for the meeting: a discount, a bundle or better product photos.

Step 6: Where the money comes from

Which categories bring in the revenue? A CTE totals each category, and SUM(revenue) OVER () gives the grand total on every row, so each category's share is one division away:

WITH category_revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON oi.product_id = p.product_id
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category
)
SELECT category, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM category_revenue
ORDER BY revenue DESC;

category    | revenue | pct_of_total
------------+---------+-------------
Displays    | 47997   | 45.9
Audio       | 22996   | 22
Accessories | 21987   | 21
Storage     | 9244    | 8.8
Books       | 2250    | 2.2
(5 rows)

Displays bring in almost half of all revenue, even though the shop sells only one display. MySQL prints the percentages with one decimal place, such as 22.0; the browser drops a trailing .0. Multiplying by 100.0 first keeps the division exact in both databases. The percentages add up to 99.9 because of rounding; mention that in the report so nobody thinks money is missing.

Step 7: Customers to win back

Finally, which customers have never completed a purchase? That includes people with no orders at all and people whose only order was cancelled. One NOT EXISTS covers both:

SELECT c.first_name, c.last_name, c.city, c.joined_on
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status <> 'cancelled'
)
ORDER BY c.joined_on;

first_name | last_name | city    | joined_on
-----------+-----------+---------+-----------
Ananya     | Iyer      | Chennai | 2024-03-11
Divya      | Joshi     | Pune    | 2025-01-09
(2 rows)

Ananya cancelled her only order, an SSD, in February; Divya signed up in January and never bought anything. The first might need a friendly message asking what went wrong; the second, a welcome offer. Because the condition o.status <> 'cancelled' sits inside the subquery, a customer with a cancelled order and a delivered order is correctly left out of this list.

Step 8: Save the report as views

The report will be needed again next month. In MySQL, save the important queries as views so that a dashboard, a spreadsheet connection or a colleague can run SELECT * FROM monthly_revenue without copying SQL around:

CREATE OR REPLACE VIEW monthly_revenue AS
SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m');

CREATE OR REPLACE VIEW customer_value AS
SELECT c.customer_id, c.first_name, c.last_name, c.city,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name, c.city;

Keeping the "not cancelled" rule inside the views means every report built on them uses the same definition of revenue, which was the decision you made in Step 1. A reporting account can be given SELECT on these views only (see Users, Privileges & Backups). When the tables grow to many thousands of orders, an index on orders (status, order_date) helps these queries (see Indexes & EXPLAIN).

What you practised

Key points

• Agree on the definition of the main number first, and apply the same filter in every query.
• Use COUNT(DISTINCT order_id) when a join repeats orders once per item.
• SUBSTR(order_date, 1, 7) gives a sortable YYYY-MM month that works in MySQL and the browser.
• CTEs plus window functions give running totals, month-on-month changes and percentages of a total.
• NOT EXISTS finds what is missing: unsold products and customers who never completed a purchase.
• Check reports against each other, such as the final running total against the headline revenue.
• Save finished report queries as views so everyone uses the same logic.

SELECT c.state,
       COUNT(DISTINCT c.customer_id) AS customers,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.state
ORDER BY revenue DESC;

WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT COUNT(*) AS buyers,
       SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END) AS repeat_buyers,
       ROUND(100.0 * SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_pct
FROM order_counts;