Skip to content

Value in range numerical percent

The query for this check calculates the percent of numerical values that are within a range provided by the user. The value_in_range_numerical_percent checks values from column which are within a range of min_value and max_value. Decide whether to include these values to the range, using optional parameters include_min_value and include_max_value. Default in check min_value and max_value are included in the range.

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 are in percent.

Jinja Template

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    100.0 * SUM(
        CASE
            WHEN {{ lib.render_data_range(parameters.min_value, parameters.max_value, parameters.include_min_value, parameters.include_max_value) }} 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 {{ lib.render_data_range(parameters.min_value, parameters.max_value, parameters.include_min_value, parameters.include_max_value) }} 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() -}}