Monthly Sales Report and Top Customers SQL Use Case

This lesson functions as a capstone use case for the entire GROUP BY and aggregation module, tackling a realistic, end-to-end business request exactly as it might arrive from a manager: 'I need a monthly sales report, and I also want to know who our top customers are.' Solving this properly requires combining nearly every concept covered in this module — JOIN with GROUP BY, multiple grouping columns, HAVING, ROLLUP for subtotals, and careful handling of NULLs — into a small number of polished, production-ready queries.

Key Definitions

• Monthly sales report: A summary report showing total revenue (and often related metrics) broken down by calendar month.
• Top customers report: A ranked list of customers based on a metric such as total spend or number of orders, typically filtered to show only the highest-value segment.
• Date function: A SQL function, such as MONTH() or DATE_FORMAT(), used to extract or format a specific part of a date value, often used as a grouping column for time-based reports.
• End-to-end business case: A realistic, complete scenario requiring multiple SQL concepts to be combined together to fully satisfy a real business request.

What You'll Learn

• Design and populate a realistic sales schema suitable for monthly reporting and customer ranking.
• Build a monthly revenue report broken down by month, using GROUP BY and date functions together.
• Identify top customers by total spend using GROUP BY, JOIN, and HAVING together.
• Add ROLLUP-based subtotals and a grand total to the monthly sales report for a polished, presentation-ready output.
• Practice combining multiple module concepts into a single, cohesive real-world deliverable.

Detailed Explanation

The schema for this use case consists of two tables: customers (customer_id, customer_name, city) and orders (order_id, customer_id, order_date, order_amount). Both reporting needs — the monthly sales report and the top customers report — draw from this same underlying data, demonstrating how a single, well-designed schema can answer multiple distinct business questions through different GROUP BY strategies.

For the monthly sales report, the key technique is extracting the month from each order's order_date using MySQL's DATE_FORMAT() function (commonly formatted as '%Y-%m' to group by year and month together, avoiding the mistake of combining all Januaries across different years into a single group). Grouping by this formatted month string, and applying SUM on order_amount, produces one revenue total per calendar month. Adding WITH ROLLUP to this GROUP BY then automatically appends a final grand total row summarizing the entire reporting period, exactly the kind of report a finance team would expect to review at a glance.

For the top customers report, the key technique is joining orders to customers on customer_id (since customer_name lives in the customers table, not orders), grouping by customer_id and customer_name together (grouping by the unique ID protects against name collisions, as covered in lesson 6.7), and computing both COUNT(order_id) for order frequency and SUM(order_amount) for total spend. A HAVING clause then filters this result down to only customers whose total spend exceeds a meaningful threshold, and ORDER BY combined with LIMIT narrows the report down to a genuine 'top N' list, such as the top 10 customers by revenue.

This use case deliberately weaves together date-based grouping, JOIN-based cross-table aggregation, HAVING-based filtering, and ROLLUP-based subtotaling — the full toolkit of this module — into two realistic, complete, business-ready query deliverables.

Visual Summary

Draw two side-by-side report mockups. Left: 'Monthly Sales Report' showing rows for '2026-01', '2026-02', '2026-03', each with a revenue figure, followed by a bolded 'Grand Total' row at the bottom (from ROLLUP). Right: 'Top Customers Report' showing a ranked list of customer names with their total spend and order count, sorted highest to lowest, with a cutoff line after the top 10, captioned 'Both reports are built from the same underlying orders and customers tables, using different GROUP BY strategies.'

Quick Reference

Report	Grouping Strategy	Key Techniques Used
Monthly Sales Report	GROUP BY formatted year-month string	DATE_FORMAT(), SUM, WITH ROLLUP for grand total
Top Customers Report	GROUP BY customer_id, customer_name	JOIN, COUNT, SUM, HAVING, ORDER BY, LIMIT

SQL Example

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY AUTO_INCREMENT,
  customer_name VARCHAR(100),
  city          VARCHAR(100)
);

CREATE TABLE orders (
  order_id      INT PRIMARY KEY AUTO_INCREMENT,
  customer_id   INT,
  order_date    DATE,
  order_amount  DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- MONTHLY SALES REPORT with grand total via ROLLUP
SELECT
  IFNULL(DATE_FORMAT(order_date, '%Y-%m'), 'Grand Total') AS sales_month,
  SUM(order_amount) AS total_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m') WITH ROLLUP;

-- TOP CUSTOMERS REPORT: top 10 by total spend, minimum 5000 threshold
SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id)   AS number_of_orders,
  SUM(o.order_amount) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.order_amount) > 5000
ORDER BY total_spend DESC
LIMIT 10;

The monthly sales report groups orders by a formatted year-month string extracted from order_date, sums revenue per month, and uses WITH ROLLUP to add a labeled grand total row summarizing the entire period. The top customers report joins customers to orders, groups by the safe combination of customer_id and customer_name, computes both order count and total spend per customer, filters with HAVING to only include customers spending over 5000, sorts by total spend descending, and uses LIMIT to return only the top 10 — a complete, business-ready customer ranking in a single query.

Real-World Examples

