SQL Windowing Aggregation Over Two Consecutive Date

user_13623734 0
Viewed 66

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         |
    +---------+--------+--------+-------------+------------------+
postgresql sql
2 Answers

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)
user_64643080

Try this workaround for your problem:

select 
t1.user_id, 
t1.amount,
date(t1.date_),
(select sum(amount)  from tab where user_id=t1.user_id and date_=t1.date_ ),
(select sum(amount)  from tab where user_id=t1.user_id and date_ between t1.date_-1 and t1.date_ )  
from tab t1

with Window function for first sum

select 
t1.user_id, 
t1.amount,
date(t1.date_),
sum(t1.amount) over (partition by t1.user_id,t1.date_),
(select sum(amount)  from tab where user_id=t1.user_id and date_ between t1.date_-1 and t1.date_ )
from tab t1

see DEMO

user_77724830
Related