Mau/dau/engagement query

gistfile1.txt

WITH dates_list AS (
  Select * 
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE())) AS dates
), 
usage_dates AS (
  SELECT
    EXTRACT(date from timestamp) AS date,
    jsonPayload.fields.fxa_uid AS users
FROM
  `moz-fx-guardian-prod-bfc7.log_storage.stdout`
WHERE
  resource.labels.container_name = 'guardian'
  AND jsonPayload.type = 'browser.oauth.proxy-token-created'
  AND jsonPayload.fields.unlimited = @unlimited
GROUP BY
  1,2
ORDER BY
  2 Desc
),
dau_calcs AS (
  SELECT
   dates_list.dates as dau_date,
   COUNT(distinct users) as dau,
  FROM dates_list
  INNER JOIN usage_dates
  ON (usage_dates.date = dates_list.dates)
  GROUP BY 1
),
mau_calcs AS (
  SELECT
    dates_list.dates as mau_date,
    COUNT(distinct users) as mau,
  FROM dates_list
  INNER JOIN usage_dates
  ON (usage_dates.date <= dates_list.dates) and (usage_dates.date >= date_sub(dates_list.dates, interval 28 day))
  GROUP by 1
)

SELECT 
  mau_date as date,
  mau,
  dau,
  ROUND(dau_calcs.dau / mau_calcs.mau, 3) as engagement_ratio
from mau_calcs
INNER JOIN dau_calcs
ON (dau_calcs.dau_date = mau_calcs.mau_date)
ORDER BY 1 ASC