SQL query for DAU/WAU/MAU using Presto SQL in AWS Athena

Question

I'm trying to create a query in AWS Athena to get the DAU, WAU and MAU (Daily, Weekly, and Monthly Active Users) values.

I was able to create the dau query

select
    date(from_unixtime(time / 1000)) as date,
    count(distinct id) as dau
from events 
where name = 'Started'
and from_unixtime(time / 1000) > date_parse('2020-12-01', '%Y-%m-%d')
group by date(from_unixtime(time / 1000))

However, when I try to use this query as in this example (https://discuss.redash.io/t/dau-wau-mau-query-example/1704) using a with statement to generate the MAU, it's not working

with dau as (
select
    date(from_unixtime(time / 1000)) as date,
    count(distinct id) as dau
from events 
where name = 'Started'
and from_unixtime(time / 1000) > date_parse('2020-12-01', '%Y-%m-%d')
group by date(from_unixtime(time / 1000))
) 
select 
    date, 
    dau,
        (select 
            count(distinct id)
        from events 
        where name = 'Started'
        and from_unixtime(time / 1000) between dau.date - interval '30' day and dau.date
) as mau
from dau

But I get an error 100071 saying that the given correlated subquery is not supported.

The problem is trying to use the date from the DAU query, if I comment the line

and from_unixtime(time / 1000) between dau.date - interval '30' day and dau.date

it will give the same MAU value for every day, but what I want is to know the value from that day minus 30 days

date         dau   mau
2020-12-01   10    234
2020-12-02   15    234
2020-12-03   20    234
...

Is there any other way to use the DAU date in that subquery or another way to get the MAU value?

Answer 1

等待大神答复