Wsrtjtyk

Let's say I have the following dataset in a table called UserEvents:

  LogIn     UserID

2018-09-30    1

2018-09-30    3

2018-10-01    1

2018-10-01    2

2018-10-01    3

2018-10-02    2

2018-10-02    3

For each day, I'd like to return the number of users logged-in in the past month. Here are the expected results for the above:

Day        PastMonthUsers

2018-09-30      2

2018-10-01      3

2018-10-02      3

Here's what I've tried:

Select 

  UserEvents1.LogIn as Day, 

  count (distinct UserEvents1.UserID) as PastMonthUsers

from UserEvents as UserEvents1

inner join 

  (

    Select 

      LogIn,

      UserID

    from UserEvents

  ) as UserEvents2 

on UserEvents2.LogIn between DATE_SUB(UserEvents1.LogIn, interval 1 month) and UserEvents1.LogIn 

group UserEvents1.Day

The above query ran for 20 minutes on my actual dataset before I cancelled it. Any thoughts on how I can simplify?

Below is for BigQuery Standard SQL and avoids doing self-JOIN in favor of using analytical function - but still can get into issue depends on your data - try and let us know

#standardSQL

SELECT DISTINCT Day, 

  (SELECT COUNT(DISTINCT user) FROM UNNEST(users) user) PastMonthUsers

FROM (

  SELECT Day, 

    ARRAY_AGG(UserID) OVER(ORDER BY days RANGE BETWEEN 29 PRECEDING AND CURRENT ROW) users

  FROM (

    SELECT LogIn Day, UserID, UNIX_DATE(LogIn) days

    FROM `project.dataset.UserEvents`

  )

)