Question 21050 in Mysql Question posted in
The official MySQL documentation can be found here.

Calculate active users by day, week and month – Mysql

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

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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;
    casted_date dau wau mau
    2020-01-16 3 3 3
    2020-02-06 3 3 3
    2020-02-07 3 6 6

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search