Skip to content

Instantly share code, notes, and snippets.

@sungchun12
Created May 12, 2022 16:48
Show Gist options
  • Save sungchun12/98a845d00a691dfb9a39b5ec1ead2986 to your computer and use it in GitHub Desktop.
Save sungchun12/98a845d00a691dfb9a39b5ec1ead2986 to your computer and use it in GitHub Desktop.
-- force a dependency
-- depends_on: analytics.dbt_demo_account_sung.fct_orders
select *
from -- Need this here, since the actual ref is nested within loops/conditions:
-- depends on: analytics.dbt_demo_account_sung.dbt_metrics_default_calendar
(with source_query as (
select
/* Always trunc to the day, then use dimensions on calendar table to achieve the _actual_ desired aggregates. */
/* Need to cast as a date otherwise we get values like 2021-01-01 and 2021-01-01T00:00:00+00:00 that don't join :( */
cast(date_trunc('day', cast(order_date as date)) as date) as date_day,
status_code,
order_count as property_to_aggregate
from analytics.dbt_demo_account_sung.fct_orders
where 1=1
),
spine__time as (
select
/* this could be the same as date_day if grain is day. That's OK!
They're used for different things: date_day for joining to the spine, period for aggregating.*/
date_month as period,
date_day
from analytics.dbt_demo_account_sung.metric_calendar_custom
),
spine__values__status_code as (
select distinct status_code
from source_query
),
spine as (
select *
from spine__time
cross join spine__values__status_code
),
joined as (
select
spine.period,
spine.status_code,
-- has to be aggregated in this CTE to allow dimensions coming from the calendar table
sum(source_query.property_to_aggregate)
as orders_over_time,
boolor_agg(source_query.date_day is not null) as has_data
from spine
left outer join source_query on source_query.date_day = spine.date_day
and ( source_query.status_code = spine.status_code
or source_query.status_code is null and spine.status_code is null
)
group by 1, 2
),
bounded as (
select
*,
min(case when has_data then period end) over () as lower_bound,
max(case when has_data then period end) over () as upper_bound
from joined
),
secondary_calculations as (
select *
from bounded
),
final as (
select
period
, status_code
, coalesce(orders_over_time, 0) as orders_over_time
from secondary_calculations
where period >= lower_bound
and period <= upper_bound
order by 1, 2
)
select * from final
) metric_subq
where orders_over_time != 0
limit 500
/* limit added automatically by dbt cloud */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment