Scenario:
In business intelligence and analytics projects, understanding sales growth trends is critical for measuring product performance. Three of the most commonly used growth metrics are:
- Month-over-Month (MoM) Sales Growth
- Quarter-over-Quarter (QoQ) Sales Growth
- Year-over-Year (YoY) Sales Growth
In this article, we'll use the sample dataset below to calculate MoM, QoQ, and YoY sales growth using SQL.
/* Create Source Table */
USE [db_MyAnalytics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Check and Drop table if already Exists */
IF OBJECT_ID('dbo.fact_ProductSales', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.fact_ProductSales;
END
GO
CREATE TABLE dbo.fact_ProductSales
(
Sales_Date DATE NULL,
Product_Name VARCHAR(50) NULL,
Sales_Amount DECIMAL(10,2) NULL
) ON [PRIMARY];
GO
/* Check and Truncate table if already Exists */
IF OBJECT_ID('dbo.fact_ProductSales', 'U') IS NOT NULL
BEGIN
TRUNCATE TABLE dbo.fact_ProductSales;
END
GO
/* Insert Data to Table */
INSERT INTO [dbo].[fact_ProductSales]
(
[Sales_Date],
[Product_Name],
[Sales_Amount]
)
VALUES
('2023-01-01','Radio',10000),
('2023-02-01','Radio',12000),
('2023-03-01','Radio',13000),
('2023-04-01','Radio',15000),
('2023-05-01','Radio',17000),
('2023-06-01','Radio',18000),
('2024-01-01','Radio',14000),
('2024-02-01','Radio',15500),
('2024-03-01','Radio',16000),
('2024-04-01','Radio',17500),
('2024-05-01','Radio',19000),
('2024-06-01','Radio',21000),
('2023-01-01','Television',50000),
('2023-02-01','Television',52000),
('2023-03-01','Television',55000),
('2023-04-01','Television',57000),
('2023-05-01','Television',60000),
('2023-06-01','Television',62000),
('2024-01-01','Television',65000),
('2024-02-01','Television',67000),
('2024-03-01','Television',70000),
('2024-04-01','Television',72000),
('2024-05-01','Television',76000),
('2024-06-01','Television',80000);
SELECT * FROM [dbo].[fact_ProductSales]
GO
#Result:
------------------------------------------------------------
/*
Understanding Growth Formula:
Growth % = (Current Period Sales - Previous Period Sales)/Previous Period Sales * 100
*/
1. Month-over-Month (MoM) Sales Growth:
Month-over-Month (MoM) Growth compares the current month's sales with previous month's sales. It helps businesses to measure short-term performance changes and identify emerging trends.MoM analysis is useful for monitoring the immediate impact of marketing campaigns, product launches, seasonal promotions, pricing changes, or other business initiatives.
Since the comparison period is only one month, it provides a near real-time view of business performance.
Example:
January Sales = $10,000
February Sales = $12,000
Example:
January Sales = $10,000
February Sales = $12,000
MoM Growth = 20%
This indicates that sales increased by 20% compared to the previous month.
WITH CTE_Monthly_Sales AS
(
SELECT
Product_Name,
Sales_Date,
Sales_Amount,
LAG(Sales_Amount) OVER
(
PARTITION BY Product_Name
ORDER BY Sales_Date
) AS PrevMonth_Sales
FROM [dbo].[fact_ProductSales]
)
SELECT
Product_Name,
Sales_Date,
Sales_Amount,
PrevMonth_Sales,
(Sales_Amount - PrevMonth_Sales) AS Sales_Variance,
ROUND(
((Sales_Amount - PrevMonth_Sales) * 100.0)
/ NULLIF(PrevMonth_Sales,0),
2
) AS MoM_Growth_Percent
FROM CTE_Monthly_Sales
ORDER BY Product_Name, Sales_Date;
#Result:
------------------------------------------------------------
Quarter-over-Quarter (QoQ) Growth compares sales from the current quarter with previous quarter. Since a quarter typically consists of three months, QoQ analysis helps smooth out monthly fluctuations and provides a broader view of business performance.
Organizations often use QoQ metrics to evaluate strategic initiatives, assess departmental performance, and measure progress against quarterly business objectives. It is especially useful for executive reporting and financial analysis.
Example:
Q1 Sales = $150,000
Q2 Sales = $180,000
QoQ Growth = 20%
This indicates that sales increased by 20% compared to the previous quarter.
WITH CTE_Quarterly_Sales AS
(
SELECT
Product_Name,
YEAR(Sales_Date) AS Sales_Year,
DATEPART(QUARTER, Sales_Date) AS Qtr_No,
SUM(Sales_Amount) AS Qtr_Sales,
LAG(SUM(Sales_Amount)) OVER
(
PARTITION BY Product_Name
ORDER BY YEAR(Sales_Date), DATEPART(QUARTER, Sales_Date)
) AS PrevQtr_Sales
FROM dbo.fact_ProductSales
GROUP BY
Product_Name,
YEAR(Sales_Date),
DATEPART(QUARTER, Sales_Date)
)
SELECT
Product_Name,
Sales_Year,
Qtr_No,
Qtr_Sales,
PrevQtr_Sales,
(Qtr_Sales - PrevQtr_Sales) AS Sales_Variance,
ROUND
(
((Qtr_Sales - PrevQtr_Sales) * 100.0)
/ NULLIF(PrevQtr_Sales, 0),
2
) AS QoQ_Growth_Percent
FROM CTE_Quarterly_Sales
ORDER BY Product_Name, Sales_Year, Qtr_No;
#Result:
------------------------------------------------------------
Year-over-Year (YoY) Growth compares sales for a specific period against the same period in the previous year. This comparison helps businesses evaluate long-term growth trends and overall business performance.
YoY is considered as one of the most important Key Performance Indicators (KPIs) because it minimizes the impact of seasonality.
For example, comparing Dec sales with Nov sales may not provide an accurate comparison due to seasonal factors such as holiday shopping. However, comparing Dec-2024 with Dec-2023 helps eliminate seasonality and provides a more accurate measure of true business growth.
YoY analysis is widely used by business leaders, analysts, investors, and finance teams to assess whether a company is growing, maintaining, or losing market momentum.
Example:
Q1 2023 Sales = $500,000
Q1 2024 Sales = $625,000
YoY Growth = 25%
This indicates that sales grew by 25% compared to the same quarter in the previous year.
Why YoY Growth is Important:
#Result:
YoY analysis is widely used by business leaders, analysts, investors, and finance teams to assess whether a company is growing, maintaining, or losing market momentum.
Example:
Q1 2023 Sales = $500,000
Q1 2024 Sales = $625,000
YoY Growth = 25%
This indicates that sales grew by 25% compared to the same quarter in the previous year.
Why YoY Growth is Important:
- Eliminates seasonal variations in sales.
- Measures long-term business performance.
- Provides a more accurate view of growth trends.
- Commonly used in financial reporting and executive dashboards.
- Helps stakeholders assess the overall health of the business.
WITH CTE_Yearly_Sales AS
(
SELECT
Product_Name,
YEAR(Sales_Date) AS Sales_Year,
SUM(Sales_Amount) AS Yearly_Sales,
LAG(SUM(Sales_Amount)) OVER
(
PARTITION BY Product_Name
ORDER BY YEAR(Sales_Date)
) AS PrevYear_Sales
FROM dbo.fact_ProductSales
GROUP BY Product_Name, YEAR(Sales_Date)
)
SELECT
Product_Name,
Sales_Year,
Yearly_Sales,
PrevYear_Sales,
(Yearly_Sales - PrevYear_Sales) AS Sales_Variance,
ROUND
(
((Yearly_Sales - PrevYear_Sales) * 100.0)
/ NULLIF(PrevYear_Sales, 0),
2
) AS YoY_Growth_Percent
FROM CTE_Yearly_Sales
ORDER BY Product_Name, Sales_Year;
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.