Version: 2.3

v2.3.2

Prod Features

Non-outlier rate presence for 0464U

provider_type	n
Physician Group	41736
Hospital	16139
Laboratory	6832

Code

# Non-outlier rate presence for 0464U (2025_08 only for labs)
df = pd.read_sql(f"""
SELECT provider_type, count(*)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE billing_code = '0464U'
AND canonical_rate_score > 1
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
""", con=trino_conn)
print(df.to_markdown(index=False))

Non-outlier rate presence for other guardant codes

'0473U',
'0487U',
'0422U',
'0486U',
'0485U',
'0464U'

provider_type	billing_code	n
Laboratory	0473U	7218
Laboratory	0464U	6832
Laboratory	0487U	6045

0422U, 0485U, and 0486U will not be in the tool until we refresh with Q1 data due to not being on the CLFS until 2026

Code

# check other guardant lab codes
df = pd.read_sql(f"""
SELECT provider_type, billing_code, count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE billing_code IN (
    '0473U',
    '0487U',
    '0422U',
    '0486U',
    '0485U',
    '0464U'
)
AND canonical_rate_score > 1
AND provider_type = 'Laboratory'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10
""", con=trino_conn)
print(df.to_markdown(index=False))

confirm Sam's lab request is present

n
2835

Code

df = pd.read_sql(f"""
--confirmed Sam's request is in there (Agendia)
select *
from tq_production.spines.spines_provider_labs
where npi = '1821253584'
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
select COUNT(*)
from tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
where provider_id = '7144998591559364424'
AND canonical_rate_score > 1
""", con=trino_conn)
print(df.to_markdown(index=False))

add best payer and hospital fields

need to add best payer/hospital contract_methodology in provider-SKU table build (i.e. prod_clickhouse_abridged)

best_payer_rate	best_hospital_rate	best_payer_method_params	best_hospital_method_params	best_payer_rate_type	best_hospital_rate_type
12.9892	12.9892	"percentage":90.00,"ccr_adjustment":0.2238629894,"gross_charge":64.47	"percentage":90.0000000000000,"ccr_adjustment":0.2238629894,"gross_charge":64.47	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
148.5	148.5	"percentage":100.00,"ccr_adjustment":1.0000000000,"gross_charge":148.5	"percentage":100.0000000000000,"ccr_adjustment":1.0000000000,"gross_charge":148.5	transform: payer_gc_komodo_cbsa_perc_to_dol	transform: hospital_fee_schedule_gc_komodo_cbsa_perc_to_dol
1097.5	1097.5	"percentage":100.00,"ccr_adjustment":1.0000000000,"gross_charge":1097.5	"percentage":100.0000000000000,"ccr_adjustment":1.0000000000,"gross_charge":1097.5	transform: payer_gc_komodo_cbsa_perc_to_dol	transform: hospital_fee_schedule_gc_komodo_cbsa_perc_to_dol
4.61	4.61	"percentage":57.61,"ccr_adjustment":0.4576551718,"gross_charge":17.5	"percentage":57.6100000000000,"ccr_adjustment":0.4576551718,"gross_charge":17.5	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
1279.34	1279.18	"percentage":79.01,"ccr_adjustment":0.4064288669,"gross_charge":3984.0	"percentage":79.0000000000000,"ccr_adjustment":0.4064288669,"gross_charge":3984.0	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
8091.97	8091.97	"percentage":78.69,"ccr_adjustment":0.8557342318,"gross_charge":12017.0	"percentage":78.6900000000000,"ccr_adjustment":0.8557342318,"gross_charge":12017.0	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
13717.4	13717.4	"percentage":78.69,"ccr_adjustment":0.8557342318,"gross_charge":20371.0	"percentage":78.6900000000000,"ccr_adjustment":0.8557342318,"gross_charge":20371.0	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
116.488	116.488	"percentage":80.00,"ccr_adjustment":0.6497236487,"gross_charge":224.11111111111111	"percentage":80.0000000000000,"ccr_adjustment":0.6497236487,"gross_charge":224.11111111111111	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
5332.51	5331.83	"percentage":79.01,"ccr_adjustment":0.4064288669,"gross_charge":16606.0	"percentage":79.0000000000000,"ccr_adjustment":0.4064288669,"gross_charge":16606.0	transform: payer_gc_hosp_state_perc_to_dol	transform: hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol
47524.5	50907.6	"per_diem":2655.00,"glos":17.9000	"per_diem":2844.00,"glos":17.9000	transform: payer_per_diem_mult_glos	transform: hosp_per_diem_mult_glos

Code

# [CLD-v2.3.2] add best payer and hospital contract methodology and params
# [CLD-v2.3.2] payer hospital best rate columns
df = pd.read_sql(f"""
SELECT
    -- best_payer_contract_methodology,
    -- best_hospital_contract_methodology,
    best_payer_rate,
    best_hospital_rate,
    best_payer_method_params,
    best_hospital_method_params,
    best_payer_rate_type,
    best_hospital_rate_type
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE best_payer_method_params IS NOT NULL
AND best_hospital_method_params IS NOT NULL
LIMIT 10
""", con=trino_conn)
df