• Finance departments run monthly sales reports structurally identical to this example every reporting period to track revenue trends over time.
• Sales and marketing teams use top-customer reports like this one to identify VIP customers for loyalty programs, personalized outreach, or account management prioritization.
• E-commerce platforms combine both report types into a single executive dashboard, showing overall revenue trends alongside the specific customers driving that revenue.
• SaaS companies adapt this exact pattern to build monthly recurring revenue (MRR) reports alongside top-account reports for customer success teams.
• Retail chains use this combined reporting approach to align monthly performance reviews with recognition or rewards for their highest-spending loyalty program members.

Common Mistakes to Avoid

• Using MONTH() alone instead of a year-and-month combined format, incorrectly merging the same month across multiple years.
• Grouping the top customers report by customer_name alone instead of customer_id, risking incorrect merging of same-named customers.
• Forgetting to add LIMIT after ORDER BY when a 'top N' report is specifically requested, returning the entire sorted list instead.
• Omitting HAVING when a minimum spend threshold is part of the actual business requirement, showing low-value customers who should have been filtered out.

Interview Questions

Q1. How would you build a monthly sales report from an orders table with a single order_date column?

Use DATE_FORMAT(order_date, '%Y-%m') to extract a combined year-month string from each order date, group by this formatted string, and apply SUM on the order_amount column to get one total per calendar month.

Q2. Why is DATE_FORMAT with '%Y-%m' preferred over just extracting the month number alone for a multi-year report?

Extracting only the month number (such as with MONTH()) would incorrectly combine January of one year with January of every other year into a single group. Including the year in the formatted string ensures each calendar month in each specific year gets its own distinct group.

Q3. How would you build a 'top 10 customers by total spend' report?

Join customers to orders on customer_id, group by customer_id and customer_name together, compute SUM(order_amount) as total spend, optionally filter with HAVING for a minimum spend threshold, sort by total spend descending with ORDER BY, and limit the result to 10 rows using LIMIT.

Q4. How would you add a grand total row to the monthly sales report?

Add WITH ROLLUP to the end of the GROUP BY clause used for the monthly report, which automatically appends a final summary row representing the total revenue across the entire reporting period, and optionally label it clearly using IFNULL or GROUPING().

Practice MCQs

1. Which function is used to extract a combined year-month string from a date column for grouping?

1. MONTH()
2. DATE_FORMAT(date_column, '%Y-%m')
3. YEAR()
4. NOW()

Answer: B. DATE_FORMAT(date_column, '%Y-%m')

Explanation: DATE_FORMAT with the '%Y-%m' pattern extracts both the year and month together, ensuring each specific calendar month in each year forms its own distinct group.

2. Why not just use MONTH(order_date) alone for a multi-year monthly sales report?

1. It causes a syntax error
2. It would incorrectly combine the same month across different years into one group
3. It only works with ROLLUP
4. It cannot be used with SUM

Answer: B. It would incorrectly combine the same month across different years into one group

Explanation: MONTH() alone extracts only the month number, ignoring the year, which would merge January 2025 and January 2026 into a single, misleading group.

3. To find the top 10 customers by total spend, which combination of clauses is needed?

1. GROUP BY and WHERE only
2. JOIN, GROUP BY, ORDER BY, and LIMIT
3. HAVING alone
4. DISTINCT and ORDER BY

Answer: B. JOIN, GROUP BY, ORDER BY, and LIMIT

Explanation: This report requires joining to get customer names, grouping to total spend per customer, ordering by that total descending, and limiting to the top 10 results.

4. Adding WITH ROLLUP to the monthly sales report's GROUP BY clause will:

1. Remove all monthly detail rows
2. Add a final grand total row summarizing the entire period
3. Cause an error since ROLLUP requires two grouping columns
4. Automatically sort the result by revenue

Answer: B. Add a final grand total row summarizing the entire period

Explanation: ROLLUP appends a summary row (or rows) to the existing grouped result; with a single grouping column, this takes the form of one grand total row at the end.

Quick Revision Points

• DATE_FORMAT(date_column, '%Y-%m') is the standard technique for grouping by calendar month across multiple years.
• Top customer reports should JOIN for names, GROUP BY unique ID, use HAVING for thresholds, and ORDER BY plus LIMIT for ranking.
• WITH ROLLUP integrates cleanly with a single-column monthly GROUP BY to add a grand total row.
• This use case demonstrates how JOIN, GROUP BY, HAVING, and ROLLUP combine into complete, real-world reporting solutions.

Conclusion

• Real business reporting requests typically require combining several SQL concepts together, not applying them in isolation.
• Date formatting technique (year+month together) is essential for accurate multi-year time-based reporting.
• The JOIN-GROUP BY-HAVING-ORDER BY-LIMIT combination is the standard pattern for any 'top N by metric' report.
• ROLLUP integrates naturally into time-based reports to provide an at-a-glance grand total alongside the monthly detail.

This capstone use case combines nearly every concept from this module into two realistic, end-to-end business deliverables: a monthly sales report using DATE_FORMAT for accurate year-and-month grouping with a ROLLUP-generated grand total, and a top customers report combining JOIN, GROUP BY, HAVING, ORDER BY, and LIMIT to rank customers by total spend. Together, these two queries demonstrate exactly how GROUP BY and aggregation concepts are woven together in real production reporting, rather than used in isolation as in earlier, more focused lessons.