Komodo Remits

Takeaways:

1. Remits data contains billed_amount. But it is ALWAYS NULL
2. Allowed amounts are available but the number of encounters with total allowed amounts > than $50k is very small (1156). This includes ALL lines of businesses, even where payer_id is NULL.

Data Dictionary

warning

billed_amount, cob_amount, and other_amount are ALWAYS null

Column	Type
patient_id	varchar
encounter_key	varchar
remit_id	varchar
payee_npi	varchar
payer_id	varchar
payer_name	varchar
claim_from_date	date
claim_to_date	date
claim_type	varchar
bill_type_code	varchar
drg_code	varchar
line_number	integer
service_from_date	date
service_to_date	date
procedure_code	varchar
revenue_code	varchar
units	integer
billed_amount	integer
bill_date	date
contract_adjustment_amount	integer
denial_amount	integer
cob_amount	integer
other_amount	integer
patient_responsibility_amount	integer
contract_adjustment_codes	varchar
denial_codes	varchar
other_codes	varchar
allowed_amount	integer
paid_amount	integer

Billed Amount is Always NULL

	n
billed_amount_not_null	0
allowed_amount_not_null	252453685
paid_amount_not_null	50429242
cob_amount_not_null	0
other_amount_not_null	0
n	18336627986

df = pd.read_sql(f"""
SELECT
    COUNT_IF(billed_amount IS NOT NULL) AS billed_amount_not_null,
    COUNT_IF(allowed_amount IS NOT NULL) AS allowed_amount_not_null,
    COUNT_IF(paid_amount IS NOT NULL) AS paid_amount_not_null,
    COUNT_IF(cob_amount IS NOT NULL) AS cob_amount_not_null,
    COUNT_IF(other_amount IS NOT NULL) AS other_amount_not_null,
    count(*) as n
FROM tq_intermediate.external_komodo.remits
""", con=trino_conn)
print(df.T.to_markdown(floatfmt=".0f"))

Allowed Amount Distribution

	Allowed Amount
p10_allowed_amount	59.00
p25_allowed_amount	110.00
p50_allowed_amount	128.00
p75_allowed_amount	228.00
p90_allowed_amount	439.00
p95_allowed_amount	648.00
p99_allowed_amount	1550.00
p999_allowed_amount	4670.00
p9999_allowed_amount	13016.00

df = pd.read_sql(f"""
WITH
df AS (
    SELECT 
        encounter_key,
        sum(allowed_amount) AS total_allowed_amount
    FROM tq_intermediate.external_komodo.remits
    GROUP BY 1
)
SELECT
    APPROX_PERCENTILE(total_allowed_amount, 0.1) AS p10_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.25) AS p25_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.5) AS p50_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.75) AS p75_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.9) AS p90_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.95) AS p95_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.99) AS p99_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.999) AS p999_allowed_amount,
    APPROX_PERCENTILE(total_allowed_amount, 0.9999) AS p9999_allowed_amount
FROM df 
""", con=trino_conn)
print(df.set_axis(['Allowed Amount']).T.to_markdown(floatfmt=".2f"))

Encounters with Allowed Amounts > $50k

There are only 1156 encounters with total allowed amounts greater than $50,000. Note that this includes all lines of business, even where payer_id contains NULL.

WITH
df AS (
    SELECT 
        encounter_key,
        sum(allowed_amount) AS total_allowed_amount
    FROM tq_intermediate.external_komodo.remits
    GROUP BY 1
)
SELECT
    COUNT_IF(total_allowed_amount > 50000) AS encounters_above_50k
FROM df