MySQL Query of Cumulative Sum (Case: when there is no order on such period, the value still on previous period)

Question

I have order table with order date of course and its amount.

I want to sum the amount of previous period with current period and resulting table like this

the query I have tried was

    with data as(
select  
        lead_id,
        no_pks,
        customer_name,
        point_name,
        funder_id,
        DATE_FORMAT(order_date,'%Y-%m') as year_and_month_order,
        sum(total_amount) as outstanding
    from 
        x
    group by
        lead_id,
        no_pks,
        customer_name,
        point_name,
        funder_id,
        year_and_month_order
)

select 
    *,
    sum(outstanding) over(partition by lead_id,no_pks order by year_and_month_order) as cumulative_outstanding
from
    data
order by lead_id,no_pks

My goals is when on such month there were no order, the amount are 0 while the cumulative amount must be followed the previous month. The result of what I need are

Answer 1

You must add one more recursive CTE and generate base dates list (calendar) using min. and max. dates from your data table as generated range borders (or set these borders as parameters) then LEFT JOIN your table to it. In window function you'd use the dates from this base table which will contain all needed dates.

Simple DEMO:

CREATE TABLE test (the_date DATE, the_value INT);
INSERT INTO test 
SELECT '2022-03-04', 1 UNION ALL
SELECT '2022-03-05', 2 UNION ALL
SELECT '2022-03-07', 3 ;

SELECT *, SUM(the_value) OVER (ORDER BY the_date) cumulative
FROM test;

the_date	the_value	cumulative
2022-03-04	1	1
2022-03-05	2	3
2022-03-07	3	6

WITH RECURSIVE
cte AS (
    SELECT MIN(the_date) the_date
    FROM test
    UNION ALL
    SELECT the_date + INTERVAL 1 DAY
    FROM cte
    WHERE the_date < ( SELECT MAX(the_date)
                       FROM test )
)
SELECT *, SUM(test.the_value) OVER (ORDER BY the_date) cumulative
FROM cte
LEFT JOIN test USING (the_date);

the_date	the_value	cumulative
2022-03-04	1	1
2022-03-05	2	3
2022-03-06	null	3
2022-03-07	3	6

db<>fiddle here

---

The variant for different id values

CREATE TABLE test (id INT, the_date DATE, the_value INT);
INSERT INTO test 
SELECT 1, '2022-03-04', 1 UNION ALL
SELECT 1, '2022-03-05', 2 UNION ALL
SELECT 1, '2022-03-07', 3 UNION ALL
SELECT 2, '2022-03-04', 4 UNION ALL
SELECT 2, '2022-03-06', 5 UNION ALL
SELECT 2, '2022-03-08', 6 ;

SELECT *, SUM(the_value) OVER (PARTITION BY id ORDER BY the_date) cumulative
FROM test;

id | the_date   | the_value | cumulative
-: | :--------- | --------: | ---------:
 1 | 2022-03-04 |         1 |          1
 1 | 2022-03-05 |         2 |          3
 1 | 2022-03-07 |         3 |          6
 2 | 2022-03-04 |         4 |          4
 2 | 2022-03-06 |         5 |          9
 2 | 2022-03-08 |         6 |         15

WITH RECURSIVE
cte1 AS (
    SELECT MIN(the_date) the_date
    FROM test
    UNION ALL
    SELECT the_date + INTERVAL 1 DAY
    FROM cte1
    WHERE the_date < ( SELECT MAX(the_date)
                       FROM test )
),
cte2 AS ( 
    SELECT DISTINCT id
    FROM test
)
SELECT *, SUM(test.the_value) OVER (PARTITION BY id ORDER BY the_date) cumulative
FROM cte1
CROSS JOIN cte2
LEFT JOIN test USING (id, the_date);

the_date   | id | the_value | cumulative
:--------- | -: | --------: | ---------:
2022-03-04 |  1 |         1 |          1
2022-03-05 |  1 |         2 |          3
2022-03-06 |  1 |      null |          3
2022-03-07 |  1 |         3 |          6
2022-03-08 |  1 |      null |          6
2022-03-04 |  2 |         4 |          4
2022-03-05 |  2 |      null |          4
2022-03-06 |  2 |         5 |          9
2022-03-07 |  2 |      null |          9
2022-03-08 |  2 |         6 |         15

db<>fiddle here