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.