AHRQ CCSRs

Overview

Code on GitHub

The AHRQ CCSRs data mart implements the Agency for Healthcare Research and Quality's Clinical Classifications Software Refined (CCSR) diagnosis and procedure groupers. AHRQ's CCSR tools group ICD-10-CM diagnosis codes and ICD-10-PCS procedure codes into clinically meaningful categories that are easier to use for analysis than raw diagnosis and procedure codes alone.

Tuva uses the CCSR mappings to support category-level analytics for conditions and procedures, including cost, utilization, outcomes, ranking, and risk adjustment use cases.

Install this standalone package alongside Core using the data mart installation guide.

Methodology

AHRQ maintains separate CCSR resources for ICD-10-CM diagnoses and ICD-10-PCS procedures. The diagnosis grouper maps ICD-10-CM codes to CCSR categories and body systems. Some diagnosis codes can map to more than one CCSR category, so Tuva provides both long-format and singular category outputs. The procedure grouper maps ICD-10-PCS procedure codes to CCSR categories and clinical domains.

The CCSR methodology is maintained by AHRQ as part of the Healthcare Cost and Utilization Project. AHRQ's CCSR overview describes the grouper as a way to aggregate ICD-10-CM/PCS codes into clinically meaningful categories.

Outputs

Model	Description
ccsr__long_condition_category	One row per condition record and mapped CCSR category. Use this when a diagnosis can belong to multiple clinically relevant categories.
ccsr__wide_condition_category	One row per encounter, claim, person, and data source with CCSR condition categories pivoted into dxccsr_* indicator columns.
ccsr__singular_condition_category	First-listed diagnosis records with the single configured default CCSR category selected for analysis.
ccsr__long_procedure_category	One row per procedure record and CCSR procedure category.
ccsr__wide_procedure_category	One row per source-scoped encounter and person with CCSR procedure categories pivoted into prccsr_* indicator columns.
ccsr__procedure_summary	Source-scoped, claim-linked procedure counts and approach rates by category and root operation.

The package materializes these relations in your root project's target schema by default. Replace <target_schema> in the examples below with that schema, or with the custom schema your project assigns to the package.

The wide models generate their category indicator columns dynamically when dbt runs. Those runtime-generated dxccsr_* and prccsr_* columns are not enumerated in the package's static model YAML, so the package-backed dictionary lists only the fixed identifier, version, and runtime columns for the wide tables.

The standalone package restores ccsr__procedure_summary for source-scoped, claim-linked procedure counts and approach rates by category and root operation. Include data_source in downstream keys and joins when upgrading from the bundled 0.18 output.

Tuva 1.0 retains the reviewed DXCCSR and PRCCSR v2023.1 mapping baseline. The CCSR 2026 mapping refresh is separate future package work; the Core Procedure Grouper's newer source mapping does not change this standalone package's CCSR version.

Example SQL

Condition Count by Body System

select
      body_system
    , count(*) as condition_count
from <target_schema>.ccsr__singular_condition_category
group by body_system
order by condition_count desc;

Condition Count by CCSR Category

select
      ccsr_category
    , ccsr_category_description
    , count(*) as condition_count
from <target_schema>.ccsr__singular_condition_category
group by
      ccsr_category
    , ccsr_category_description
order by condition_count desc;

Procedure Count by Clinical Domain

select
      clinical_domain
    , count(*) as procedure_count
from <target_schema>.ccsr__long_procedure_category
group by clinical_domain
order by procedure_count desc;

Acute Inpatient Visits by CCSR Diagnosis Category

with ccsr_encounter_category as (
    select distinct
          encounter_id
        , data_source
        , ccsr_category
        , ccsr_category_description
        , ccsr_parent_category
        , body_system
    from <target_schema>.ccsr__long_condition_category
    where diagnosis_rank = 1
)

select
      ccsr.ccsr_category
    , ccsr.ccsr_category_description
    , ccsr.ccsr_parent_category
    , ccsr.body_system
    , count(*) as visit_count
    , sum(cast(encounter.paid_amount as decimal(18, 2))) as paid_amount
    , cast(
        sum(encounter.paid_amount) / nullif(count(*), 0)
        as decimal(18, 2)
      ) as paid_per_visit
from core.encounter as encounter
left join ccsr_encounter_category as ccsr
    on encounter.encounter_id = ccsr.encounter_id
    and encounter.data_source = ccsr.data_source
where encounter.encounter_type = 'acute inpatient'
group by
      ccsr.ccsr_category
    , ccsr.ccsr_category_description
    , ccsr.ccsr_parent_category
    , ccsr.body_system
order by visit_count desc;

Emergency Department Visits by CCSR Diagnosis Category

with ccsr_encounter_category as (
    select distinct
          encounter_id
        , data_source
        , ccsr_category
        , ccsr_category_description
        , ccsr_parent_category
        , body_system
    from <target_schema>.ccsr__long_condition_category
    where diagnosis_rank = 1
)

select
      ccsr.ccsr_category
    , ccsr.ccsr_category_description
    , ccsr.ccsr_parent_category
    , ccsr.body_system
    , count(*) as visit_count
    , sum(cast(encounter.paid_amount as decimal(18, 2))) as paid_amount
    , cast(
        sum(encounter.paid_amount) / nullif(count(*), 0)
        as decimal(18, 2)
      ) as paid_per_visit
from core.encounter as encounter
left join ccsr_encounter_category as ccsr
    on encounter.encounter_id = ccsr.encounter_id
    and encounter.data_source = ccsr.data_source
where encounter.encounter_type = 'emergency department'
group by
      ccsr.ccsr_category
    , ccsr.ccsr_category_description
    , ccsr.ccsr_parent_category
    , ccsr.body_system
order by visit_count desc;