cr_pr_cd_equality_report.sql

set client_min_messages = warning;
drop type if exists pr_cd_equality_report_t cascade;
create type pr_cd_equality_report_t as("count(*)" int, max_score text, max_ratio text);

create or replace function pr_cd_equality_report(
  delta_threshold in double precision)
  returns SETOF pr_cd_equality_report_t
  language sql
as $body$
  with
    measures as (
      select
        score,
        (percent_rank() over w) as pr,
        (cume_dist()    over w) as cd
      from t4_view
      window w as (order by score))
    ,
    ratios as (
      select
        score,
        (pr*100::double precision)/cd as ratio
      from measures)
    ,
    deltas as (
      select
        score,
        ratio,
        abs(ratio - 100) as delta
      from ratios)
    ,
    bad_deltas as (
      select
        score,
        ratio,
        delta
      from deltas
      where delta > delta_threshold)
    ,
    result as (
      select
        count(*)                      as n,
        to_char(max(score), '999.99') as max_score,
        to_char(max(ratio), '999.99') as max_ratio
      from bad_deltas)

select (n, max_score, max_ratio)::pr_cd_equality_report_t
from result;
$body$;