Mini project: ShopLite monthly sales report

60 min · Intermediate

A monthly sales report is a handful of grouped queries over the same rows, each with a total at the bottom, and one plain query that proves the totals are right.

What you'll build

The ShopLite owner wants one script that prints the store's sales report: revenue, orders, units and average order value for every month; revenue by category; and revenue by city and month with a subtotal for each city. Revenue counts every order line whose order is not CANCELLED, and amounts use Indian digit grouping, such as 1,96,453.00.

In the practice database the order dates, cities, categories and prices live in four different tables, and joining them is the next module. So the team has exported one flat sales sheet: a table shoplite_sales with one row per order line, holding the same 15 orders. The table is already created and filled for you by data/shop.sql in your repository, so your queries.sql only runs the reports on it. It ends with a check that the report totals match a plain SUM over the raw rows.

Output

+---------+--------+-------+--------------+-----------+
| month   | orders | units | revenue      | avg_order |
+---------+--------+-------+--------------+-----------+
| 2025-01 |      2 |     7 |    22,544.00 | 11,272.00 |
| 2025-02 |      2 |     4 |    66,037.00 | 33,018.50 |
| 2025-03 |      3 |     8 |    72,083.00 | 24,027.67 |
| 2025-04 |      3 |     6 |    24,594.00 |  8,198.00 |
| 2025-05 |      2 |     4 |     6,298.00 |  3,149.00 |
| 2025-06 |      1 |     5 |     4,897.00 |  4,897.00 |
| Total   |     13 |    34 |  1,96,453.00 | 15,111.77 |
+---------+--------+-------+--------------+-----------+
+----------------+--------+--------------+
| category       | orders | revenue      |
+----------------+--------+--------------+
| Laptops        |      2 |  1,07,980.00 |
| Mobiles        |      3 |    55,997.00 |
| Accessories    |      7 |    16,539.00 |
| Fashion        |      2 |    10,194.00 |
| Books          |      3 |     4,145.00 |
| (no category)  |      1 |     1,000.00 |
| Toys           |      1 |       598.00 |
| All categories |     13 |  1,96,453.00 |
+----------------+--------+--------------+
+------------+-------------+--------------+
| city       | month       | revenue      |
+------------+-------------+--------------+
| Bengaluru  | 2025-01     |    20,597.00 |
| Bengaluru  | 2025-02     |    43,839.00 |
| Bengaluru  | 2025-04     |     4,398.00 |
| Bengaluru  | Subtotal    |    68,834.00 |
| Chennai    | 2025-01     |     1,947.00 |
| Chennai    | 2025-03     |     1,897.00 |
| Chennai    | 2025-06     |     4,897.00 |
| Chennai    | Subtotal    |     8,741.00 |
| Delhi      | 2025-02     |    22,198.00 |
| Delhi      | 2025-04     |    19,298.00 |
| Delhi      | 2025-05     |     4,998.00 |
| Delhi      | Subtotal    |    46,494.00 |
| Kochi      | 2025-04     |       898.00 |
| Kochi      | Subtotal    |       898.00 |
| Mumbai     | 2025-03     |    64,990.00 |
| Mumbai     | Subtotal    |    64,990.00 |
| Pune       | 2025-03     |     5,196.00 |
| Pune       | 2025-05     |     1,300.00 |
| Pune       | Subtotal    |     6,496.00 |
| All cities | Grand total |  1,96,453.00 |
+------------+-------------+--------------+
+---------------+----------------+-------------------+
| lines_counted | orders_counted | revenue_plain_sum |
+---------------+----------------+-------------------+
|            23 |             13 |         196453.00 |
+---------------+----------------+-------------------+
+------------+--------+
| status     | orders |
+------------+--------+
| CANCELLED  |      2 |
| DELIVERED  |      8 |
| PLACED     |      3 |
| SHIPPED    |      2 |
| All orders |     15 |
+------------+--------+

Requirements

Your program should do all of these.

queries.sql only reads the table shoplite_sales (one row per order line, created by data/shop.sql); it does not create or fill any table. sale_month, such as 2025-01, is already generated from order_date.
Result 1, the monthly report: columns month, orders (COUNT(DISTINCT order_id)), units, revenue and avg_order (revenue divided by orders), one row per month in date order and a last row labelled Total.
Result 2, the category report: columns category, orders and revenue, from biggest to smallest revenue (equal revenue: by category name). A product with no category shows as (no category), and the last row is All categories.
Result 3, the city report: columns city, month and revenue, cities in name order with their months in date order, a Subtotal row after each city's months, and a last row All cities | Grand total.
Cancelled orders are removed in WHERE in every revenue report; rollup rows are labelled with GROUPING(), never with COALESCE.
Money in results 1 to 3 is printed with FORMAT(x, 2, 'en_IN') and padded to the left with LPAD (12 characters for revenue, 9 for avg_order) so the amounts line up on the right.
Result 4, the check: columns lines_counted, orders_counted and revenue_plain_sum, a plain COUNT(*), COUNT(DISTINCT order_id) and SUM over the non-cancelled rows, unformatted.
Result 5, orders per status: columns status and orders, statuses in name order and a last row All orders, counting cancelled orders too.

