Evaluate performance and prevalence for any series of diagnostics tests, incl. SARS

with tests as (
 select
    :reg :: text  as region_name,
    :rid :: text  as report_id,
    :tst :: float as tests,
    :pos :: float as positives
  ),

permutations as  (
 select
    sens :: float as sensitivity,
    spec :: float as specificity
 from
    generate_series(0.005, 1.000, 0.005) as sens,
    generate_series(0.005, 1.000, 0.005) as spec
  ),

prevalences as (
 select
    (positives / tests + specificity - 1) :: float /
    (sensitivity + specificity - 1) :: float as prevalence,
    *
  from
    permutations,
    tests
  where
    sensitivity + specificity > 1
  ),

matrices as (
  select
    (tests * prevalence * sensitivity) :: float       as true_positives,
    (tests * (1 - prevalence) * specificity) :: float as true_negatives,
    *
  from
    prevalences
  where
    prevalence between 0 and 1
  ),

results as (
  select
    positives - true_positives           as false_positives,
    (tests - positives) - true_negatives as false_negatives,
    *
  from
    matrices
  )

select
  region_name,
  report_id,
  (tests) :: int                    as tests_performed,
  (positives) :: int                as positives_reported,
  (tests * prevalence) :: int       as has_disease,
  (tests * (1 - prevalence)) :: int as hasnot_disease,
  (true_positives) :: int           as true_positives,
  (false_positives) :: int          as false_positives,
  (true_negatives) :: int           as true_negatives,
  (false_negatives) :: int          as false_negatives,
  sensitivity :: numeric(4,3),
  specificity :: numeric(4,3),
  prevalence :: numeric(4,3)
from
  results
where
  (false_positives + true_positives) :: int = positives :: int