compiled_metric_example.sql

-- 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 */