How to Calculate sales growth?

Question

I want to get total monthly sales growth from three (3) tables. Can someone show me how to do it?

Table1

Month	Amount
Sep 2023	2.00
Oct 2023	3.00
Oct 2023	1.00

Table2

Month	Amount
Sep 2023	1.00
Oct 2023	2.00
Oct 2023	2.00

Table3

Month	Amount
Sep 2023	5.00
Oct 2023	2.00
Oct 2023	1.00

This is what l have tried

WITH CTE AS (
SELECT OrderDate, Amount FROM Table1 
    UNION ALL 
SELECT OrderDate, Amount FROM Table2 
    UNION ALL SELECT OrderDate, Amount FROM Table3)
SELECT 
    Datepart(Month, OrderDate) as 
    monthly_sales, Sum(Amount) As monthly_sales FROM  CTE Group by Datepart(Month, OrderDate),
CTE2 AS (SELECT *,  lag(monthly_sales) over (order by Month ) as prev_month_sales FROM CTE ), 
CTE3 AS (SELECT     *,  100*(monthly_sales - prev_month_sales)/(prev_month_sales) as sales_growth FROM CTE2)
SELECT * from CTE3

Expected output.

**Sales growth **

Month	monthly_sales	prev_month_sales	sales_growth
Sep 2023	8.00	0	0
Oct 2023	7.00	8.0	12.52
Oct 2023	4.00	7.0	-42.86

Answer 1

We can contain all data in 1 CTE and then join with lagged data. Here is my solution:

-- Create the tables
CREATE TABLE Table1 (
    [Month] date,
    Amount decimal(18, 2)
);

CREATE TABLE Table2 (
    [Month] date,
    Amount decimal(18, 2)
);

CREATE TABLE Table3 (
    [Month] date,
    Amount decimal(18, 2)
);

-- Insert data into the tables
INSERT INTO Table1 ([Month], Amount)
VALUES
    ('2023-09-01', 2.00),
    ('2023-10-01', 3.00),
    ('2023-10-01', 1.00);

INSERT INTO Table2 ([Month], Amount)
VALUES
    ('2023-09-01', 1.00),
    ('2023-10-01', 2.00),
    ('2023-10-01', 2.00);

INSERT INTO Table3 ([Month], Amount)
VALUES
    ('2023-09-01', 5.00),
    ('2023-10-01', 2.00),
    ('2023-10-01', 1.00);

-- Calculate sales growth
WITH CombinedData AS (
    SELECT [Month], SUM(Amount) AS monthly_sales
    FROM (
        SELECT [Month], Amount FROM Table1
        UNION ALL
        SELECT [Month], Amount FROM Table2
        UNION ALL
        SELECT [Month], Amount FROM Table3
    ) AS Combined
    GROUP BY [Month]
),
SalesGrowth AS (
    SELECT
        FORMAT([Month], 'MMM yyyy') [Month],
        monthly_sales,
        LAG(monthly_sales, 1, 0) OVER (ORDER BY  [Month]) AS prev_month_sales,
        CASE
            WHEN LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month]) = 0 THEN 0
            ELSE (monthly_sales - LAG(monthly_sales, 1, 0) OVER (ORDER BY  [Month])) * 100.0 / LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month])
        END AS sales_growth
    FROM CombinedData
)
SELECT [Month], monthly_sales, prev_month_sales, sales_growth
FROM SalesGrowth;

DROP TABLE IF EXISTS Table1
DROP TABLE IF EXISTS Table2
DROP TABLE IF EXISTS Table3

Here is the sample output:

Here is sample fiddle link

NB: I create sales_growth on percentage (multiplied by 100). You can change to make it suitable to your requirement