I have a temp_table with user_id and date and I want to find the DAU , WAU and MAU and I am querying this for that where:
DAU - Count of active users for that day
WAU - Count of active users in last 7 days
MAU - Count of active users in last 30 days
where the date is starting from a date that is mentioned here , so there can be no current_date comparison.

dau as (Select casted_date, count(user_id) as dau 
from temp table 
group by casted_date)
select casted date, dau,
sum(dau) over (order by casted_date rows between -6 preceding and current row) as wau,
sum(dau) over (order by casted_date rows between -29 preceding and current row) as mau
from dau;

but the query is giving me an error like this :
syntax error at or near "-".

PS: I am writing the query in mysql

CodePudding user response：

I don't know if your query logic be completely correct, but the syntax error you are currently seeing is due to the window function calls. Consider this corrected version:

sum(dau) over (order by casted_date rows between 6 preceding and current row) as wau,
sum(dau) over (order by casted_date rows between 29 preceding and current row) as mau

There is no need to use -6 to refer to the previous 6 rows, as 6 preceding already means this.

CodePudding user response：

First, you have a syntax error, you have casted date where you should have casted_date and I would not use an alias of date either which happens to be a MySQL keyword without escaping it.

You can use case-when to achieve your goal:

select
td.casted_date,
count(distinct td.id) as DAU,
count(distinct tw.id) as WAU,
count(distinct tm.id) as MAU
from temp td
left join temp tw
on tw.casted_date between date_sub(td.casted_date, interval 7 day) and td.casted_date
left join temp tm
on tm.casted_date between date_sub(td.casted_date, interval 30 day) and td.casted_date
group by td.casted_date;

Tested with this schema:

create table temp(
    id int primary key auto_increment,
    casted_date date
);

insert into temp(casted_date)
values
('2020-02-07'),
('2020-02-07'),
('2020-02-07'),
('2020-02-06'),
('2020-02-06'),
('2020-02-06'),
('2020-01-16'),
('2020-01-16'),
('2020-01-16');

Fiddle can be found here: http://sqlfiddle.com/#!9/441aaa/10

CodePudding user response：

Xou can use biuild in date functions to parttion the window functions

create table temp(
    id int primary key auto_increment,
    casted_date date,
  user_id int
);

insert into temp(casted_date,user_id)
values
('2020-02-07',1),
('2020-02-07',2),
('2020-02-07',3),
('2020-02-06',1),
('2020-02-06',2),
('2020-02-06',4),
('2020-01-16',1),
('2020-01-16',2),
('2020-01-16',1);

Records: 9  Duplicates: 0  Warnings: 0

WITH
dau as (Select casted_date, count(user_id) as dau 
from temp
group by casted_date)
select casted_date, dau,
sum(dau) over (PARTITION BY YEAR(casted_date) ,WEEK(casted_date) order by casted_date ) as wau,
sum(dau) over (PARTITION BY YEAR(casted_date),MONTH(casted_date) order by casted_date ) as mau
from dau
  ORDER BY casted_date ASC;

fiddle