ShopLite project 3: business report queries

60 min · Intermediate

A business report is a query whose answer someone can act on: which month sold most, who the best customers are, which products to restock. It is only useful if every number is right, so each report is checked by hand against the data.

What you'll build

The ShopLite owner wants a small dashboard. You write reports.sql, which runs after shoplite.sql from project 2. It creates two views that the reports build on (order_totals, one row per order with its total, and monthly_sales), one index for the dashboard's most common filter, and seven reports: monthly sales with a running total and the change from last month, the top 3 customers, revenue by main category, the best seller of each category, revenue by delivery city, a restock list and the customers who never ordered.

Every sales number leaves out cancelled orders, because a cancelled order is not a sale. Before you trust a report, you check at least one of its numbers by hand against the seed rows.

Output of reports.sql (after shoplite.sql)

+---------+--------+-------+-----------+---------------+----------------+
| month   | orders | units | revenue   | running_total | change_vs_last |
+---------+--------+-------+-----------+---------------+----------------+
| 2026-07 |      4 |    11 |  89081.00 |      89081.00 |           NULL |
| 2026-08 |      5 |    12 |  78479.00 |     167560.00 |      -10602.00 |
| 2026-09 |      5 |    11 | 138083.00 |     305643.00 |       59604.00 |
+---------+--------+-------+-----------+---------------+----------------+
+-----+-------------+--------+-----------+-----------+
| rnk | full_name   | orders | spent     | avg_order |
+-----+-------------+--------+-----------+-----------+
|   1 | Arjun Mehta |      2 | 107980.00 |  53990.00 |
|   2 | Kabir Singh |      2 |  92098.00 |  46049.00 |
|   3 | Asha Rao    |      3 |  69334.00 |  23111.33 |
+-----+-------------+--------+-----------+-----------+
+---------------+-------+-----------+
| main_category | units | revenue   |
+---------------+-------+-----------+
| Electronics   |    16 | 291308.00 |
| Fashion       |     6 |   8594.00 |
| Books         |    12 |   5741.00 |
+---------------+-------+-----------+
+-------------+-----------------------+-------+
| category    | product               | units |
+-------------+-----------------------+-------+
| Accessories | boAt Airdopes 141     |     5 |
| Books       | Atomic Habits         |     7 |
| Fashion     | Cotton Kurta          |     4 |
| Laptops     | Lenovo IdeaPad Slim 3 |     2 |
| Mobiles     | Redmi Note 13 5G      |     2 |
+-------------+-----------------------+-------+
+-----------+--------+-----------+
| city      | orders | revenue   |
+-----------+--------+-----------+
| Mumbai    |      2 | 107980.00 |
| New Delhi |      2 |  92098.00 |
| Bengaluru |      3 |  69334.00 |
| Chennai   |      4 |  25142.00 |
| Kochi     |      2 |   5893.00 |
| Pune      |      1 |   5196.00 |
+-----------+--------+-----------+
+-----------+----------------------------+-------+
| sku       | name                       | stock |
+-----------+----------------------------+-------+
| ACC-M331  | Logitech M331 Silent Mouse |     0 |
| LAP-HPV15 | HP Victus 15 Gaming        |     5 |
| MOB-IP15  | iPhone 15 128GB            |     8 |
+-----------+----------------------------+-------+
+------------+------------------------+------------+
| full_name  | email                  | joined_on  |
+------------+------------------------+------------+
| Imran Khan | imran.khan@example.com | 2026-09-01 |
+------------+------------------------+------------+

Requirements

Your program should do all of these.

reports.sql runs after shoplite.sql (the tables and seed rows already exist; the checker uses an empty database named shop, so there is no USE) and can be run again after shoplite.sql rebuilds the database.
View order_totals has one row per order with its units and total; view monthly_sales has orders, units and revenue per month, without cancelled orders.
Column names, in order: monthly report month, orders, units, revenue, running_total, change_vs_last; top customers rnk, full_name, orders, spent, avg_order (equal spends share a rank, ties on spend are listed by name); categories main_category, units, revenue (revenue high to low); best sellers category, product, units (sorted by category, ties on units go to the product name); cities city, orders, revenue (revenue high to low, then city); restock sku, name, stock (lowest stock first, then SKU); never ordered full_name, email, joined_on (by customer id; a customer whose only order was cancelled has still ordered).
The monthly report shows a running total and the change from the previous month. The top 3 customers by money spent show a rank, the number of orders and the average order value.
Revenue is shown by main category (Mobiles counts under Electronics), and the best-selling product of each category is listed.
Revenue by delivery city, a restock list (active products with stock under 10) and the customers who never ordered are listed.
An index on orders (status, ordered_at) exists, and EXPLAIN shows that a dashboard filter uses it.
Every report that can return several rows has an ORDER BY, so the output is the same on every run.

