Count how many calendar events happen per day with Big Query

[nchoffman]

I am trying to get a count for how many meetings (specifically calendar events) are happening per day within our domain. We currently have Big Query exports enabled and, when fully remote, this can be counted through the Usage table -> Customer Usage Metrics -> Meet -> num_meetings_X_calls. However, when hybrid work picks up (split in office/remote) and not all meetings are taking place over Meet, we still want to keep track of how many meetings are happening even if all participants are physically together (ie a Meet link might have been generated on the calendar event but since the link wasn't clicked, no conference_id is made to track it). The Usage table for Calendar only provides num_Xday_active_users which is too general. The Activity table for Calendar  has an event event created where you can count the event_id. However, this isn't exactly tracking how many happened on a certain. Any help would be appreciated.

[phillmidwinter]

You could use a window function in BigQuery to count events by partitioning the dataset by each day perhaps? Something like this...

SELECT COUNT(event_id) OVER (PARTITION BY day)
FROM table;

[nchoffman]

Thanks for the reply. This is generally what I am hoping to do. I'm admittedly new to BQ and am running into an issue in the BY day portion. I've tried various different date/timestamp formats. According to Google, the Activity table generates a new table every day (which is partitioned by day).

I do successfully get a response on a singular day with this (but ideally would like to run this query against the entire table to see trends over time):

SELECT DISTINCT(calendar.event_id)
FROM project
WHERE DATE(_PARTITIONTIME) = "2021-06-23" AND calendar.event_id IS NOT NULL

However, for our organization of ~2,000 people, I am getting 41,651 allegedly unique event IDs  for one day which seems just a tad high. Am I perhaps counting these values wrong? When I look through the results, I am seeing duplicates of event IDs.