Common queries

All queries run against sof.pdex_prior_authorization_item, the materialized view built from a ViewDefinition. Scope is PDex prior-auth EOBs.

1. EOBs per month, last 12 months

SELECT date_trunc('month', created::date)::date AS month,
       COUNT(DISTINCT eob_id)                   AS eob_count
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
GROUP  BY 1
ORDER  BY 1 DESC;

2. EOBs by business identifier system

WITH per_eob AS (
  SELECT eob_id, MAX(business_identifier_system) AS bis
  FROM   sof.pdex_prior_authorization_item
  GROUP  BY eob_id
)
SELECT COALESCE(bis, 'no-business-identifier') AS business_identifier_system,
       COUNT(*) AS eob_count
FROM   per_eob
GROUP  BY 1
ORDER  BY 2 DESC;

3. Top 20 members by PA count, last 12 months

High utilizers.

SELECT patient_ref,
       COUNT(DISTINCT eob_id) AS eob_count,
       COUNT(*)               AS pa_item_rows
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
  AND  patient_ref IS NOT NULL
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  20;

pa_item_rows counts materialized view rows (one per item × adjudication); eob_count is distinct authorizations.

4. Top 20 service codes, last 12 months

Which services get authorized most.

SELECT item_product_or_service_code,
       COUNT(*)               AS item_count,
       COUNT(DISTINCT eob_id) AS eob_count
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  20;

5. Top 20 primary diagnoses, last 12 months

SELECT primary_diagnosis_code, primary_diagnosis_display,
       COUNT(DISTINCT eob_id) AS eob_count
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
  AND  primary_diagnosis_code IS NOT NULL
GROUP  BY 1, 2
ORDER  BY 3 DESC
LIMIT  20;

6. EOBs by outcome × status, last 12 months

The basic adjudication mix.

SELECT outcome, status, COUNT(DISTINCT eob_id) AS eob_count
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
GROUP  BY 1, 2
ORDER  BY 3 DESC;

7. EOBs by denial reason code

Why authorizations get denied.

WITH per_eob AS (
  SELECT eob_id,
         array_agg(DISTINCT code) FILTER (WHERE code IS NOT NULL) AS codes
  FROM   sof.pdex_prior_authorization_item,
         LATERAL unnest(item_denial_reason_codes) AS code
  GROUP  BY eob_id
)
SELECT COALESCE(c, 'no-denial-reason') AS denial_reason_code,
       COUNT(*) AS eob_count
FROM   per_eob
LEFT  JOIN LATERAL unnest(COALESCE(codes, ARRAY[NULL::text])) AS c ON true
GROUP  BY 1
ORDER  BY 2 DESC;

The denial codes come from CARC (Claim Adjustment Reason Codes, maintained by X12). A separate set, RARC (Remittance Advice Remark Codes, M-/N- prefixed), gives supplementary detail. Resolve specific codes against the X12 lists.

8. Denial rate by month

SELECT date_trunc('month', created::date)::date AS month,
       round(100.0 * count(*) FILTER (WHERE outcome = 'error')
                  / nullif(count(*), 0), 1) AS denial_pct,
       count(*) AS eob_count
FROM   sof.pdex_prior_authorization_item
WHERE  created::date >= current_date - INTERVAL '12 months'
GROUP  BY 1
ORDER  BY 1 DESC;