Build it step by step in the Build tab (6 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

Next step

Start building

6 milestones

What you'll build

Every sales number leaves out cancelled orders, because a cancelled order is not a sale. Before you trust a report, you check at least one of its numbers by hand against the seed rows.

Output of reports.sql (after shoplite.sql)

+---------+--------+-------+-----------+---------------+----------------+
| month   | orders | units | revenue   | running_total | change_vs_last |
+---------+--------+-------+-----------+---------------+----------------+
| 2026-07 |      4 |    11 |  89081.00 |      89081.00 |           NULL |
| 2026-08 |      5 |    12 |  78479.00 |     167560.00 |      -10602.00 |
| 2026-09 |      5 |    11 | 138083.00 |     305643.00 |       59604.00 |
+---------+--------+-------+-----------+---------------+----------------+
+-----+-------------+--------+-----------+-----------+
| rnk | full_name   | orders | spent     | avg_order |
+-----+-------------+--------+-----------+-----------+
|   1 | Arjun Mehta |      2 | 107980.00 |  53990.00 |
|   2 | Kabir Singh |      2 |  92098.00 |  46049.00 |
|   3 | Asha Rao    |      3 |  69334.00 |  23111.33 |
+-----+-------------+--------+-----------+-----------+
+---------------+-------+-----------+
| main_category | units | revenue   |
+---------------+-------+-----------+
| Electronics   |    16 | 291308.00 |
| Fashion       |     6 |   8594.00 |
| Books         |    12 |   5741.00 |
+---------------+-------+-----------+
+-------------+-----------------------+-------+
| category    | product               | units |
+-------------+-----------------------+-------+
| Accessories | boAt Airdopes 141     |     5 |
| Books       | Atomic Habits         |     7 |
| Fashion     | Cotton Kurta          |     4 |
| Laptops     | Lenovo IdeaPad Slim 3 |     2 |
| Mobiles     | Redmi Note 13 5G      |     2 |
+-------------+-----------------------+-------+
+-----------+--------+-----------+
| city      | orders | revenue   |
+-----------+--------+-----------+
| Mumbai    |      2 | 107980.00 |
| New Delhi |      2 |  92098.00 |
| Bengaluru |      3 |  69334.00 |
| Chennai   |      4 |  25142.00 |
| Kochi     |      2 |   5893.00 |
| Pune      |      1 |   5196.00 |
+-----------+--------+-----------+
+-----------+----------------------------+-------+
| sku       | name                       | stock |
+-----------+----------------------------+-------+
| ACC-M331  | Logitech M331 Silent Mouse |     0 |
| LAP-HPV15 | HP Victus 15 Gaming        |     5 |
| MOB-IP15  | iPhone 15 128GB            |     8 |
+-----------+----------------------------+-------+
+------------+------------------------+------------+
| full_name  | email                  | joined_on  |
+------------+------------------------+------------+
| Imran Khan | imran.khan@example.com | 2026-09-01 |
+------------+------------------------+------------+

Requirements

Your program should do all of these.

reports.sql runs after shoplite.sql (the tables and seed rows already exist; the checker uses an empty database named shop, so there is no USE) and can be run again after shoplite.sql rebuilds the database.

View order_totals has one row per order with its units and total; view monthly_sales has orders, units and revenue per month, without cancelled orders.

Column names, in order: monthly report month, orders, units, revenue, running_total, change_vs_last; top customers rnk, full_name, orders, spent, avg_order (equal spends share a rank, ties on spend are listed by name); categories main_category, units, revenue (revenue high to low); best sellers category, product, units (sorted by category, ties on units go to the product name); cities city, orders, revenue (revenue high to low, then city); restock sku, name, stock (lowest stock first, then SKU); never ordered full_name, email, joined_on (by customer id; a customer whose only order was cancelled has still ordered).

The monthly report shows a running total and the change from the previous month. The top 3 customers by money spent show a rank, the number of orders and the average order value.

Revenue is shown by main category (Mobiles counts under Electronics), and the best-selling product of each category is listed.

Revenue by delivery city, a restock list (active products with stock under 10) and the customers who never ordered are listed.

An index on orders (status, ordered_at) exists, and EXPLAIN shows that a dashboard filter uses it.

Every report that can return several rows has an ORDER BY, so the output is the same on every run.