SQL Windowing Aggregation Over Two Consecutive Date

I am not an SQL expert and finding this a bit challenging. Imagine I have the following table but with more users:

+---------+--------+--------+-------------+
| 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:

1. Grouped by user_id
2. For each user it is grouped by the date
3. 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         |
    +---------+--------+--------+-------------+------------------+

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)