0

I want to use one query to get the distinct count of monthly, weekly and daily users in one query. Currently,I am using three different data sources with the following fields:

**To calculate Monthly Active Users, I am using the following select statement: **

cast(CONCAT(cast(year(date(date))as varchar),'', cast(month(date(date)) as varchar),'','01') as date) AS date,
mobile_app_os,
CASE country WHEN 'DE' THEN 'Germany' ELSE 'International' END AS country,
count(distinct app_user_id) as MAU

**To calculate Weekly Active Users, I am using the following select statement: **

CAST(date_parse(CAST(year_of_week(date(date)) AS varchar) || ':' || CAST(week(date(date)) AS varchar), '%x:%v') AS date) as date,
CASE country WHEN 'DE' THEN 'Germany' ELSE 'International' END AS country,
mobile_app_os,
count(distinct app_user_id) as WAU

**To calculate Daily Active Users, I am using the following select statement: **

CAST(date(date) AS DATE) as date,
mobile_app_os,
CASE country WHEN 'DE' THEN 'Germany' ELSE 'International' END AS country,
count(distinct app_user_id) as DAU

I query the data data using Presto connector in Tableau and then use tableau to join these different data sources to calculate DAU/MAU, WAU/MAU and DAU/WAU ratios.

It would be super convenient to have these data sources in one query because joining more than two data sources in Tableau is not a good idea.

nbk
  • 45,398
  • 8
  • 30
  • 47
pri328
  • 1

1 Answers1

0
SELECT 
    DATE_TRUNC('month', date) as month,
    DATE_TRUNC('week', date) as week,
    date,
    mobile_app_os,
    CASE country WHEN 'DE' THEN 'Germany' ELSE 'International' END AS country,
    COUNT(DISTINCT CASE WHEN DATE_TRUNC('month', date) = month THEN app_user_id END) as MAU,
    COUNT(DISTINCT CASE WHEN DATE_TRUNC('week', date) = week THEN app_user_id END) as WAU,
    COUNT(DISTINCT app_user_id) as DAU
FROM 
    your_table
GROUP BY 
    1, 2, 3, 4, 5
AIViz
  • 82
  • 9