How to Calculate sales growth?

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

Table1

Table2

Table3

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 **

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