need to count by datetime to a 5 second interval

[jhalfon]

So, I’m hoping someone can help with Looker/BigQuery problem. I have a user start date time and an end date time. I need to count the users online every 5 seconds in blocks from start to finish.
So, in the end I would have a graph showing the concurrent users every 5 seconds, from 00:00:00 to 23:59:59

example: start is 2020-09-01 00:00:03 and end time is 2020-09-01 00:01:00 I need to find concurrent users in 5 second intervals. So, in the interval 00:00:00 to 00:00:05 this example would be counted once. and counted as 1 in 00:00:10 to 00:00:15, and again once in 00:00:16-00:00:20, etc. IT WOULD ALSO need to be counted in each 5 second interval up thru the end time of 00:01:00 for a total of 13 times it is counted over the intervals.
I started to create a derived table with the following code:
SELECT bucket
FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), CURRENT_TIMESTAMP() , INTERVAL 5 SECOND)) AS bucket
however there are a couple of issues, 1. it isn’t showing the entire complete previous day, just the 5 second intervals. and I don’t think I can join this with any tables I need.

[Dawid]

I think in order to join you need to first convert the start and end to a nearest 5-second interval

[Red3]

Yep to elaborate a bit on what @Dawid said, you’d need to left join the user session table to a table with the timestamp of each 5 second interval.

I’m not a bigquery guy but i think it would go something like this:

with seconds as (
    SELECT
        *
    FROM UNNEST(GENERATE_DATE_ARRAY('2015-01-01', '2025-01-01', INTERVAL 5 SECOND)) AS example
)

SELECT 
    seconds.timestamp
    , count(distinct sessions.user_id)
from seconds 
left join sessions 
            ON  seconds.timestamp < sessions.start
            AND seconds.timestamp > sessions.end
group by 1
order by 1 desc

I borrowed the timestamp generation from here: https://stackoverflow.com/a/58169269/7078602
Probably not the most efficient way but that’s how i’d do it

[Dawid]

Exactly… I use UNNEST(GENERATE_DATE_ARRAY to create a minute-granularity calendar table for aggregation