Created
September 17, 2020 20:38
-
-
Save gregtap/2f6dac81c47f18964a56057c387cc851 to your computer and use it in GitHub Desktop.
SQL aggregated rolling sum
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
pinaraf
commented
Sep 17, 2020
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment