Hospital encounters, diagnoses and claims

1. 1Which payers deny the most claims, and for what reasons?other denies the most (10.3%), most often coding_error.

   with reasons as (
     select payer, denial_reason, count(*) as denials,
            row_number() over (partition by payer order by count(*) desc, denial_reason) as rank_in_payer
     from claims
     where status = 'denied'
     group by payer, denial_reason
   ),
   payers as (
     select payer, count(*) as claims,
            sum(case when status = 'denied' then 1 else 0 end) as denied
     from claims
     group by payer
   )
   select p.payer, p.claims, p.denied,
          round(100.0 * p.denied / p.claims, 1) as denial_rate_pct,
          r.denial_reason as top_reason, r.denials as top_reason_denials
   from payers p
   left join reasons r on r.payer = p.payer and r.rank_in_payer = 1
   order by denial_rate_pct desc

   payer	claims	denied	denial_rate_pct	top_reason	top_reason_denials
   other	263	27	10.3	coding_error	7
   commercial	2,136	198	9.3	not_medically_necessary	55
   medicare	1,111	103	9.3	not_medically_necessary	32
   self_pay	275	23	8.4	not_medically_necessary	10
   medicaid	952	75	7.9	missing_authorization	24

2. 2What is the average length of stay by diagnosis category?Longest average stay: gynecologic (1.17 days); shortest: urologic (0.46 days).

   -- An encounter with diagnoses in several categories counts once in each.
   select d.category,
          count(distinct e.encounter_id) as encounters,
          round(avg(e.los_hours) / 24, 2) as avg_stay_days,
          round(percentile_cont(0.5) within group (order by e.los_hours), 1) as median_stay_hours
   from (select distinct encounter_id, category from diagnoses) d
   join encounters e on e.encounter_id = d.encounter_id
   group by d.category
   order by avg_stay_days desc

   category	encounters	avg_stay_days	median_stay_hours
   gynecologic	41	1.17	1.1
   injury	362	1.07	1.2
   respiratory	1,314	1.05	1.3
   mental_health	818	1.04	1.3
   infectious	591	1.03	1.3
   cardiovascular	976	1.02	1.3
   neurological	265	0.99	1.3
   gastrointestinal	680	0.91	1.4
   metabolic	1,597	0.88	1.3
   musculoskeletal	843	0.85	1.3
   pregnancy	52	0.79	1.2
   urologic	48	0.46	1.1

3. 3How much of billed revenue is finally paid?Payers paid $7,938,924 of $18,033,950 billed, 44.0%.

   select status,
          count(*) as claims,
          round(sum(billed_amount), 2) as billed,
          round(sum(coalesce(allowed_amount, 0)), 2) as allowed,
          round(sum(coalesce(paid_amount, 0)), 2) as paid_by_payer,
          round(sum(coalesce(patient_responsibility, 0)), 2) as patient_responsibility,
          round(100 * sum(coalesce(paid_amount, 0)) / sum(billed_amount), 1) as paid_pct_of_billed
   from claims
   group by status
   order by billed desc

   status	claims	billed	allowed	paid_by_payer	patient_responsibility	paid_pct_of_billed
   paid	4,122	15,718,050.63	7,938,924.1	7,938,924.1	7,779,126.53	50.5
   denied	426	1,495,593.8	778,187.9	0	0	0
   pending	189	820,305.41	455,793.62	0	0	0

4. 4Which lab flags cluster around which diagnoses?The strongest cluster: LDL cholesterol is abnormal in 70.2% of musculoskeletal encounters against 63.7% overall.

   -- Abnormal share of each analyte within encounters coded with each category; pairs with at least 50 results.
   with labs as (
     select d.category, l.analyte, l.flag
     from lab_results l
     join (select distinct encounter_id, category from diagnoses) d on d.encounter_id = l.encounter_id
   ),
   base as (
     select analyte, 100.0 * sum(case when flag <> 'normal' then 1 else 0 end) / count(*) as abnormal_pct
     from lab_results
     group by analyte
   )
   select x.category, x.analyte, count(*) as results,
          round(100.0 * sum(case when x.flag <> 'normal' then 1 else 0 end) / count(*), 1) as abnormal_pct,
          round(b.abnormal_pct, 1) as abnormal_pct_all_encounters
   from labs x
   join base b on b.analyte = x.analyte
   group by x.category, x.analyte, b.abnormal_pct
   having count(*) >= 50
   order by 100.0 * sum(case when x.flag <> 'normal' then 1 else 0 end) / count(*) - b.abnormal_pct desc
   limit 10

   category	analyte	results	abnormal_pct	abnormal_pct_all_encounters
   musculoskeletal	LDL cholesterol	131	70.2	63.7
   mental_health	Hemoglobin A1c	174	60.9	55.8
   injury	White blood cell count	128	10.2	6.3
   cardiovascular	LDL cholesterol	150	67.3	63.7
   infectious	Glucose	217	62.7	59.3
   infectious	Sodium	205	6.8	3.6
   mental_health	Platelet count	280	6.8	3.7
   infectious	Creatinine	219	10	7.2
   mental_health	LDL cholesterol	131	66.4	63.7
   gastrointestinal	LDL cholesterol	104	66.3	63.7

Every query runs on the files in the download as they are. ANSWERS.sql holds all of them; run it in DuckDB from the dataset's folder.

• Retail star schemaA dimensional model you can actually practise joins on: one fact table, four dimensions, 47,051 rows, every join resolving and every total known.47,051 rows · 5 tables
• Machine degradation100 machines monitored from commissioning to failure, with an exact remaining-useful-life label on every one of 23,118 readings.46,336 rows · 3 tables
• Ecommerce storefrontA year of orders across five joined tables, with best sellers, heavy buyers, a real Q4 peak and totals that reconcile to the cent.49,493 rows · 5 tables
• B2B SaaS subscription analyticsAccounts, seats, MRR, churn and support load, where company size actually drives the plan.42,240 rows · 5 tables
• Credit risk portfolio3,000 borrowers and 4,200 commercial loans, where a credit rating actually predicts whether the loan defaults, at the rate S&P's own data says it should.7,200 rows · 2 tables
• Network intrusion / netflow500 hosts and 20,000 labeled flows, where a port scan, a DDoS flood, and a brute-force attempt each carry the real statistical signature their MITRE ATT&CK technique actually leaves in traffic.20,500 rows · 2 tables