CODEWITHSUNDEEP
sqldatetimecountprestoamazon-athena
OctavianWR
OctavianWR

Reputation: 227

rolling count on Athena or Quicksight

I have this dataset

date         id
1/1/2020     1
1/1/2020     2
...
n            m

I want to have rolling count of distinct monthly user on AWS Quicksight or Athena. for example

date         MAU
1/1/2020     -
1/2/2020     -
1/30/2020    100
1/31/2020    102

100 on 1/30/2020 means that in the past 30 days, there is 100 distinct user that active (from 1/1/2020 to 1/30/2020). 102 on 1/31/2020 means that in the past 30 days there is 102 distinct user that active (from 1/2/2020 to 1/30/2020)

Upvotes: 0

Views: 902

Answers (1)

GMB
GMB

Reputation: 222482

The basic idea is to use a window count with a range frame. Does it work in Amazon Athena if we convert the date to an epoch and use the following range frame?

select date, 
    sum(count(*)) over(
        order by to_unixtime(date)
        range between - 60 * 60 * 24 * 30 preceding and current row
    ) mau
from mytable
group by date

An alternative to the window function solution would be a correlated subquery:

select date, 
    count(*) + (
        select count(*) 
        from mytable t1
        where t1.date >= t.date - interval '30' day and t1.date < t.date
    ) mau
from mytable
group by date

Upvotes: 2

Related Questions