SQL Reference
Both fee schedule scripts share the same CTE pipeline. The institutional and
professional variants differ only in their ssp_groupings, medicare, and
national_benchmarks CTEs.
SSP Groupings (prerequisite)
Enriches the raw crosswalk with RII-based tier multipliers. This table is consumed by both fee schedule scripts.
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.ssp_groupings AS
WITH
ssp_groupings AS (
SELECT
ssp_grouper,
sub_category,
code,
pos,
type
FROM tq_dev.internal_dev_csong_ssp.ssp_initial_pilot_codes_and_xwalk
WHERE ssp_grouper IS NOT NULL
),
tiers_base AS (
SELECT
ssp,
tier,
AVG(intensity_score) AS avg_intensity_score
FROM tq_dev.internal_dev_csong_ssp.rii_code_tiers
WHERE ssp_setting = 'IP'
GROUP BY 1, 2
),
tiers_z AS (
SELECT
ssp,
tier,
avg_intensity_score,
(avg_intensity_score - AVG(avg_intensity_score) OVER (PARTITION BY ssp))
/ (STDDEV_POP(avg_intensity_score) OVER (PARTITION BY ssp) + 1e-12) / 6.0 AS z_score
FROM tiers_base
),
tiers_weights AS (
SELECT
ssp,
tier,
avg_intensity_score,
EXP(z_score - MAX(z_score) OVER (PARTITION BY ssp)) AS w
FROM tiers_z
),
tiers_final AS (
SELECT
ssp,
tier,
avg_intensity_score,
w / AVG(w) OVER (PARTITION BY ssp) AS multiplier
FROM tiers_weights
)
SELECT
DISTINCT
sg.ssp_grouper,
CASE
WHEN sg.sub_category = '-'
THEN CAST(tf.tier AS VARCHAR)
ELSE sg.sub_category
END as sub_category,
sg.code,
sg.pos,
sg.type,
tf.avg_intensity_score,
tf.multiplier
FROM ssp_groupings sg
LEFT JOIN tiers_final tf
ON sg.ssp_grouper = tf.ssp
AND sg.sub_category = '-'
Ancillary Provider Types (Step 2)
Service-type classification for professional line codes. Produced by
ancillary_provider_types.py. The resulting table is consumed by
professional_line_codes.py to populate the service_type column.
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.ssp_line_code_service_types_2026_03_27 AS
WITH
cats AS (
SELECT
service_code,
service_type,
category
FROM tq_production.spines.spines_services ss
LEFT JOIN tq_production.spines.spines_services_relationships ssr
ON ss.service_id = ssr.service_id
LEFT JOIN tq_production.spines.spines_services_clinical_categories sscc
ON sscc.category_id = ssr.target_id
WHERE ssr.target_id_type = 'category_id'
),
-- All professional/optional line codes from supplemented table
sub_package_combined AS (
SELECT base_code, line_code
FROM tq_dev.internal_dev_csong_ssp.supplemented_sub_package_contents_2026_03_27
WHERE fee_type IN ('Professional Fee', 'Optional Fee')
AND item_type != 'revenue_code'
)
SELECT
DISTINCT
spc.line_code AS code,
CASE
-- 1. Anesthesia reference pricing
WHEN spc.line_code IN (
SELECT DISTINCT hcpcs
FROM tq_production.reference_internal.anesthesia_reference_pricing
) THEN 'Anesthesia'
-- 2. Clinical lab fee schedule
WHEN spc.line_code IN (
SELECT DISTINCT hcpcs
FROM tq_production.reference_external.clinical_laboratory_reference_pricing
) THEN 'Lab/Path'
-- 3. Hardcoded Lab/Path radiology codes (LABPATH_RADIOLOGY_CODES)
WHEN spc.line_code IN (/* LABPATH_RADIOLOGY_CODES list */)
THEN 'Lab/Path'
-- 4. CCS category: Anesthesia
WHEN ssc.ccs_category = 'Anesthesia'
THEN 'Anesthesia'
-- 5. CCS category: Lab/Path
WHEN ssc.ccs_category IN (
'Laboratory - Chemistry and Hematology',
'Pathology',
'Microscopic examination (bacterial smear, culture, toxicology)',
'Other Laboratory'
) THEN 'Lab/Path'
-- 6. CCS category: Radiology (explicit list)
WHEN ssc.ccs_category IN (
'Other diagnostic radiology and related techniques',
'Magnetic resonance imaging',
'Other diagnostic ultrasound',
'Therapeutic radiology',
'Diagnostic ultrasound of heart (echocardiogram)',
'Other CT scan',
'Radioisotope scan and function studies'
) THEN 'Radiology'
-- 7. CCS category: CT scan wildcard
WHEN ssc.ccs_category LIKE '%CT scan%'
THEN 'Radiology'
-- 8. Default: clinical category from spines
ELSE cats.category
END AS service_type
FROM tq_dev.internal_dev_csong_ssp.ssp_groupings_2026_03_27 g
INNER JOIN sub_package_combined spc
ON spc.base_code = g.code
LEFT JOIN cats
ON cats.service_code = spc.line_code
LEFT JOIN tq_dev.internal_dev_csong_ssp.services_spines_cleaned ssc
ON ssc.service_code = spc.line_code
AND ssc.service_type IN ('HCPCS', 'MS-DRG')
WHERE g.ssp_grouper IS NOT NULL
Institutional Fee Schedule
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.institutional_fee_schedule_2026_03_12 AS
WITH
ssp_groupings AS (
SELECT
ssp_grouper,
REPLACE(sub_category, '-', '0') AS sub_category,
code,
pos,
type,
COALESCE(multiplier, 1) as multiplier
FROM tq_dev.internal_dev_csong_ssp.ssp_groupings
WHERE ssp_grouper IS NOT NULL
),
-- Medicare Rates (IPPS): per (msdrg, provider_id)
medicare_ipps AS (
SELECT
msdrg AS billing_code,
provider_id,
AVG(total_payment) AS medicare_rate
FROM tq_production.reference_external.ipps_reference_pricing
WHERE
is_latest_start_effective_date = True
AND pricing_priority = 1
GROUP BY 1, 2
),
-- Medicare Rates (OPPS): per (apc, provider_id)
medicare_opps_apc AS (
SELECT
apc,
provider_id,
AVG(rate) AS medicare_rate
FROM tq_production.reference_external.opps_reference_pricing
WHERE
is_latest_start_effective_date = True
AND pricing_priority = 1
GROUP BY 1, 2
),
-- HCPCS-to-APC bridge: map billing_code to apc via opps_reference_pricing
hcpcs_to_apc AS (
SELECT DISTINCT
hcpcs AS billing_code,
apc
FROM tq_production.reference_external.opps_reference_pricing
WHERE
is_latest_start_effective_date = True
AND pricing_priority = 1
AND apc IS NOT NULL
),
-- Combined Medicare: IPPS (msdrg) + OPPS (hcpcs -> apc -> rate)
medicare AS (
SELECT billing_code, provider_id, medicare_rate
FROM medicare_ipps
UNION ALL
SELECT
bridge.billing_code,
opps.provider_id,
opps.medicare_rate
FROM hcpcs_to_apc bridge
INNER JOIN medicare_opps_apc opps ON bridge.apc = opps.apc
),
national_benchmarks AS (
SELECT
billing_code,
billing_code_type,
APPROX_PERCENTILE(
CASE WHEN canonical_rate_score = 5 THEN canonical_rate ELSE NULL END,
0.5
) AS canonical_rate_validated,
APPROX_PERCENTILE(
CASE WHEN canonical_rate_score > 1 THEN canonical_rate ELSE NULL END,
0.5
) AS canonical_rate_backup,
COALESCE(MAX(acl.volume), 1) AS volume
FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged br
LEFT JOIN tq_dev.internal_dev_csong_sandbox.acute_care_large acl
ON br.billing_code_type = acl.code_type
AND br.billing_code = acl.code
WHERE billing_code IN (SELECT DISTINCT code FROM ssp_groupings)
AND billing_code_type IN ('HCPCS', 'MS-DRG')
AND canonical_rate_score = 5
AND provider_type = 'Hospital'
AND network_type = 'PPO'
GROUP BY 1, 2
),
ssp_rates AS (
SELECT
g.ssp_grouper,
g.sub_category,
g.pos,
g.multiplier,
m.provider_id,
MAX_BY(g.code, n.volume) AS code,
-- Commercial: volume-weighted average
multiplier * SUM(COALESCE(n.canonical_rate_validated, n.canonical_rate_backup) * n.volume)
/ NULLIF(SUM(n.volume), 0) AS subcategory_price,
-- Medicare: rate from highest-volume code
multiplier * MAX_BY(COALESCE(m.medicare_rate, 0), n.volume) AS subcategory_medicare_price,
SUM(n.volume) AS volume
FROM ssp_groupings g
INNER JOIN national_benchmarks n ON g.code = n.billing_code
LEFT JOIN medicare m ON g.code = m.billing_code
GROUP BY 1, 2, 3, 4, 5
),
-- Roll up to ssp_grouper level: volume-weighted avg for commercial,
-- highest-volume subcategory for medicare
ssp_grouper_rates AS (
SELECT
ssp_grouper,
provider_id,
SUM(subcategory_price * volume) / NULLIF(SUM(volume), 0) AS ssp_grouper_price,
MAX_BY(subcategory_medicare_price, volume) AS ssp_grouper_medicare_price
FROM ssp_rates
GROUP BY 1, 2
)
SELECT
s.ssp_grouper,
s.sub_category,
s.pos,
s.code,
s.multiplier,
s.provider_id,
s.subcategory_price,
s.subcategory_medicare_price,
sg.ssp_grouper_price,
sg.ssp_grouper_medicare_price,
s.subcategory_price / CASE s.pos WHEN 'IP' THEN 6000 ELSE 500 END AS subcategory_weight,
s.subcategory_medicare_price / CASE s.pos WHEN 'IP' THEN 6000 ELSE 500 END AS subcategory_medicare_weight,
sg.ssp_grouper_price / CASE s.pos WHEN 'IP' THEN 6000 ELSE 500 END AS ssp_grouper_weight,
sg.ssp_grouper_medicare_price / CASE s.pos WHEN 'IP' THEN 6000 ELSE 500 END AS ssp_grouper_medicare_weight
FROM ssp_rates s
JOIN ssp_grouper_rates sg
ON s.ssp_grouper = sg.ssp_grouper AND s.provider_id = sg.provider_id
ORDER BY s.ssp_grouper, s.sub_category, s.provider_id
Professional Line Codes (Step 1)
Line-code level table with benchmark and Medicare rates. Produced by
professional_line_codes.py.
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.professional_line_codes_2026_03_27 AS
WITH
-- Line codes to exclude (e.g. venipuncture)
excluded_line_codes AS (
SELECT code FROM tq_dev.internal_dev_csong_ssp.excluded_line_codes_2026_03_27
),
ssp_groupings AS (
SELECT
g.ssp_grouper,
REPLACE(g.sub_category, '-', '0') AS sub_category,
spc.line_code AS code,
g.code AS base_code,
g.pos,
g.type,
spc.fee_type,
spc.association_rate,
CASE
WHEN spc.line_code IN (SELECT code FROM tq_dev.internal_dev_csong_ssp.ssp_groupings_2026_03_27 WHERE ssp_grouper = g.ssp_grouper) THEN 1
WHEN st.service_type = 'Anesthesia' THEN GREATEST(COALESCE(NULLIF(spc.average_units, 0), 15) / 15.0, 1)
ELSE GREATEST(COALESCE(NULLIF(spc.average_units, 0), 1), 1)
END AS units
FROM tq_dev.internal_dev_csong_ssp.ssp_groupings_2026_03_27 g
INNER JOIN tq_dev.internal_dev_csong_ssp.supplemented_sub_package_contents_2026_03_27 spc
ON spc.base_code = g.code
LEFT JOIN tq_dev.internal_dev_csong_ssp.ssp_line_code_service_types_2026_03_27 st
ON st.code = spc.line_code
WHERE g.ssp_grouper IS NOT NULL
AND g.type IN ('CPT','HCPCS','MS-DRG')
AND spc.fee_type IN ('Professional Fee', 'Optional Fee')
AND spc.item_type != 'revenue_code'
AND spc.line_code NOT IN (SELECT code FROM excluded_line_codes)
-- Keep only codes common enough: NULL means no claims data (always keep),
-- otherwise require association_rate > 0.3 (ENCOUNTER_THRESHOLD)
AND (spc.association_rate IS NULL OR spc.association_rate > 0.3)
),
-- Medicare Rates (physician): state-level averages from MPFS
medicare_by_state AS (
SELECT
hcpcs AS billing_code,
state_code,
AVG(non_facility_rate) AS medicare_rate
FROM tq_production.reference_external.physician_reference_pricing
WHERE is_latest_start_effective_date = True
GROUP BY 1, 2
),
-- Medicare Rates (anesthesia): state-level from anesthesia fee schedule
medicare_anesthesia_by_state AS (
SELECT
hcpcs AS billing_code,
state_code,
AVG(rate) AS medicare_rate
FROM tq_production.reference_internal.anesthesia_reference_pricing
WHERE is_latest_start_effective_date = True
GROUP BY 1, 2
),
-- Medicare Rates (clinical lab): national CLFS fallback for codes not in MPFS or anesthesia
medicare_clfs_national AS (
SELECT
hcpcs AS billing_code,
AVG(rate) AS medicare_rate
FROM tq_production.reference_external.clinical_laboratory_reference_pricing
WHERE code_indicator = 'National'
AND is_latest_start_effective_date = True
GROUP BY 1
),
provider_state AS (
SELECT provider_id, provider_state
FROM tq_production.spines.spines_provider_hospitals
),
-- Medicare joined to provider_id via state
-- Priority: MPFS > Anesthesia fee schedule > CLFS (national fallback)
medicare AS (
SELECT ms.billing_code, ps.provider_id, ms.medicare_rate
FROM medicare_by_state ms
INNER JOIN provider_state ps ON ms.state_code = ps.provider_state
UNION ALL
SELECT ma.billing_code, ps.provider_id, ma.medicare_rate
FROM medicare_anesthesia_by_state ma
INNER JOIN provider_state ps ON ma.state_code = ps.provider_state
WHERE ma.billing_code NOT IN (SELECT DISTINCT billing_code FROM medicare_by_state)
UNION ALL
SELECT cn.billing_code, ps.provider_id, cn.medicare_rate
FROM medicare_clfs_national cn
CROSS JOIN provider_state ps
WHERE cn.billing_code NOT IN (SELECT DISTINCT billing_code FROM medicare_by_state)
AND cn.billing_code NOT IN (SELECT DISTINCT billing_code FROM medicare_anesthesia_by_state)
),
national_benchmarks AS (
SELECT
billing_code,
billing_code_type,
APPROX_PERCENTILE(
CASE WHEN canonical_rate_score = 5 THEN canonical_rate ELSE NULL END,
0.5
) AS canonical_rate_validated,
APPROX_PERCENTILE(
CASE WHEN canonical_rate_score > 1 THEN canonical_rate ELSE NULL END,
0.5
) AS canonical_rate_backup,
COUNT(CASE WHEN canonical_rate_score = 5 THEN 1 END) AS n_rates_validated,
COUNT(CASE WHEN canonical_rate_score > 1 THEN 1 END) AS n_rates_backup,
COALESCE(MAX(acl.volume), 1) AS volume
FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged br
LEFT JOIN tq_dev.internal_dev_csong_sandbox.acute_care_large acl
ON br.billing_code_type = acl.code_type
AND br.billing_code = acl.code
WHERE billing_code IN (SELECT DISTINCT code FROM ssp_groupings)
AND billing_code_type = 'HCPCS'
AND canonical_rate_score >= 4
AND bill_type = 'Professional'
AND provider_type = 'Physician Group'
AND network_type = 'PPO'
GROUP BY 1, 2
)
SELECT
g.ssp_grouper,
g.sub_category,
g.pos,
g.base_code,
g.code AS line_code,
g.fee_type,
m.provider_id,
-- Units (anesthesia scaled by average_units/15, others = 1)
g.units,
-- Commercial benchmark (multiplied by units)
COALESCE(n.canonical_rate_validated, n.canonical_rate_backup) * g.units AS canonical_rate,
n.canonical_rate_validated,
n.canonical_rate_backup,
n.n_rates_validated,
n.n_rates_backup,
n.volume,
-- Medicare (multiplied by units)
COALESCE(m.medicare_rate, 0) * g.units AS medicare_rate,
CASE WHEN n.canonical_rate_validated IS NOT NULL THEN 'validated'
ELSE 'backup' END AS rate_source,
-- Association rate: relative encounter proportion from claims discovery
-- (NULL for codes from sub_package_contents with no claims data)
g.association_rate,
-- Service type from ssp_line_code_service_types
CASE
WHEN st.service_type IN ('Anesthesia', 'Lab/Path', 'Radiology') THEN st.service_type
ELSE 'Professional'
END AS service_type,
-- Description and CCS category (from services_spines_cleaned)
ssc.service_description AS line_code_description,
ssc.shorthand AS line_code_shorthand,
ssc.ccs_category
FROM ssp_groupings g
LEFT JOIN national_benchmarks n ON g.code = n.billing_code
LEFT JOIN medicare m ON g.code = m.billing_code
LEFT JOIN tq_dev.internal_dev_csong_ssp.ssp_line_code_service_types_2026_03_27 st ON g.code = st.code
LEFT JOIN tq_dev.internal_dev_csong_ssp.services_spines_cleaned ssc
ON g.code = ssc.service_code AND ssc.service_type IN ('HCPCS', 'MS-DRG')
ORDER BY g.ssp_grouper, g.sub_category, g.base_code, g.fee_type, g.code, m.provider_id
Professional Fee Schedule (Step 2)
Aggregates professional_line_codes to fee schedule level. Produced by
with_professional_fee_schedule.py.
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.professional_fee_schedule_2026_03_12 AS
WITH
base_code_rates AS (
SELECT
ssp_grouper,
sub_category,
pos,
base_code,
provider_id,
MAX(volume) AS volume,
SUM(canonical_rate) AS base_code_price,
SUM(medicare_rate) AS base_code_medicare_price,
SUM(CASE WHEN fee_type = 'Professional Fee' THEN canonical_rate END) AS base_code_professional_price,
SUM(CASE WHEN fee_type = 'Professional Fee' THEN medicare_rate END) AS base_code_professional_medicare_price,
MAX_BY(rate_source, volume) AS rate_source
FROM tq_dev.internal_dev_csong_ssp.professional_line_codes_2026_03_12
GROUP BY 1, 2, 3, 4, 5
),
ssp_rates AS (
SELECT
ssp_grouper,
sub_category,
pos,
provider_id,
MAX(base_code) AS code,
SUM(base_code_price * volume) / NULLIF(SUM(volume), 0) AS subcategory_price,
MAX_BY(base_code_medicare_price, volume) AS subcategory_medicare_price,
SUM(volume) AS volume,
SUM(base_code_professional_price * volume)
/ NULLIF(SUM(CASE WHEN base_code_professional_price IS NOT NULL THEN volume END), 0) AS subcategory_professional_price,
MAX_BY(base_code_professional_medicare_price,
CASE WHEN base_code_professional_price IS NOT NULL THEN volume END) AS subcategory_professional_medicare_price,
SUM(CASE WHEN base_code_professional_price IS NOT NULL THEN volume END) AS professional_volume
FROM base_code_rates
GROUP BY 1, 2, 3, 4
),
ssp_grouper_rates AS (
SELECT
ssp_grouper,
provider_id,
SUM(subcategory_price * volume) / NULLIF(SUM(volume), 0) AS ssp_grouper_price,
MAX_BY(subcategory_medicare_price, volume) AS ssp_grouper_medicare_price,
SUM(subcategory_professional_price * professional_volume)
/ NULLIF(SUM(professional_volume), 0) AS ssp_grouper_professional_price,
MAX_BY(subcategory_professional_medicare_price, professional_volume) AS ssp_grouper_professional_medicare_price
FROM ssp_rates
GROUP BY 1, 2
)
SELECT
s.ssp_grouper,
s.sub_category,
s.pos,
s.code,
s.provider_id,
s.subcategory_price,
s.subcategory_medicare_price,
sg.ssp_grouper_price,
sg.ssp_grouper_medicare_price,
s.subcategory_professional_price,
s.subcategory_professional_medicare_price,
sg.ssp_grouper_professional_price,
sg.ssp_grouper_professional_medicare_price
FROM ssp_rates s
JOIN ssp_grouper_rates sg
ON s.ssp_grouper = sg.ssp_grouper AND s.provider_id = sg.provider_id
ORDER BY s.ssp_grouper, s.sub_category, s.provider_id
prices.py
The two fee schedule tables are joined on (ssp_grouper, sub_category, pos, provider_id) and
output at two granularities.
Sub-category level
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.combined_subcategory_fee_schedule_2026_03_12 AS
SELECT
inst.ssp_grouper,
inst.sub_category,
inst.pos,
inst.provider_id,
-- Institutional
inst.subcategory_price AS inst_price,
inst.subcategory_medicare_price AS inst_medicare_price,
inst.subcategory_price / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS inst_weight,
inst.subcategory_medicare_price / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS inst_medicare_weight,
-- Professional (inst + Professional Fee only)
inst.subcategory_price + prof.subcategory_professional_price AS prof_price,
inst.subcategory_medicare_price + prof.subcategory_professional_medicare_price AS prof_medicare_price,
(inst.subcategory_price + prof.subcategory_professional_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_weight,
(inst.subcategory_medicare_price + prof.subcategory_professional_medicare_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_medicare_weight,
-- Professional (inst + Professional + Optional)
inst.subcategory_price + prof.subcategory_price AS prof_with_optional_price,
inst.subcategory_medicare_price + prof.subcategory_medicare_price AS prof_with_optional_medicare_price,
(inst.subcategory_price + prof.subcategory_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_with_optional_weight,
(inst.subcategory_medicare_price + prof.subcategory_medicare_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_with_optional_medicare_weight
FROM tq_dev.internal_dev_csong_ssp.institutional_fee_schedule_2026_03_12 inst
LEFT JOIN tq_dev.internal_dev_csong_ssp.professional_fee_schedule_2026_03_12 prof
ON inst.ssp_grouper = prof.ssp_grouper
AND inst.sub_category = prof.sub_category
AND inst.pos = prof.pos
AND inst.provider_id = prof.provider_id
ORDER BY inst.ssp_grouper, inst.sub_category, inst.provider_id
SSP level
ANY_VALUE is safe because ssp_grouper_* values are identical across all
sub-categories within a grouper (for a given provider).
CREATE OR REPLACE TABLE tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12 AS
WITH
sub_packages AS (
SELECT DISTINCT base_code, sub_package_total_billed_count
FROM tq_production.standard_service_packages.sub_packages
)
SELECT
inst.ssp_grouper,
inst.pos,
inst.provider_id,
-- Institutional
ANY_VALUE(inst.ssp_grouper_price) AS inst_price,
ANY_VALUE(inst.ssp_grouper_medicare_price) AS inst_medicare_price,
ANY_VALUE(inst.ssp_grouper_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS inst_weight,
ANY_VALUE(inst.ssp_grouper_medicare_price) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS inst_medicare_weight,
-- Professional (inst + Professional Fee only)
ANY_VALUE(inst.ssp_grouper_price) + ANY_VALUE(prof.ssp_grouper_professional_price) AS prof_price,
ANY_VALUE(inst.ssp_grouper_medicare_price) + ANY_VALUE(prof.ssp_grouper_professional_medicare_price) AS prof_medicare_price,
(ANY_VALUE(inst.ssp_grouper_price) + ANY_VALUE(prof.ssp_grouper_professional_price)) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_weight,
(ANY_VALUE(inst.ssp_grouper_medicare_price) + ANY_VALUE(prof.ssp_grouper_professional_medicare_price)) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_medicare_weight,
-- Professional (inst + Professional + Optional)
ANY_VALUE(inst.ssp_grouper_price) + ANY_VALUE(prof.ssp_grouper_price) AS prof_with_optional_price,
ANY_VALUE(inst.ssp_grouper_medicare_price) + ANY_VALUE(prof.ssp_grouper_medicare_price) AS prof_with_optional_medicare_price,
(ANY_VALUE(inst.ssp_grouper_price) + ANY_VALUE(prof.ssp_grouper_price)) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_with_optional_weight,
(ANY_VALUE(inst.ssp_grouper_medicare_price) + ANY_VALUE(prof.ssp_grouper_medicare_price)) / CASE inst.pos WHEN 'IP' THEN 6000 ELSE 500 END AS prof_with_optional_medicare_weight,
-- Claim count from sub_packages
SUM(sp.sub_package_total_billed_count) AS total_claim_count
FROM tq_dev.internal_dev_csong_ssp.institutional_fee_schedule_2026_03_12 inst
LEFT JOIN tq_dev.internal_dev_csong_ssp.professional_fee_schedule_2026_03_12 prof
ON inst.ssp_grouper = prof.ssp_grouper
AND inst.pos = prof.pos
AND inst.provider_id = prof.provider_id
LEFT JOIN sub_packages sp ON inst.code = sp.base_code
GROUP BY inst.ssp_grouper, inst.pos, inst.provider_id
ORDER BY inst.ssp_grouper, inst.provider_id
Combo SSPs
Combines two existing SSPs using multiple procedure logic (100% primary + 50%
secondary). Primary/secondary is determined per provider by inst_price.
Produced by combo_ssps.py.
Insert into combined_ssp_fee_schedule
-- Delete existing rows for the combo SSP first
DELETE FROM tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12
WHERE ssp_grouper = 'GA.2.colonoscopy_and_egd';
INSERT INTO tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12
WITH
ssp_a AS (
SELECT * FROM tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12
WHERE ssp_grouper = 'GA.0.colonoscopy' AND pos = 'OP'
),
ssp_b AS (
SELECT * FROM tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12
WHERE ssp_grouper = 'GA.0.egd' AND pos = 'OP'
),
combined AS (
SELECT
a.provider_id,
-- Determine primary (100%) and secondary (50%) by inst_price
CASE WHEN a.inst_price >= b.inst_price THEN a.inst_price ELSE b.inst_price END AS primary_inst,
CASE WHEN a.inst_price >= b.inst_price THEN b.inst_price ELSE a.inst_price END AS secondary_inst,
CASE WHEN a.inst_price >= b.inst_price THEN a.inst_medicare_price ELSE b.inst_medicare_price END AS primary_inst_med,
CASE WHEN a.inst_price >= b.inst_price THEN b.inst_medicare_price ELSE a.inst_medicare_price END AS secondary_inst_med,
CASE WHEN a.inst_price >= b.inst_price THEN a.prof_price ELSE b.prof_price END AS primary_prof,
CASE WHEN a.inst_price >= b.inst_price THEN b.prof_price ELSE a.prof_price END AS secondary_prof,
CASE WHEN a.inst_price >= b.inst_price THEN a.prof_medicare_price ELSE b.prof_medicare_price END AS primary_prof_med,
CASE WHEN a.inst_price >= b.inst_price THEN b.prof_medicare_price ELSE a.prof_medicare_price END AS secondary_prof_med,
CASE WHEN a.inst_price >= b.inst_price THEN a.anes_price ELSE b.anes_price END AS primary_anes,
CASE WHEN a.inst_price >= b.inst_price THEN b.anes_price ELSE a.anes_price END AS secondary_anes,
CASE WHEN a.inst_price >= b.inst_price THEN a.anes_medicare_price ELSE b.anes_medicare_price END AS primary_anes_med,
CASE WHEN a.inst_price >= b.inst_price THEN b.anes_medicare_price ELSE a.anes_medicare_price END AS secondary_anes_med,
CASE WHEN a.inst_price >= b.inst_price THEN a.labpath_price ELSE b.labpath_price END AS primary_labpath,
CASE WHEN a.inst_price >= b.inst_price THEN b.labpath_price ELSE a.labpath_price END AS secondary_labpath,
CASE WHEN a.inst_price >= b.inst_price THEN a.labpath_medicare_price ELSE b.labpath_medicare_price END AS primary_labpath_med,
CASE WHEN a.inst_price >= b.inst_price THEN b.labpath_medicare_price ELSE a.labpath_medicare_price END AS secondary_labpath_med,
CASE WHEN a.inst_price >= b.inst_price THEN a.radiology_price ELSE b.radiology_price END AS primary_radiology,
CASE WHEN a.inst_price >= b.inst_price THEN b.radiology_price ELSE a.radiology_price END AS secondary_radiology,
CASE WHEN a.inst_price >= b.inst_price THEN a.radiology_medicare_price ELSE b.radiology_medicare_price END AS primary_radiology_med,
CASE WHEN a.inst_price >= b.inst_price THEN b.radiology_medicare_price ELSE a.radiology_medicare_price END AS secondary_radiology_med
FROM ssp_a a
INNER JOIN ssp_b b ON a.provider_id = b.provider_id
)
SELECT
'GA.2.colonoscopy_and_egd' AS ssp_grouper,
'OP' AS pos,
provider_id,
CAST(NULL AS varchar) AS medicare_apc,
-- Institutional: 100% primary + 50% secondary
primary_inst + 0.5 * COALESCE(secondary_inst, 0) AS inst_price,
primary_inst_med + 0.5 * COALESCE(secondary_inst_med, 0) AS inst_medicare_price,
(primary_inst + 0.5 * COALESCE(secondary_inst, 0)) / 500 AS inst_weight,
(primary_inst_med + 0.5 * COALESCE(secondary_inst_med, 0)) / 500 AS inst_medicare_weight,
-- Professional: 100% primary + 50% secondary
COALESCE(primary_prof, 0) + 0.5 * COALESCE(secondary_prof, 0) AS prof_price,
COALESCE(primary_prof_med, 0) + 0.5 * COALESCE(secondary_prof_med, 0) AS prof_medicare_price,
(COALESCE(primary_prof, 0) + 0.5 * COALESCE(secondary_prof, 0)) / 500 AS prof_weight,
(COALESCE(primary_prof_med, 0) + 0.5 * COALESCE(secondary_prof_med, 0)) / 500 AS prof_medicare_weight,
-- Anesthesia: 100% primary + 50% secondary
COALESCE(primary_anes, 0) + 0.5 * COALESCE(secondary_anes, 0) AS anes_price,
COALESCE(primary_anes_med, 0) + 0.5 * COALESCE(secondary_anes_med, 0) AS anes_medicare_price,
(COALESCE(primary_anes, 0) + 0.5 * COALESCE(secondary_anes, 0)) / 500 AS anes_weight,
(COALESCE(primary_anes_med, 0) + 0.5 * COALESCE(secondary_anes_med, 0)) / 500 AS anes_medicare_weight,
-- Lab/Path: 100% primary + 50% secondary
COALESCE(primary_labpath, 0) + 0.5 * COALESCE(secondary_labpath, 0) AS labpath_price,
COALESCE(primary_labpath_med, 0) + 0.5 * COALESCE(secondary_labpath_med, 0) AS labpath_medicare_price,
(COALESCE(primary_labpath, 0) + 0.5 * COALESCE(secondary_labpath, 0)) / 500 AS labpath_weight,
(COALESCE(primary_labpath_med, 0) + 0.5 * COALESCE(secondary_labpath_med, 0)) / 500 AS labpath_medicare_weight,
-- Radiology: 100% primary + 50% secondary
COALESCE(primary_radiology, 0) + 0.5 * COALESCE(secondary_radiology, 0) AS radiology_price,
COALESCE(primary_radiology_med, 0) + 0.5 * COALESCE(secondary_radiology_med, 0) AS radiology_medicare_price,
(COALESCE(primary_radiology, 0) + 0.5 * COALESCE(secondary_radiology, 0)) / 500 AS radiology_weight,
(COALESCE(primary_radiology_med, 0) + 0.5 * COALESCE(secondary_radiology_med, 0)) / 500 AS radiology_medicare_weight
FROM combined
Insert into combined_subcategory_fee_schedule
DELETE FROM tq_dev.internal_dev_csong_ssp.combined_subcategory_fee_schedule_2026_03_12
WHERE ssp_grouper = 'GA.2.colonoscopy_and_egd';
INSERT INTO tq_dev.internal_dev_csong_ssp.combined_subcategory_fee_schedule_2026_03_12
SELECT
ssp_grouper,
'0' AS sub_category,
pos,
provider_id,
medicare_apc,
inst_price, inst_medicare_price, inst_weight, inst_medicare_weight,
prof_price, prof_medicare_price, prof_weight, prof_medicare_weight,
anes_price, anes_medicare_price, anes_weight, anes_medicare_weight,
labpath_price, labpath_medicare_price, labpath_weight, labpath_medicare_weight,
radiology_price, radiology_medicare_price, radiology_weight, radiology_medicare_weight
FROM tq_dev.internal_dev_csong_ssp.combined_ssp_fee_schedule_2026_03_12
WHERE ssp_grouper = 'GA.2.colonoscopy_and_egd'