Build it step by step in the Build tab (5 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.

What's next

You finished the preview of Aggregates & Grouping

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Output

+---------+--------+-------+--------------+-----------+
| month   | orders | units | revenue      | avg_order |
+---------+--------+-------+--------------+-----------+
| 2025-01 |      2 |     7 |    22,544.00 | 11,272.00 |
| 2025-02 |      2 |     4 |    66,037.00 | 33,018.50 |
| 2025-03 |      3 |     8 |    72,083.00 | 24,027.67 |
| 2025-04 |      3 |     6 |    24,594.00 |  8,198.00 |
| 2025-05 |      2 |     4 |     6,298.00 |  3,149.00 |
| 2025-06 |      1 |     5 |     4,897.00 |  4,897.00 |
| Total   |     13 |    34 |  1,96,453.00 | 15,111.77 |
+---------+--------+-------+--------------+-----------+
+----------------+--------+--------------+
| category       | orders | revenue      |
+----------------+--------+--------------+
| Laptops        |      2 |  1,07,980.00 |
| Mobiles        |      3 |    55,997.00 |
| Accessories    |      7 |    16,539.00 |
| Fashion        |      2 |    10,194.00 |
| Books          |      3 |     4,145.00 |
| (no category)  |      1 |     1,000.00 |
| Toys           |      1 |       598.00 |
| All categories |     13 |  1,96,453.00 |
+----------------+--------+--------------+
+------------+-------------+--------------+
| city       | month       | revenue      |
+------------+-------------+--------------+
| Bengaluru  | 2025-01     |    20,597.00 |
| Bengaluru  | 2025-02     |    43,839.00 |
| Bengaluru  | 2025-04     |     4,398.00 |
| Bengaluru  | Subtotal    |    68,834.00 |
| Chennai    | 2025-01     |     1,947.00 |
| Chennai    | 2025-03     |     1,897.00 |
| Chennai    | 2025-06     |     4,897.00 |
| Chennai    | Subtotal    |     8,741.00 |
| Delhi      | 2025-02     |    22,198.00 |
| Delhi      | 2025-04     |    19,298.00 |
| Delhi      | 2025-05     |     4,998.00 |
| Delhi      | Subtotal    |    46,494.00 |
| Kochi      | 2025-04     |       898.00 |
| Kochi      | Subtotal    |       898.00 |
| Mumbai     | 2025-03     |    64,990.00 |
| Mumbai     | Subtotal    |    64,990.00 |
| Pune       | 2025-03     |     5,196.00 |
| Pune       | 2025-05     |     1,300.00 |
| Pune       | Subtotal    |     6,496.00 |
| All cities | Grand total |  1,96,453.00 |
+------------+-------------+--------------+
+---------------+----------------+-------------------+
| lines_counted | orders_counted | revenue_plain_sum |
+---------------+----------------+-------------------+
|            23 |             13 |         196453.00 |
+---------------+----------------+-------------------+
+------------+--------+
| status     | orders |
+------------+--------+
| CANCELLED  |      2 |
| DELIVERED  |      8 |
| PLACED     |      3 |
| SHIPPED    |      2 |
| All orders |     15 |
+------------+--------+

Requirements

Your program should do all of these.

queries.sql only reads the table shoplite_sales (one row per order line, created by data/shop.sql); it does not create or fill any table. sale_month, such as 2025-01, is already generated from order_date.

Result 1, the monthly report: columns month, orders (COUNT(DISTINCT order_id)), units, revenue and avg_order (revenue divided by orders), one row per month in date order and a last row labelled Total.

Result 2, the category report: columns category, orders and revenue, from biggest to smallest revenue (equal revenue: by category name). A product with no category shows as (no category), and the last row is All categories.

Result 3, the city report: columns city, month and revenue, cities in name order with their months in date order, a Subtotal row after each city's months, and a last row All cities | Grand total.

Cancelled orders are removed in WHERE in every revenue report; rollup rows are labelled with GROUPING(), never with COALESCE.

Money in results 1 to 3 is printed with FORMAT(x, 2, 'en_IN') and padded to the left with LPAD (12 characters for revenue, 9 for avg_order) so the amounts line up on the right.

Result 4, the check: columns lines_counted, orders_counted and revenue_plain_sum, a plain COUNT(*), COUNT(DISTINCT order_id) and SUM over the non-cancelled rows, unformatted.

Result 5, orders per status: columns status and orders, statuses in name order and a last row All orders, counting cancelled orders too.