I am not an SQL expert and finding this a bit challenging. Imagine I have the following table but with more users:
x
+---------+--------+--------+-------------+
| user_id | amount | date | sum_per_day |
+---------+--------+--------+-------------+
| user8 | 300 | 7/2/20 | 300 |
| user8 | 150 | 6/2/20 | 400 |
| user8 | 250 | 6/2/20 | 400 |
| user8 | 25 | 5/2/20 | 100 |
| user8 | 25 | 5/2/20 | 100 |
| user8 | 25 | 5/2/20 | 100 |
| user8 | 25 | 5/2/20 | 100 |
| user8 | 50 | 2/2/20 | 50 |
+---------+--------+--------+-------------+
As you see they are grouped by user_id
. Now what I like to do is add a column called sum_over_two_day
which satisfies the following conditions:
- Grouped by
user_id
- For each user it is grouped by the
date
- The sum is then calculated per two consecutive calendar days for
amount
(today + previous calendar day)
So the output will be this:
+---------+--------+--------+-------------+------------------+
| user_id | amount | date | sum_per_day | sum_over_two_day |
+---------+--------+--------+-------------+------------------+
| user8 | 300 | 7/2/20 | 300 | 700 |
| user8 | 150 | 6/2/20 | 400 | 500 |
| user8 | 250 | 6/2/20 | 400 | 500 |
| user8 | 25 | 5/2/20 | 100 | 100 |
| user8 | 25 | 5/2/20 | 100 | 100 |
| user8 | 25 | 5/2/20 | 100 | 100 |
| user8 | 25 | 5/2/20 | 100 | 100 |
| user8 | 50 | 2/2/20 | 50 | 50 |
+---------+--------+--------+-------------+------------------+
Advertisement
Answer
The proper way is to use a window function with a RANGE
clause:
SELECT user_id,
amount,
date,
sum(amount) OVER (PARTITION BY user_id
ORDER BY date
RANGE BETWEEN INTERVAL '1 day' PRECEDING
AND CURRENT ROW)
AS sum_over_two_day
FROM atable
ORDER BY user_id, date;
user_id | amount | date | sum_over_two_day
---------+--------+------------+------------------
user8 | 50 | 2020-02-02 | 50
user8 | 25 | 2020-02-05 | 100
user8 | 25 | 2020-02-05 | 100
user8 | 25 | 2020-02-05 | 100
user8 | 25 | 2020-02-05 | 100
user8 | 250 | 2020-02-06 | 500
user8 | 150 | 2020-02-06 | 500
user8 | 300 | 2020-02-07 | 700
(8 rows)