Skip to content

Non negative percent

The query for this check calculates the percentage of non-negative values from column.

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_target_column('analyzed_table') }} < 0 THEN 0
            ELSE 1
        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/bigquery.sql.jinja2' as lib with context -%}
SELECT
    100.0 * SUM(
        CASE
            WHEN {{ lib.render_target_column('analyzed_table') }} < 0 THEN 0
            ELSE 1
        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() -}}