HLL queries for all, channel and country

hll.org

Generate Queries using REDASH

1. Compare average WAU counts across months of June,July, August for Firefox desktop. These queries need to be run on https://sql.telemetry.mozilla.org and then downloaded as CSV files.

WITH sample AS
  ( SELECT *
   FROM client_count
   WHERE APP_NAME='Firefox' AND activity_date >= '2016-05-01'
     AND activity_date < '2016-09-01'),
     dau AS
  ( SELECT 
           activity_date,
           merge(cast(hll AS HLL)) AS hll
   FROM sample
   GROUP BY activity_date
            ),
     wau7 AS
  ( SELECT activity_date,
           merge(hll) OVER (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau),
     mau28 AS
  ( SELECT activity_date,
           merge(hll)  OVER (ORDER BY activity_date ROWS BETWEEN 27 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau)
SELECT dau.activity_date,
       cardinality(mau28.hll) AS mau28,
       cardinality(wau7.hll) AS wau7,
       cardinality(dau.hll) AS dau
FROM mau28,
     wau7,
     dau
WHERE mau28.activity_date = dau.activity_date
  AND dau.activity_date = wau7.activity_date

1. Compare average WAU counts across months of June,July, August for Fir15ox, for channels (esp non release)

WITH sample AS
  ( SELECT *
   FROM client_count
   WHERE  APP_NAME='Firefox' AND  activity_date > '2016-05-01'
     AND activity_date < '2016-09-01'),
     dau AS
  ( SELECT normalized_channel,
           activity_date,
           merge(cast(hll AS HLL)) AS hll
   FROM sample
   GROUP BY activity_date,
            normalized_channel
            ),
     wau7 AS
  ( SELECT normalized_channel,
           activity_date,
           merge(hll) OVER (PARTITION BY normalized_channel
                                ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau),
     mau28 AS
  ( SELECT normalized_channel,
           activity_date,
           merge(hll)  OVER (PARTITION BY normalized_channel
                                ORDER BY activity_date ROWS BETWEEN 27 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau)
SELECT dau.normalized_channel,
       dau.activity_date,
       cardinality(mau28.hll) AS mau28,
       cardinality(wau7.hll) AS wau7,
       cardinality(dau.hll) AS dau
FROM mau28,
     wau7,
     dau
WHERE mau28.normalized_channel = dau.normalized_channel
  AND mau28.activity_date = dau.activity_date
  AND dau.normalized_channel = wau7.normalized_channel
  AND dau.activity_date = wau7.activity_date

1. Compare average WAU counts across months of June,July, August for Firefox, for countries

WITH sample AS
  ( SELECT *
   FROM client_count
   WHERE  APP_NAME='Firefox' AND  activity_date > '2016-05-01'
     AND activity_date < '2016-09-01'),
     dau AS
  ( SELECT country,
           activity_date,
           merge(cast(hll AS HLL)) AS hll
   FROM sample
   GROUP BY activity_date,
            country
            ),
     wau7 AS
  ( SELECT country,
           activity_date,
           merge(hll) OVER (PARTITION BY country
                                ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau),
     mau28 AS
  ( SELECT country,
           activity_date,
           merge(hll)  OVER (PARTITION BY country
                                ORDER BY activity_date ROWS BETWEEN 27 PRECEDING AND 0 FOLLOWING) AS hll
   FROM dau)
SELECT dau.country,
       dau.activity_date,
       cardinality(mau28.hll) AS mau28,
       cardinality(wau7.hll) AS wau7,
       cardinality(dau.hll) AS dau
FROM mau28,
     wau7,
     dau
WHERE mau28.country = dau.country
  AND mau28.activity_date = dau.activity_date
  AND dau.country = wau7.country
  AND dau.activity_date = wau7.activity_date

Now read these files into R,