Cfrgtkky

I'm trying to calculate Day N retention on a dataset in google Big Query. The table consists of one month of data from a mobile app and I want to find out how many users returned each day. I am using standardSQL. So far the code I have is

SELECT date(d1.eventDate) as dt,

        COUNT(distinct d1.userID) as total_users,

        COUNT(distinct d2.userID) as retained_users

         FROM `dataset` as d1

        LEFT JOIN `dataset` as d2 ON 

        d1.userID = d2.userID

        AND date(d1.eventDate) = date(datetime(d2.eventDate, '-1 day'))

          GROUP BY 1

          ORDER BY 1"

When I try to execute I get the error message

  Error: Invalid time zone: -1 day [invalidQuery]

My table structure is

    eventDate           | UserID | 

2016-05-06 00:00:00 UTC | 100000 |

2016-05-06 00:00:00 UTC | 200000 |

2016-05-06 00:00:00 UTC | 300000 |

What should I be using instead of '-1 day'?

TIMESTAMP_SUB would fix the query as written, but might not be good enough as a solution for performance reasons. But at least it gets you the 1 day substraction:

SELECT date(d1.created_at) as dt,

        COUNT(distinct d1.actor.id) as total_users,

        COUNT(distinct d2.actor.id) as retained_users

         FROM `githubarchive.month.201810` as d1

        LEFT JOIN `githubarchive.month.201810` as d2 ON 

        d1.actor.id = d2.actor.id

        AND date(d1.created_at) = date(TIMESTAMP_SUB(d2.created_at, INTERVAL -24 HOUR))

          GROUP BY 1

          ORDER BY 1

To improve performance, do some de-duping before the JOIN:

SELECT day as dt,

    COUNT(distinct d1.id) as total_users,

    COUNT(distinct d2.id) as retained_users

FROM (SELECT DISTINCT actor.id, DATE(created_at) day FROM `githubarchive.month.201810`)as d1

LEFT JOIN (SELECT DISTINCT actor.id,  DATE(TIMESTAMP_SUB(created_at, INTERVAL -24 HOUR)) day FROM `githubarchive.month.201810`) as d2 

USING (id, day)

GROUP BY 1

ORDER BY 1

Below is for BigQuery Standard SQL and is further optimized to not to use any JOINs but rather using analytic functions

#standardSQL

SELECT

  day, 

  COUNT(1) total_users,

  COUNTIF(delta = 1) retained_users

FROM (

  SELECT

    day, id, 

    DATE_DIFF(day, LAG(day) OVER(PARTITION BY id ORDER BY day), DAY) delta

  FROM (

    SELECT DISTINCT

      DATE(created_at) day,

      actor.id

    FROM `githubarchive.month.201810`

  )

)

GROUP BY day

ORDER BY day   

or, if to use original question's notation:

#standardSQL

SELECT

  day, 

  COUNT(1) total_users,

  COUNTIF(delta = 1) retained_users

FROM (

  SELECT

    day, userID, 

    DATE_DIFF(day, LAG(day) OVER(PARTITION BY userID ORDER BY day), DAY) delta

  FROM (

    SELECT DISTINCT

      DATE(eventDate) day,

      userID

    FROM `project.dataset.table`

  )

)

GROUP BY day

ORDER BY day