Run a query multiple times based on different date parameters in the when statements 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 date period defined in the WHEN statements. This is how I would normally do so:

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

select
  "last_30_days" as period
  , event_location
  , count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 30
group by event_location

union all

select
  "last_60_days" as period
  , event_location
  , count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 60
group by event_location

union all

select
  "all_time" as period
  , event_location
  , count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 10000
group by 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 period and unions the results (producing the same output as code above)?

Answer 1

Consider below approach

select 
  period, 
  event_location, 
  count(distinct if(event_datetime >= current_date - days, event_uuid, null)) as number_events
from base, 
unnest([
  struct('last_30_days' as period, 30 as days),
  struct('last_60_days', 60),
  struct('all_time', 10000)
]) 
group by period, event_location