MySQL Query to find Cumulative Distribution of Sales

Find the Cumulative Distribution of Sales

The cumulative distribution of sales is the running total of sales values divided by the overall total sales, representing the proportion of total sales accumulated up to a specific point. It shows how sales are distributed across intervals (e.g., months or categories) and highlights trends like concentration or growth over time. For example, if total sales are 100 and monthly sales are 10, 20, and 30, the cumulative distribution would be 10%, 30%, and 60%.

Write a MySQL query to find the cumulative distribution of sales using a window function.

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the MonthlySales table
SELECT 
    -- Retrieve the Month column to display the corresponding month for each sales record
    Month, 
    -- Retrieve the Sales column to show the sales amount for each month
    Sales,
    -- Calculate the cumulative distribution of Sales:
    -- CUME_DIST() computes the relative rank of each row in the dataset,
    -- It returns a value between 0 and 1, representing the percentage of rows 
    -- with a Sales value less than or equal to the current row’s Sales.
    -- ORDER BY Sales ensures that the calculation is performed based on ascending sales values.
    CUME_DIST() OVER (ORDER BY Sales) AS CumulativeDistribution
-- Specify the MonthlySales table as the source of the data
FROM MonthlySales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the cumulative distribution of sales, which represents the relative position of each month's sales within the entire dataset.
• This demonstrates the use of the CUME_DIST() window function.
• Key Components:
• CUME_DIST() OVER (ORDER BY Sales): Calculates the cumulative distribution of sales.
• SELECT Month, Sales: Retrieves the month and sales data along with the cumulative distribution.
• Why use Window Functions?:
• Window functions allow you to perform statistical calculations, such as cumulative distribution, without the need for complex subqueries.
• Real-World Application:
• For example, in a retail business, you might want to analyze the distribution of sales to identify periods of high or low performance.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the cumulative sum of sales for each month.
• Write a MySQL query to find the percentile rank of sales using a window function.
• Write a MySQL query to compute the difference in sales between the current and previous months.
• Write a MySQL query to calculate the moving average of sales over a 3-month period.

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

Previous MySQL Exercise: Calculate the Difference between Each Employee's Salary and the Company Average.
Next MySQL Exercise: Calculate the Percentile Rank of Each Employee's Salary.