Sum of day window rolling

23 Views Asked by At

I have the following table of data:

transaction_id user_id amount transaction_date
354 423 240.13 01/01/2022 12:00:00
2425 423 167.24 01/03/2022 12:00:00
1352 423 234.14 01/03/2022 12:00:00
2145 423 123.45 01/04/2022 12:00:00
1544 423 82.90 01/06/2022 12:00:00
1223 525 124.20 01/01/2022 12:00:00
1732 525 324.00 01/02/2022 12:00:00
1413 525 100.50 01/03/2022 12:00:00
1141 525 123.00 01/03/2022 12:00:00
1341 525 198.35 01/04/2022 12:00:00
2362 826 146.24 01/02/2022 12:00:00
1234 826 242.00 01/04/2022 12:00:00

I wrote the following query in order to compute the sum of the amount for each 3 days:

SELECT user_id, transaction_date,
      SUM(amount) OVER(PARTITION BY user_id ORDER BY transaction_date RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW ) AS rolling_earnings_3d 
FROM user_transactions;

I got the following output:

user_id transaction_date rolling_earnings_3d
423 01/01/2022 12:00:00 240.13
423 01/03/2022 12:00:00 641.51
423 01/03/2022 12:00:00 641.51
423 01/04/2022 12:00:00 524.83
423 01/06/2022 12:00:00 206.35
525 01/01/2022 12:00:00 124.20
525 01/02/2022 12:00:00 448.20
525 01/03/2022 12:00:00 671.70
525 01/03/2022 12:00:00 671.70
525 01/04/2022 12:00:00 745.85
826 01/02/2022 12:00:00 146.24

The output must be:

user_id transaction_date rolling_earnings_3d
423 01/01/2022 12:00:00 240.13
423 01/03/2022 12:00:00 641.51
423 01/04/2022 12:00:00 524.83
423 01/06/2022 12:00:00 206.35
525 01/01/2022 12:00:00 124.20
525 01/02/2022 12:00:00 448.20
525 01/03/2022 12:00:00 671.70
525 01/04/2022 12:00:00 745.85
826 01/02/2022 12:00:00 146.24
826 01/04/2022 12:00:00 530.24
826 01/07/2022 12:00:00 100.00

It seems that the rows with the same dates are computed twice.

0

There are 0 best solutions below