compare_hospital_rates

Compares what a billing code costs ACROSS hospitals (cheapest real price first) with a p10/median/p90 benchmark AND a per-(payer x line-of-business) breakdown. The product query for 'where is care cheapest' and 'what does each payer actually pay'. Reads the canonical serving view `vlada_curated.vw_fact_hospital_chargemaster_canonical`, which resolves each raw payer string to its REAL parent entity (the canonical payer graph) and classifies each plan to a regulatory line-of-business (commercial / medicare_advantage / medicaid / marketplace / ...). Inputs: billing_code (required), state (optional 2-letter), code_system (optional), billing_class (default 'institutional' = facility/UB-04; or 'professional'/'both'; classes are never mixed -- since 1.6.0 'institutional'/'professional' each WIDEN to include billing_class_resolved='both' rows, a charge posted on both the facility and professional side of a bill per the HPT schema; pass 'institutional_only'/'professional_only' for the strict single-class stratum), limit (default 25), payer (optional brand OR parent, e.g. 'Aetna' or 'CVS Health'), line_of_business (optional, e.g. 'medicare_advantage'). Each returned rate carries the view's canonical payer columns (`payer_canonical_id`, `payer_parent_name`, `payer_entity_class`, `payer_match_method`) plus `line_of_business` so a comparison is apples-to-apples (Aetna-MA != Aetna-Commercial). `value.by_payer_lob` is the 'rates across all the payers' table. CROSS-SUBSTRATE: every rate also carries x_medicare (the multiple of the Medicare allowed amount: PFS national non-facility for professional, OPPS APC for outpatient institutional, IPPS MS-DRG national base for inpatient DRG codes); `value.medicare` holds the allowed amount + schedule + year, so the answer is 'this payer pays 1.6x Medicare at this hospital'. VALIDATION (load-bearing): billing_class is normalized + inferred from code_system/rate_type where the source omits it; only the requested class is compared; class-implausible rates (e.g. $0.007 for a CT) and code-relative outliers are filtered. CHEAPEST HEADLINE (1.2.0): `value.cheapest` is ranked over rows PLAUSIBLE for the code's Medicare basis -- when the allowed amount is a real procedure (>= $500) a row under 0.3x Medicare (the comparability cascade's own lower sane bound) is set aside into `value.implausible_tail` (full rows + floor + reason, never deleted; benchmark still includes it), and the rest rank hospital-stated methodology (fee_schedule/case_rate/per_diem) before methodology-unknown, rate ascending within each tier -- read `plausibility` + `rank_tier` on each row. Under the $500 gate (imaging / visits / labs) no floor applies, so a $44 head CT still headlines. CASE-RATE HEADLINE (1.7.0): on a DRG/case-rate seat the floor is 0.5x (not 0.3x -- an inpatient rate below half of IPPS is not credible as the full case price), and `cheapest` headlines hospital-STATED case_rate rows ONLY when at least one survives the floor; methodology-unknown rows are demoted into `value.implausible_tail` (tail_reason='methodology_unknown_demoted') even when they themselves clear the floor. `cheapest` headlines an unknown row only when NO stated row survives, and a `headline_methodology_unknown` warning caveat says so. ONE CODE SYSTEM PER WINDOW (1.3.0): `cheapest` holds rows of the seated code system only (`value.code_system_resolved`, the dominant system or the caller's `code_system`), each row stamped `code_system`; when the floor empties a full window the tool re-asks once for rows at or above the floor rate (`value.cheapest_window.second_pass`), and if `cheapest` still ends empty a `no_plausible_rows_in_window` caveat says why. ONE METHODOLOGY FAMILY PER WINDOW (1.4.0): within the seated code system, `cheapest` and the re-seated `value.benchmark` / `comparable_benchmark` are further restricted to ONE methodology family -- fee_schedule (a line, the natural family for CPT/HCPCS/APC/RC), case_rate (an admission, the natural family for a DRG/MS-DRG seat), or per_diem (a day) -- because a case rate, a per diem and a fee schedule price different economic objects and must never share a median or a ranked list. `methodology_family` (optional: fee_schedule|case_rate|per_diem) overrides the natural seat. Rows the hospital stated in a DIFFERENT family are set aside into `value.other_family`, in full, never blended in; `value.by_methodology_family` reports n/n_hospitals/p10/median/p90 for every family present, and a `methodology_families_mixed` caveat fires when more than 10% of the population sits outside the seated family. BY_METHODOLOGY RECONCILES (1.6.1): `value.by_methodology` (percent_of_charge/fee_schedule/case_rate/per_diem/other/unknown, with median_rate_over_gross_charge) is narrowed to the SAME population `value.benchmark.n` reports -- sum(by_methodology[].n) == benchmark.n, ALWAYS, by construction; the unstratified, every-family view (within the seated code system) is `value.by_methodology_all_families`, which is what `value.percent_of_charge_share` and the `methodology_mix` caveat read (percent_of_charge rows are always 'unknown' family and would otherwise be invisible once a family is seated). SOURCE: CMS Hospital Price Transparency MRFs (45 CFR 180.50) + CMS Medicare PFS/OPPS fee schedules. KNOWN GAPS: not geo-aware beyond state; payer resolution covers ~80.9% of volume and line-of-business ~85.7% (measured on the 70553 probe, 2026-08-13; call coverage_status for live counts), unmatched rates fall to '(unresolved)'/'unspecified'; bare Blue-family labels (e.g. 'Bcbs') are bucketed '(blue family: label too generic to attribute)' with payer_resolution='generic_label' and never attributed to a specific Blue parent; IPPS DRG x_medicare is the national base (excludes wage index/IME/DSH/outliers); x_medicare null for revenue/non-HCPCS CDM codes; ghost-rate filter is heuristic. DRG GROUPER PRECISION (1.5.0): MS-DRG, APR-DRG and AP-DRG are different maintainers with different numbering (MS-DRG 470 = joint replacement, APR-DRG 470 = chronic kidney disease) and only MS-DRG prices against the IPPS base; the served mart still collapses all of them into one bare 'DRG' code system, so `value.medicare.precision` reads 'drg_grouper_unknown' (with a warning caveat) on that seat until the code_system_resolved bucket split rebuild lands, and 'exact' otherwise. BOTH-CLASS DEFAULT STRATUM (1.6.0): billing_class 'institutional'/'professional' each widen to include billing_class_resolved='both' rows (a charge the HPT schema posts for both sides of a bill); every row still carries its own resolved `billing_class`, and a `both_class_rows_included` caveat states the measured count whenever the answer includes at least one such row. Measured on CT (2026-08-18): 9.63% of REAL_NEGOTIATED rows are 'both', 100% with no institutional sibling row -- previously invisible at the old strict-institutional default. Pass 'institutional_only'/'professional_only' for the pre-1.6.0 strict single-class stratum.