data-dwh-dbt-project/models/reporting/general/mtd_aggregated_metrics_by_deal.sql

with
    int_mtd_aggregated_metrics_by_deal as (
        select * from {{ ref("int_mtd_aggregated_metrics_by_deal") }}
    )

select
    -- PRIMARY KEY --
    m.date as date,
    m.id_deal as id_deal,
    m.metric as metric,

    -- TIME ATTRIBUTES --
    m.year as year,
    m.month as month,
    m.day as day,
    case when m.is_end_of_month then 1 else 0 end as is_end_of_month,
    case when m.is_current_month then 1 else 0 end as is_current_month,
    case
        when m.is_end_of_month_or_yesterday then 1 else 0
    end as is_end_of_month_or_yesterday,
    m.first_day_month as first_day_month,

    -- MAIN DEAL ATTRIBUTES --
    m.deal as deal,
    m.active_accommodations_per_deal_segmentation
    as active_accommodations_per_deal_segmentation,
    m.main_billing_country_iso_3_per_deal as main_billing_country_iso_3_per_deal,

    -- HUBSPOT ATTRIBUTES --
    m.account_manager as account_manager,

    -- DEAL BUSINESS SCOPE    
    m.business_scope as business_scope,

    -- DEAL LIFECYCLE --
    m.deal_lifecycle_state as deal_lifecycle_state,

    -- METRIC VALUES AND DISPLAY --
    m.order_by as order_by,
    m.number_format as number_format,
    m.value as value,
    m.previous_year_value as previous_year_value,
    m.relative_increment as relative_increment,
    m.relative_increment_with_sign_format as relative_increment_with_sign_format
from int_mtd_aggregated_metrics_by_deal m
where
    (
        (
            -- Not show current + previous month if the metric depends on
            -- invoicing cycle and it is before the 12th of the month, if it
            -- is the 12th of the month or after, only exclude the current
            -- month.
            m.display_exclusion = 'INVOICING'
            and {{ is_date_before_12th_of_previous_month("m.date") }}
        )
        or (
            -- Handle exclusion for Churn/MRR metrics: do not show them in the
            -- current month.
            m.display_exclusion = 'ONGOING_MONTH'
            and date_trunc('month', m.date) < date_trunc('month', current_date)
        )
        -- Keep all history for the rest of metrics
        or m.display_exclusion = 'NONE'
    )