SQL aggregated rolling sum

test.sql

WITH example AS (
 SELECT '2020-04-15' AS start_date,  6000.00 AS amount, "e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" AS fun_id
 UNION ALL SELECT '2020-04-21',  7000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7"
 UNION ALL SELECT '2020-05-04', 13000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7"
 UNION ALL SELECT '2020-05-28',  4000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7"
 UNION ALL SELECT '2020-08-05', 30000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7"
 UNION ALL SELECT '2020-05-04',  1962.00 ,"89ba98b9-a4fa-41f3-9015-4e3cc29961ad"
 UNION ALL SELECT '2020-07-16',  4038.00 ,"89ba98b9-a4fa-41f3-9015-4e3cc29961ad"
 UNION ALL SELECT '2020-07-20', 10000.00 ,"ce257ea2-1a0c-42ff-9d1c-ea2073846628"
 UNION ALL SELECT '2020-08-31', 25000.00 ,"9f38de3d-4cd7-4950-9d6d-5ddf68c67c20"
 UNION ALL SELECT '2020-03-09', 45000.00 ,"8f4298ff-356c-4be9-8dd6-b12e1078f668"
 UNION ALL SELECT '2020-09-07', 10000.00 ,"a505f602-eb2b-4d1e-90b4-39dfec282843"
 UNION ALL SELECT '2020-09-07', 10000.00 ,"fa49d1c7-517a-4b7e-b0c8-1c616e858f9e" )

SELECT fun_id, start_date, amount, SUM(amount) OVER (PARTITION BY fun_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_sum from example ORDER BY fun_id, start_date

Table foo

d <date>
a <amount>
c <client_id>

Retourne la liste, par semaine de la somme des amount avant la date d, pour tous les clients.

Given a start_date (Monday).

Client1 Week1 100
Client1 Week2 200
Client1 Week3 300
Client1 Week4 300
Client1 Week5 300
Client1 Week6 400
Client2 Week1 300

WITH example AS (
 SELECT '2020-04-15'::date AS start_date,  6000.00 AS amount, 'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7' AS fun_id
 UNION ALL SELECT '2020-04-21',  7000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
 UNION ALL SELECT '2020-05-04', 13000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
 UNION ALL SELECT '2020-05-28',  4000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
 UNION ALL SELECT '2020-08-05', 30000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
 UNION ALL SELECT '2020-05-04',  1962.00 ,'89ba98b9-a4fa-41f3-9015-4e3cc29961ad'
 UNION ALL SELECT '2020-07-16',  4038.00 ,'89ba98b9-a4fa-41f3-9015-4e3cc29961ad'
 UNION ALL SELECT '2020-07-20', 10000.00 ,'ce257ea2-1a0c-42ff-9d1c-ea2073846628'
 UNION ALL SELECT '2020-08-31', 25000.00 ,'9f38de3d-4cd7-4950-9d6d-5ddf68c67c20'
 UNION ALL SELECT '2020-03-09', 45000.00 ,'8f4298ff-356c-4be9-8dd6-b12e1078f668'
 UNION ALL SELECT '2020-09-07', 10000.00 ,'a505f602-eb2b-4d1e-90b4-39dfec282843'
 UNION ALL SELECT '2020-09-07', 10000.00 ,'fa49d1c7-517a-4b7e-b0c8-1c616e858f9e'
 UNION ALL SELECT '2020-09-08', 42, 'fa49d1c7-517a-4b7e-b0c8-1c616e858f9e' )
SELECT fun_id, extract(week from start_date), array_agg(start_date), sum(amount) from example group by 1, 2 order by 1, 2;