MySQL Query to find the Top 5 Highest Sales per Region

Find the Top 5 Highest Sales by Region

Write a MySQL query to find the top 5 highest sales in each region using a window function.

Solution:

-- Define a Common Table Expression (CTE) named RankedSales to rank sales within each region
WITH RankedSales AS (
    -- Begin the inner SELECT statement to compute ranking of sales per region
    SELECT 
        -- Retrieve the Region column to group sales data by region
        Region, 
        -- Retrieve the Sales column to show the sales amount
        Sales,
        -- Assign a row number to each sales record within its region:
        -- PARTITION BY Region ensures ranking is done separately for each region.
        -- ORDER BY Sales DESC ranks sales in descending order (highest sales get rank 1).
        ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Sales DESC) AS SalesRank
    -- Specify the source table containing regional sales data
    FROM RegionalSales
)
-- Begin the main SELECT statement to retrieve the top sales records from each region
SELECT 
    -- Retrieve the Region column to display sales data by region
    Region, 
    -- Retrieve the Sales column to show the top sales values
    Sales
-- Specify the source as the previously defined CTE 'RankedSales'
FROM RankedSales
-- Filter the results to include only the top 5 sales records for each region
WHERE SalesRank <= 5;

Explanation:

• Purpose of the Query:
• The goal is to identify the top 5 highest sales in each region.
• This demonstrates the use of a Common Table Expression (CTE) and the ROW_NUMBER() window function.
• Key Components:
• ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Sales DESC): Assigns a rank to each sale within the region.
• WITH RankedSales AS (...): Defines a CTE to store the ranked sales.
• WHERE SalesRank <= 5: Filters the results to include only the top 5 sales in each region.
• Why use CTEs and Window Functions?:
• CTEs make complex queries more readable and manageable by breaking them down into simpler parts.
• Window functions allow you to perform cumulative calculations without the need for complex subqueries or joins.
• Real-World Application:
• For example, in a retail business, you might want to identify the top-performing regions for strategic planning.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find the bottom 5 lowest sales in each region.
• Write a MySQL query to rank products based on their total sales within each region.
• Write a MySQL query to calculate the percentage contribution of each region to total sales.
• Write a MySQL query to list the regions with sales exceeding the average regional sales.

Go to:

• Master MySQL Window Functions and CTEs Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Calculate the Cumulative Sum of Sales by Quarter.
NEXT : Calculate the Average Sales Over a Rolling 3-Month Window.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.