Use "payer_hospital" for validated rates

canonical_rate_source	canonical_rate_score	n
payer	2	52344235
hospital	2	1895781
imputation	2	15472904
payer_hospital	5	32964454
imputation	4	27221378
payer	4	94171030
	0	2856389431
payer	3	217617839
imputation	1	26614545
hospital	3	8922889
payer	1	60767894
hospital	1	9334423
hospital	4	57767469
imputation	3	57146524

Code

# [CLD-v2.3.2] Use payer_hospital for validated rates
df = pd.read_sql(f"""
SELECT
    canonical_rate_source,
    canonical_rate_score,
    count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
GROUP BY 1,2
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
SELECT
    count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE canonical_rate_score = 5
AND canonical_rate_source IS NULL
""", con=trino_conn)
print(df.to_markdown(index=False))

Include all labs in core rates

Increased to 5391 from 972

_col0
5391

Code

# [CLD-v2.3.2] Include all labs in core rates
df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_11
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_08
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_1.tmp_int_combined_2025_10
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df.to_markdown(index=False))

Add new ASC codes

billing_code	n_providers
93655	1071
93656	30
93657	1071

Code

# [CLD-v2.3.2] Add new ASC codes
df = pd.read_sql(f"""
SELECT billing_code, COUNT(distinct provider_id) as n_providers
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE provider_type = 'ASC'
AND billing_code IN ('93655','93656','93657')
AND canonical_rate_score > 1
GROUP BY 1
""", con=trino_conn)
print(df.to_markdown(index=False))

update OPPS codeset

Increased to 7512 from 7084

Code

# [CLD-v2.3.2] update OPPS codeset
df = pd.read_sql(f"""
SELECT COUNT(DISTINCT billing_code)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE bill_type = 'Outpatient'
AND provider_type = 'Hospital'
AND canonical_rate_score > 1
""", con=trino_conn)
print(df.to_markdown(index=False))

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT billing_code)
FROM tq_dev.internal_dev_csong_cld_v2_3_1.tmp_int_combined_2025_10
WHERE bill_type = 'Outpatient'
AND provider_type = 'Hospital'
AND canonical_rate_score > 1
""", con=trino_conn)
print(df.to_markdown(index=False))

filter EXCHANGE in hospital plan bridge

Sample trace_raw_id from Clear Rates and check payer_class_name in hospital_data.hospital_rates

payer_class_name	n
Exchange	88609

They all have "Exchange" payer class name now.

Code

# [CLD-v2.3.2] filter EXCHANGE in hospital plan bridge
df = pd.read_sql(f"""
WITH
sample AS (
    SELECT hospital_rates_id
    FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_raw_mrf_hospital_rates_2025_11
    WHERE network_id IN (
        SELECT network_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_network_spine_2025_11
        WHERE network_type = 'EXCHANGE'
    )
    AND provider_id IN (
        SELECT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_11
        WHERE provider_type = 'Hospital'
    )
    ORDER BY RANDOM()
    LIMIT 100000
)
SELECT payer_class_name, count(*) as n
FROM tq_production.hospital_data.hospital_rates hr
WHERE hr.id IN (SELECT hospital_rates_id FROM sample)
GROUP BY 1
""", con=trino_conn)
df

check for RC to HCPCS imputation

canonical_rate_source	canonical_rate_score	bill_type	provider_type	canonical_rate_type	_col5
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_perc_to_dol	212336
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_perc_to_dol	19761
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_state_perc_to_dol	1710778
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_perc_to_dol	32548
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_cbsa_perc_to_dol	210832
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_cbsa_perc_to_dol	208439
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_cbsa_perc_to_dol	404372
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_state_perc_to_dol	408854
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_perc_to_dol	804911
imputation	3	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_state_perc_to_dol	5893056
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_komodo_cbsa_perc_to_dol	215762
imputation	2	Outpatient	Hospital	impute: rc_hcpcs_gc_hosp_state_perc_to_dol	2614826

Code

df = pd.read_sql(f"""
SELECT canonical_rate_source, canonical_rate_score, bill_type, provider_type, canonical_rate_type, COUNT(*)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE canonical_rate_type LIKE '%rc_hcpcs%'
AND canonical_rate_score > 1
GROUP BY 1, 2, 3, 4, 5
""", con=trino_conn)
print(df.to_markdown(index=False))

Full Code

Code

# %%
from IPython import get_ipython
from jinja2 import Template

if get_ipython() is not None:
    get_ipython().run_line_magic("load_ext", "autoreload")
    get_ipython().run_line_magic("autoreload", "2")
from dotenv import load_dotenv
import pandas as pd
from tqds.sql import db, sql

import pandas as pd
import numpy as np

import seaborn as sns
import matplotlib.pyplot as plt
sns.set_theme(style='whitegrid', rc={'figure.figsize': (14, 12)})

pd.options.display.max_columns = None
pd.options.display.max_rows = 100
pd.options.display.max_colwidth = None
pd.set_option('display.float_format', lambda x: '%.3f' % x)
trino_conn = db.TrinoClient().conn
redshift_conn = db.RedshiftClient().conn
query = sql.Query()


# %% [markdown]
"""
PRs:

