/komodo-provider-codeset

Search Komodo tables for common codes (procedure, diagnosis, etc.) given a provider type from Spines tables.

Usage

/komodo-provider-codeset

When invoked, the skill asks you for the provider type's Spines tables and any filters, then queries Komodo medical claims to find the most common codes associated with that provider type.

What It Does

1. Takes the Spines NPI table(s) you provide and extracts all distinct NPIs for the provider type.
2. Joins against Komodo medical_headers on hco_1_npi / hco_2_npi to find claims associated with those providers.
3. Joins medical_service_lines for procedure-level detail.
4. Applies any filters you specify (e.g., professional claims only, 2024+).
5. Runs six aggregation queries to find the most common:
   • Procedure codes (HCPCS/CPT) from service lines
   • Diagnosis codes (unpivoted from da, d1–d26 on headers)
   • Revenue codes from service lines
   • Place of service from service lines
   • Modifier combinations from service lines
   • Summary stats (total claims, patients, claim type breakdown, date range)
6. Saves all results to a subfolder with an Excel spreadsheet, a .sql file of all queries executed, and a README summarizing the analysis.

Inputs

• Spines table(s) — one or more tables containing NPIs for the provider type
  • Example: tq_dev.spines.spines_provider_urgent_care, tq_dev.spines.spines_provider_urgent_care_additional_npis
• Filters (optional) — additional WHERE clauses to apply
  • Example: claim_type_code = 'P', year(statement_from) >= 2024
• Output folder name — subfolder for all outputs (default: komodo_<provider_type>_codeset/)

Outputs

• <output_folder>/codes.xlsx — Excel workbook with 6 sheets: Summary, Procedure Codes, Diagnosis Codes, Revenue Codes, Place of Service, Modifiers. Each sheet includes raw counts and percentage of total.
• <output_folder>/queries.sql — All SQL queries executed with actual values (copy-paste to reproduce in Trino)
• <output_folder>/README.md — Markdown summary of the request, methodology, key stats, and top 20 codes per category
• Console output — Top 20 rows of each code type printed for quick review

Reference

Komodo Tables

Table	Alias	Key Columns
tq_intermediate.external_komodo.medical_headers	mh	visit_id, encounter_key, hco_1_npi, hco_2_npi, claim_type_code, statement_from, da/d1–d26, p1–p25
tq_intermediate.external_komodo.medical_service_lines	msl	visit_id, encounter_key, procedure_code, revenue_code, place_of_service, modifier_1–modifier_4, diagnosis_code_1–diagnosis_code_4

Common Filters

Filter	Description
claim_type_code = 'P'	Professional claims only
claim_type_code = 'I'	Institutional claims only
year(statement_from) >= 2024	Recent claims

NPI Join Logic

Provider type is filtered by joining spines NPI tables against hco_1_npi OR hco_2_npi on medical_headers.

Output Sheets

Sheet	Contents
Summary	Total claims, patients, claim type breakdown, date range
Procedure Codes	HCPCS/CPT codes with counts and %
Diagnosis Codes	Unpivoted dx codes (da, d1–d26) with counts and %
Revenue Codes	Revenue codes with counts and %
Place of Service	POS codes with counts and %
Modifiers	Modifier combinations with counts and %