secondary_calculation_period_over_period.sql

{%- macro default__secondary_calculation_period_over_period(metric_name, grain, dimensions, calc_config, metric_config) -%}
    {%- set calc_sql %}
            lag(
                {{ metric_name }}, {{ calc_config.interval }}
            ) over (
                {% if dimensions -%}
                    partition by {{ dimensions | join(", ") }} 
                {% endif -%}
                order by date_{{grain}}
            )
    {%- endset-%}
    
    {%- if calc_config.comparison_strategy == 'difference' -%}
        {% do return (adapter.dispatch('metric_comparison_strategy_difference', 'metrics')(metric_name, calc_sql, metric_config)) %}
    
    {%- elif calc_config.comparison_strategy == 'ratio' -%}
        {% do return (adapter.dispatch('metric_comparison_strategy_ratio', 'metrics')(metric_name, calc_sql, metric_config)) %}
    
    {-% else -%}
        {% do exceptions.raise_compiler_error("Bad comparison_strategy for period_over_period: " ~ calc_config.comparison_strategy ~ ". calc_config: " ~ calc_config) %}
    {%- endif -%}

{% endmacro %}

{% macro default__metric_comparison_strategy_difference(metric_name, calc_sql, metric_config) -%}
    {%- if not metric_config.get("treat_null_values_as_zero", True) %}
        {{ metric_name }} - {{ calc_sql }}
    {%- else -%}
        coalesce({{ metric_name }}, 0) - coalesce(
        {{ calc_sql }}
        , 0)
    {%- endif %}
        
{%- endmacro -%}

{% macro default__metric_comparison_strategy_ratio(metric_name, calc_sql, metric_config) -%}
    
    {%- if not metric_config.get("treat_null_values_as_zero", True) %}
        cast({{ metric_name }} as {{ type_float() }}) / nullif(
            {{ calc_sql }}
            , 0)
    {%- else -%}
         coalesce(
            cast({{ metric_name }} as {{ type_float() }}) / nullif(
            {{ calc_sql }}
            , 0) 
        , 0)
    {%- endif %}
    
{%- endmacro %}