[CLD-v2.3.2] add best payer and hospital contract methodology and params
[CLD-v2.3.2] payer hospital best rate columns
[CLD-v2.3.2] Use payer_hospital for validated rates
[CLD-v2.3.2] Include all labs in core rates
[CLD-v2.3.2] Add new ASC codes
[CLD-v2.3.2] update OPPS codeset
[CLD-v2.3.2] filter EXCHANGE in hospital plan bridge
[CLD-v2.3.2] RC to HCPCS imputation
Double check rate presence for 0464U
"""


# %%
# Double check rate presence for 0464U (2025_08 only for labs)
df = pd.read_sql(f"""
SELECT provider_type, count(*)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE billing_code = '0464U'
AND canonical_rate_score > 1
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
""", con=trino_conn)
df

# %%
# [CLD-v2.3.2] add best payer and hospital contract methodology and params
# [CLD-v2.3.2] payer hospital best rate columns
df = pd.read_sql(f"""
SELECT
    -- best_payer_contract_methodology,
    -- best_hospital_contract_methodology,
    best_payer_rate,
    best_hospital_rate,
    best_payer_method_params,
    best_hospital_method_params,
    best_payer_rate_type,
    best_hospital_rate_type
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE best_payer_method_params IS NOT NULL
AND best_hospital_method_params IS NOT NULL
LIMIT 10
""", con=trino_conn)
df

# %%
# [CLD-v2.3.2] Use payer_hospital for validated rates
df = pd.read_sql(f"""
SELECT
    canonical_rate_source,
    canonical_rate_score,
    count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
GROUP BY 1,2
""", con=trino_conn)
df

df = pd.read_sql(f"""
SELECT
    count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE canonical_rate_score = 5
AND canonical_rate_source IS NULL
""", con=trino_conn)
df


# %%
# [CLD-v2.3.2] Include all labs in core rates
df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_11
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df)

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_08
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df)

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df)

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT provider_id)
FROM tq_dev.internal_dev_csong_cld_v2_3_1.tmp_int_combined_2025_10
WHERE provider_type = 'Laboratory'
""", con=trino_conn)
print(df)


# %%
# [CLD-v2.3.2] Add new ASC codes
df = pd.read_sql(f"""
SELECT billing_code, COUNT(distinct provider_id) as n_providers
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE provider_type = 'ASC'
AND billing_code IN ('93655','93656','93657')
GROUP BY 1
""", con=trino_conn)
print(df)

# %%
# [CLD-v2.3.2] update OPPS codeset
df = pd.read_sql(f"""
SELECT COUNT(DISTINCT billing_code)
FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
WHERE bill_type = 'Outpatient'
AND provider_type = 'Hospital'
AND canonical_rate_score > 1
""", con=trino_conn)
print(df)

df = pd.read_sql(f"""
SELECT COUNT(DISTINCT billing_code)
FROM tq_dev.internal_dev_csong_cld_v2_3_1.tmp_int_combined_2025_10
WHERE bill_type = 'Outpatient'
AND provider_type = 'Hospital'
AND canonical_rate_score > 1
""", con=trino_conn)
print(df)

# %%
# [CLD-v2.3.2] filter EXCHANGE in hospital plan bridge
df = pd.read_sql(f"""
WITH
sample AS (
    SELECT hospital_rates_id
    FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_raw_mrf_hospital_rates_2025_11
    WHERE network_id IN (
        SELECT network_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_network_spine_2025_11
        WHERE network_type = 'EXCHANGE'
    )
    AND provider_id IN (
        SELECT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_2.tmp_ref_provider_spine_2025_11
        WHERE provider_type = 'Hospital'
    )
    ORDER BY RANDOM()
    LIMIT 100000
)
SELECT payer_class_name, count(*) as n
FROM tq_production.hospital_data.hospital_rates hr
WHERE hr.id IN (SELECT hospital_rates_id FROM sample)
GROUP BY 1
""", con=trino_conn)
df


# %%

Prod Features​

Non-outlier rate presence for 0464U​

Non-outlier rate presence for other guardant codes​

confirm Sam's lab request is present​

add best payer and hospital fields​

Use "payer_hospital" for validated rates​

Include all labs in core rates​

Add new ASC codes​

update OPPS codeset​

filter EXCHANGE in hospital plan bridge​

check for RC to HCPCS imputation​

Full Code​

On this page:

Prod Features

Non-outlier rate presence for 0464U

Non-outlier rate presence for other guardant codes

confirm Sam's lab request is present

add best payer and hospital fields

Use "payer_hospital" for validated rates

Include all labs in core rates

Add new ASC codes

update OPPS codeset

filter EXCHANGE in hospital plan bridge

check for RC to HCPCS imputation

Full Code