ClassDojo Metrics engine Blog post sample code

example_generate_wbr_periods.sql

{% macro generate_wbr_periods(
    source_table
    , date_column
    , date_end
    , metrics_sql_body
    , optional_entity_filter=''
) 
%}

{{ config(
    materialized='table',
    unique_key='foo',
    dist='auto',
    sort=[date_column]
) }}

/*
Arguments:
- source_table: The name of the source wide table.
- date_column: The date column used to determine the timeframe.
- date_end: The date to limit the result
- metrics_sql_body: a "body" of SQL code to calculate desired metrics
- optional_entity_filter: a desired filter on the source_table
*/

-- Base table
WITH dbt_wide_table AS (
    SELECT *
    FROM {{ ref(source_table) }}
    WHERE {% if optional_entity_filter == '' %} TRUE {% else %} {{ optional_entity_filter }} {% endif %}
)

, reference_dates AS (
    SELECT
        '{{ date_end }}'::DATE AS configured_date_end

        , DATE_TRUNC('MONTH', configured_date_end) AS current_month_start

        , EXTRACT(DAY FROM configured_date_end - DATE_TRUNC('MONTH', configured_date_end))::INT AS days_elapsed_in_month
        
        , DATE_TRUNC('QUARTER', configured_date_end) AS current_quarter_start
        , EXTRACT(DAY FROM configured_date_end - DATE_TRUNC('QUARTER', configured_date_end))::INT AS days_elapsed_in_quarter
        
        , DATE_TRUNC('YEAR', configured_date_end) AS current_year_start
        , EXTRACT(DAY FROM configured_date_end - DATE_TRUNC('YEAR', configured_date_end))::INT AS days_elapsed_in_year
)

-- CTE for each timeframe
, daily_stats AS (
    SELECT
        'day' AS period_type
        , EXTRACT(doy FROM {{ date_column }}) AS period_value
        , EXTRACT(year FROM {{ date_column }})::VARCHAR(4) AS year
        , {{ date_column }}::DATE AS date_key
        , {{ date_column }}::DATE AS end_date_key
        
        {{ metrics_sql_body }}
        
    FROM dbt_wide_table
    GROUP BY 1, 2, 3, 4, 5
)

/*
Redshift's EXTRACT(week FROM <date>) function follows the ISO standard of a
week starting Monday and ending Sunday.

*/
, weekly_stats AS (
    SELECT
        'week' AS period_type

        -- Typical way of calculating ISO-week, which is "Mon-Sun" weeks
        EXTRACT(week FROM {{ date_column }}) AS period_value,
        EXTRACT(year FROM DATE_TRUNC('WEEK', {{ date_column }})::DATE)::VARCHAR(4) AS year,
        DATE_TRUNC('WEEK', {{ date_column }})::DATE AS date_key,
        DATE_TRUNC('WEEK', {{ date_column }})::DATE + 6 AS end_date_key,

        {{ metrics_sql_body }}

    FROM dbt_wide_table
    GROUP BY 1, 2, 3, 4, 5
    HAVING COUNT(DISTINCT date_key) = 7 -- only keep full weeks 
)

, monthly_stats AS (
    SELECT
        'month' AS period_type
        , EXTRACT(month FROM {{ date_column }}) AS period_value
        , EXTRACT(year FROM {{ date_column }})::VARCHAR(4) AS year
        , DATE_TRUNC('MONTH', {{ date_column }})::DATE AS date_key
        , MAX({{date_column}})::DATE AS end_date_key

        {{ metrics_sql_body }}

    FROM dbt_wide_table
    GROUP BY 1, 2, 3, 4
    HAVING BOOL_OR(date_key = LAST_DAY(date_key)) -- only keep full months (at least one row on the last day of month)
)

-- CTE for Month-to-Date stats
, month_to_date_stats AS (
    SELECT
        'month_to_date' AS period_type
        , EXTRACT(month FROM {{ date_column }}) AS period_value
        , EXTRACT(year FROM {{ date_column }})::VARCHAR(4) AS year
        , DATE_TRUNC('MONTH', {{ date_column }})::DATE AS date_key
        , DATEADD(day, (SELECT days_elapsed_in_month FROM reference_dates), DATE_TRUNC('MONTH', {{ date_column }}))::DATE AS end_date_key

        {{ metrics_sql_body }}
    FROM dbt_wide_table
    WHERE 
        {{ date_column }} <= DATEADD(day , (SELECT days_elapsed_in_month FROM reference_dates) , DATE_TRUNC('MONTH', {{ date_column }})
            )
    GROUP BY 1, 2, 3, 4, 5
)

-- CTE for Quarter-to-Date stats
, quarter_to_date_stats AS (
    SELECT
        'quarter_to_date' AS period_type
        , EXTRACT(quarter FROM {{ date_column }}) AS period_value
        , EXTRACT(year FROM {{ date_column }})::VARCHAR(4) AS year
        , DATE_TRUNC('QUARTER', {{ date_column }})::DATE AS date_key
        , DATEADD(day, (SELECT days_elapsed_in_quarter FROM reference_dates), DATE_TRUNC('QUARTER', {{ date_column }}))::DATE AS end_date_key

        {{ metrics_sql_body }}

    FROM dbt_wide_table
    WHERE {{ date_column }} <= DATEADD(day, (SELECT days_elapsed_in_quarter FROM reference_dates), DATE_TRUNC('QUARTER', {{ date_column }}))
    GROUP BY 1, 2, 3, 4, 5
)

-- CTE for Year-to-Date stats
, year_to_date_stats AS (
    SELECT
        'year_to_date' AS period_type
        , 1 AS period_value  -- Representing full year-to-date
        , EXTRACT(year FROM {{ date_column }})::VARCHAR(4) AS year
        , DATE_TRUNC('YEAR', {{ date_column }})::DATE AS date_key
        , DATEADD(day, (SELECT days_elapsed_in_year FROM reference_dates), DATE_TRUNC('YEAR', {{ date_column }}))::DATE AS end_date_key

        {{ metrics_sql_body }}

    FROM dbt_wide_table
    WHERE {{ date_column }} <= DATEADD(day, (SELECT days_elapsed_in_year FROM reference_dates), DATE_TRUNC('YEAR', {{ date_column }}))
    GROUP BY 1, 2, 3, 4, 5
)

-- Union all CTEs at the end
SELECT * FROM daily_stats
UNION ALL
SELECT * FROM weekly_stats
UNION ALL
SELECT * FROM monthly_stats
UNION ALL
SELECT * FROM month_to_date_stats
UNION ALL
SELECT * FROM quarter_to_date_stats
UNION ALL
SELECT * FROM year_to_date_stats

{% endmacro %}