CODEWITHSUNDEEP
google-bigquery
Timon
Timon

Reputation: 73

BigQuery: How to find number of working employees at every hour of the day, given their shift starting and ending time

My data contains the start time and end time of a shift for each employee.

user start_time end_time
5238797 08:00 10:00
3919833 08:00 11:30 
1642034 08:00 11:30 
3818609 08:00 11:30
4903371 09:00 15:00
4786985 09:00 11:00
4513139 09:00 12:00
4452816 09:00 12:00
...

I am trying to create a table where I count the number of working employees at specific time intervals for a given day. Example:

Time Count
08:00 4
08:30 4
09:00 8
09:30 8
10:00 7
...

I have tried to do the following: 

SUM(IF("08:00" >= start_time AND "08:00" < end_time, 1, 0)) AS H8

But any changes in the desired interval (e.g. every 30 mins to 15 mins) will require a lot of manual copy/pasting. Also, the results would be in the columns instead of the rows.

Does anybody know how I could go about this? I am using Standard SQL in Google BigQuery.

Upvotes: 0

Views: 280

Answers (1)

Pentium10
Pentium10

Reputation: 207982

You can have a query like this:

with data as (
  select 5238797 as user, TIME "08:00:00" as start_time, TIME "10:00:00" as end_time
  union all
  select 3919833 as user, TIME "08:00:00" as start_time, TIME "11:30:00" as end_time
  union all
  select 4903371 as user, TIME "09:00:00" as start_time, TIME "15:00:00" as end_time
), slots as (
SELECT
  num,
  time_add(time(8,0,0),INTERVAL 30*num minute ) as slot, 
  data.*
  FROM UNNEST(GENERATE_ARRAY(0,16)) AS num
  cross join data
), t as (
  select slots.*, if(slot between start_time and end_time,user,null) as works from slots
), t_final as (
  select slot,count(distinct works) from t
  group by 1 
  order by 1
)
select * from t_final

which returns:

+----------+-----+
| slot     | cc  |
+----------+-----+
| 08:00:00 | 2   |
+----------+-----+
| 08:30:00 | 2   |
+----------+-----+
| 09:00:00 | 3   |
+----------+-----+
| 09:30:00 | 3   |
+----------+-----+
| 10:00:00 | 3   |
+----------+-----+
| 10:30:00 | 2   |
+----------+-----+
| 11:00:00 | 2   |
+----------+-----+
| 11:30:00 | 2   |
+----------+-----+
| 12:00:00 | 1   |
+----------+-----+
| 12:30:00 | 1   |
+----------+-----+
| 13:00:00 | 1   |
+----------+-----+
| 13:30:00 | 1   |
+----------+-----+
| 14:00:00 | 1   |
+----------+-----+
| 14:30:00 | 1   |
+----------+-----+
| 15:00:00 | 1   |
+----------+-----+
| 15:30:00 | 0   |
+----------+-----+
| 16:00:00 | 0   |
+----------+-----+

Upvotes: 2

Related Questions