Skip to content

Distinct count percent

The query for this check calculates the percentage of unique values in a specified 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 is in percent.

Jinja Template

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    (count(distinct {{ lib.render_target_column('analyzed_table') }}) / count({{ lib.render_target_column('analyzed_table') }})) * 100 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
    (count(distinct {{ lib.render_target_column('analyzed_table') }}) / count({{ lib.render_target_column('analyzed_table') }})) * 100 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() -}}