SQL Exercise – 15

SQL Exercise:

A company tracks sales data on a monthly basis and wants to calculate the percentage growth or decline in sales for each product compared to the previous month. This will help the company analyze trends and take corrective measures if sales decline.

Table creation script:

CREATE TABLE MonthlySales (
ProductID INT,
SaleMonth DATE,
SalesAmount DECIMAL(10, 2)
);

Data Insertion script:

INSERT INTO MonthlySales (ProductID, SaleMonth, SalesAmount) VALUES
(1, '2024-01-01', 1000),
(1, '2024-02-01', 1200),
(1, '2024-03-01', 1500),
(2, '2024-01-01', 2000),
(2, '2024-02-01', 1800),
(2, '2024-03-01', 1900);

Solution:

WITH PreviousMonthSalesData AS (
SELECT 
ProductID,
SaleMonth,
SalesAmount,
LAG(SalesAmount) OVER (PARTITION BY ProductID ORDER BY SaleMonth) AS PreviousMonthSalesAmount
FROM 
MonthlySales
)
SELECT 
ProductID,
SaleMonth,
SalesAmount,
PreviousMonthSalesAmount,
CASE 
WHEN PreviousMonthSalesAmount IS NULL THEN NULL
ELSE CAST(((SalesAmount - PreviousMonthSalesAmount) / PreviousMonthSalesAmount) * 100 AS DECIMAL(10, 2))
END AS GrowthPercentage
FROM 
PreviousMonthSalesData;

Output:

 

Expalantion:

• CTE (Common Table Expression):
  • The WITH PreviousMonthSalesDatapart creates a temporary result set.
  • It uses the LAG window function to get the sales amount of the previous month for the same product (PreviousMonthSalesAmount).
• Main Query:
  • Calculates the percentage change in sales between the current and previous months using the formula: GrowthPercentage= (Current month sales – Previous month sales/Previous month sales)*100
  • If there’s no PreviousMonthSales (e.g., for the first month), the result is NULL.
• Window Function:
  • LAG(SalesAmount) OVER (PARTITION BY ProductID ORDER BY SaleMonth) ensures we compare monthly sales for each product in chronological order.
• Rounding:
  • The Decimal function limits the growth percentage to 2 decimal places.