Bigquery: run a query multiple times based on different date_trunc and union the results, instead of multiple UNION ALLs

Question

I am looking to pull and union similar aggregations from a core table, but differing on the time period truncation. Eg this is how I would normally do so:

with base as (
  select 
    event_datetime
    , event_location
    , event_uuid
  from events_table
)

select
  date_trunc(event_datetime, day) as day
  , "day" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by day, event_location

union all

select
  date_trunc(event_datetime, week) as week
  , "week" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by week, event_location

union all

select
  date_trunc(event_datetime, week) as month
  , "month" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by month, event_location

Does anyone know if there is a way to avoid having to maintain three separate subqueries and have a single subquery that re-runs based on the different date_trunc and unions the results (producing the same output as code above)? I know someone at my previous company accomplished this, but I can't figure out how.

Thanks!

Answer 1

Consider below approach

select 
  day, 
  period_type, 
  event_location, 
  count(distinct event_uuid) as number_events
from base t, 
unnest([
  struct('day' as period_type, date_trunc(event_datetime, day) as day),
  struct('week', date_trunc(event_datetime, week)),
  struct('month', date_trunc(event_datetime, month))
]) 
group by day, period_type, event_location