Column datetime difference percent

The query for this check calculates the timestamp difference between two columns and check if the difference is less than the max_difference parameter.

Successfully classified records are assigned value of 1, and any other values, 0. Those values are then summed (so the counting of valid values is effectively performed), divided by the number of records, and multiplicated by a 100.0 so that the results is in percent.

---

Jinja Template

BigQuerySnowflake

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    100.0*SUM(
        CASE
            WHEN
                ABS(DATETIME_DIFF({{ lib.render_datetime_columns_to_calculate_diff(parameters.column1, parameters.column2, parameters.time_scale) }})) < {{ parameters.max_difference }} THEN 1
            ELSE 0 END
            )/COUNT(*) AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    100.0*SUM(
        CASE
            WHEN
                ABS(TIMEDIFF({{ lib.render_datetime_columns_to_calculate_diff(parameters.column1, parameters.column2, parameters.time_scale) }})) < {{ parameters.max_difference }} THEN 1
            ELSE 0 END
            )/COUNT(*) AS actual_value
    {{- lib.render_dimension_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}