Postgres: Count number of users actions within time interval

user_9993288 0
Viewed 63

I am trying to count kind of DAU by specified actions, where users are divided into categories by number of their actions within some interval.

Raw data example: 

date        user_id   amount_actions
2018-12-01  1         2      
2018-12-02  1         1
2018-12-10  1         1
2018-12-01  2         2      
2018-12-02  2         1
2018-12-10  3         1

Result table I wish I could have: 

date        user_id   amount_actions rolling_sum_7_days
2018-12-01  1         2              2
2018-12-02  1         1              3
2018-12-10  1         1              1
2018-12-01  2         2              2
2018-12-12  2         1              1
2018-12-10  3         1              1
2018-12-15  3         1              2

Thanks.

postgresql
2 Answers

You can do a lateral join that calculates the sum of actions for that user in the past seven days:

select  date
,       user_id
,       amount_actions
,       sum_actions
from    YourTable yt1
cross join lateral
        (
        select  sum(amount_actions) as sum_actions
        from    YourTable yt2
        where   yt1.user_id = yt2.user_id
                and yt1.date - interval '7 days' < yt2.date
                and yt2.date <= yt1.date
        ) sum_actions

Working example at rextester.

user_505520

Using a cumulative sum on Postgres:

select
    dt, user_id, amount_actions,
    to_char(dt, 'WWYYYY') wk,
    sum(amount_actions) 
        over 
        (partition by user_id, to_char(dt, 'WWYYYY')
         order by user_id, dt) rolling_sum_7_days
from
    tbl
order by user_id, dt;

The partition is: user_id + WeekYear to_char(dt, 'WWYYYY')

dt         | user_id | amount_actions | wk     | rolling_sum_7_days
:--------- | ------: | -------------: | :----- | -----------------:
2018-12-01 |       1 |              2 | 482018 |                  2
2018-12-02 |       1 |              1 | 482018 |                  3
2018-12-10 |       1 |              1 | 502018 |                  1
2018-12-01 |       2 |              2 | 482018 |                  2
2018-12-02 |       2 |              1 | 482018 |                  3
2018-12-10 |       3 |              1 | 502018 |                  1

db<>fiddle here

user_32704270
Related