Google BigQuery: Rolling Count Distinct

Question

I have a table with is simply a list of dates and user IDs (not aggregated).

We define a metric called active users for a given date by counting the distinct number of IDs that appear in the previous 45 days.

I am trying to run a query in BigQuery that, for each day, returns the day plus the number of active users for that day (count distinct user from 45 days ago until today).

I have experimented with window functions, but can't figure out how to define a range based on the date values in a column. Instead, I believe the following query would work in a database like MySQL, but does not in BigQuery.

SELECT 
  day,
  (SELECT 
    COUNT(DISTINCT visid) 
   FROM daily_users
   WHERE day BETWEEN DATE_ADD(t.day, -45, "DAY") AND t.day
   ) AS active_users
FROM daily_users AS t
GROUP BY 1

This doesn't work in BigQuery: "Subselect not allowed in SELECT clause."

How to do this in BigQuery?

Answer 1

Below should work with BigQuery

#legacySQL
SELECT day, active_users FROM (
  SELECT 
    day, 
    COUNT(DISTINCT id) 
      OVER (ORDER BY ts RANGE BETWEEN 45*24*3600 PRECEDING AND CURRENT ROW) AS active_users
  FROM (
    SELECT day, id, TIMESTAMP_TO_SEC(TIMESTAMP(day)) AS ts 
    FROM daily_users
  )
) GROUP BY 1, 2 ORDER BY 1  

Above assumes that day field is represented as '2016-01-10' format.
If it is not a case , you should adjust TIMESTAMP_TO_SEC(TIMESTAMP(day)) in most inner select

Also please take a look at COUNT(DISTINC) specifics in BigQuery

Update for BigQuery Standard SQL

#standardSQL
SELECT 
  day, 
  (SELECT COUNT(DISTINCT id) FROM UNNEST(active_users) id) AS active_users
FROM (
  SELECT 
    day, 
    ARRAY_AGG(id) 
      OVER (ORDER BY ts RANGE BETWEEN 3888000 PRECEDING AND CURRENT ROW) AS active_users
  FROM (
    SELECT day, id,  UNIX_DATE(PARSE_DATE('%Y-%m-%d', day)) * 24 * 3600 AS ts 
    FROM daily_users
  )
) 
GROUP BY 1, 2 
ORDER BY 1  

You can test / play with it using below dummy sample

#standardSQL
WITH daily_users AS (
  SELECT 1 AS id, '2016-01-10' AS day UNION ALL
  SELECT 2 AS id, '2016-01-10' AS day UNION ALL
  SELECT 1 AS id, '2016-01-11' AS day UNION ALL
  SELECT 3 AS id, '2016-01-11' AS day UNION ALL
  SELECT 1 AS id, '2016-01-12' AS day UNION ALL
  SELECT 1 AS id, '2016-01-12' AS day UNION ALL
  SELECT 1 AS id, '2016-01-12' AS day UNION ALL
  SELECT 1 AS id, '2016-01-13' AS day
)
SELECT 
  day, 
  (SELECT COUNT(DISTINCT id) FROM UNNEST(active_users) id) AS active_users
FROM (
  SELECT 
    day, 
    ARRAY_AGG(id) 
      OVER (ORDER BY ts RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW) AS active_users
  FROM (
    SELECT day, id,  UNIX_DATE(PARSE_DATE('%Y-%m-%d', day)) * 24 * 3600 AS ts 
    FROM daily_users
  )
) 
GROUP BY 1, 2 
ORDER BY 1