Example SQL
These examples use the published Tuva Core v1.0.0 schema and the corresponding standalone package v1.0.0 schemas. Install Core and each package needed for a section using their Git tags in the consuming root project; see Getting Started. Quality Measures, AHRQ Quality Indicators, CMS Chronic Conditions, CMS HCC, NYU ED Classification, and CCSR are optional installations.
The SQL dialect is DuckDB: functions such as strftime, datediff, left, unpivot, and limit require adaptation on other warehouses. Replace the example schema names if your connector uses a prefix or custom schemas. For CCSR, replace <ccsr_schema> with the schema configured by your root project. These are analytical starting points, not evidence that every query has run on every supported warehouse.
The encounter examples select claims-derived rows. If you analyze clinical encounters, define a separate cohort rather than combining clinical and claims representations of the same visit. Preserve data_source in person, encounter, practitioner, location, and claim joins. Counts across sources represent source-specific records, not deduplicated people.
core.member_month, core.cost, and core.utilization share member_month_id. Each row is an enrollment member month with payer, plan, member, and source dimensions, not necessarily one unique person-month. Medical and pharmacy claims expose their assigned member_month_id; use it when attaching enrollment instead of joining only on person and month. Apply the same analysis period and enrollment cohort to numerators and denominators. A null ratio below means its denominator is zero or unavailable.
Acute Inpatient
The acute inpatient care setting is one of the biggest drivers of health care expenditure and as a result a primary target for research and analysis.
Acute Inpatient Visits
Here we show a variety of different ways to analyze the total number of acute inpatient visits.
Total Number of Acute IP Visits
select count(1)
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
Total Number of Acute IP Visits by Month
select
strftime(encounter_end_date, '%Y%m') as year_month
, count(1) as count
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1
order by 1
Total Number of Acute IP Visits by Admit Type
select
admit_type_code
, admit_type_description
, count(1) as count
, cast(100.0 * count(*)/sum(count(*)) over() as numeric(38,1)) as percent
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1,2
order by 1,2
Total Number of Acute IP Visits by Discharge Disposition
select
discharge_disposition_code
, discharge_disposition_description
, count(1) as count
, cast(100.0 * count(*)/sum(count(*)) over() as numeric(38,1)) as percent
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1,2
order by 1,2
Total Number of Acute IP Visits by DRG
select
drg_code
, drg_description
, drg_code_type
, count(1) as count
, cast(100.0 * count(*)/sum(count(*)) over() as numeric(38,1)) as percent
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1,2,3
order by 5 desc
Total Number of Acute IP Visits by Facility
select
facility_npi
, facility_name
, facility_type
, count(1) as count
, cast(100.0 * count(*)/sum(count(*)) over() as numeric(38,1)) as percent
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1,2,3
order by 5 desc
Acute Inpatient Visits PKPY
If you have claims data, and specifically eligibility and enrollment data, you can calculate acute inpatient visits per 1,000 members per year (PKPY). This metric divides visits for people enrolled in the encounter month by enrollment member months, then annualizes per 1,000. Multiple payer/plan enrollments remain separate denominator rows; use a deduplicated person-month denominator if that is your intended population metric.
Total Number of Acute IP Visits PKPY
with enrollment as (
select data_source, year_month, count(*) as member_months
from core.member_month
group by data_source, year_month
), eligible_people as (
select distinct data_source, person_id, year_month from core.member_month
), inpatient as (
select e.data_source, strftime(e.encounter_end_date, '%Y%m') as year_month,
count(*) as inpatient_visits
from core.encounter e
join eligible_people m
on e.person_id = m.person_id and e.data_source = m.data_source
and strftime(e.encounter_end_date, '%Y%m') = m.year_month
where e.encounter_type = 'acute inpatient' and e.encounter_source_type = 'claim'
group by e.data_source, strftime(e.encounter_end_date, '%Y%m')
)
select m.data_source, m.year_month, m.member_months,
coalesce(i.inpatient_visits, 0) as inpatient_visits,
12000.0 * coalesce(i.inpatient_visits, 0) / nullif(m.member_months, 0) as visits_pkpy
from enrollment m
left join inpatient i on m.data_source = i.data_source and m.year_month = i.year_month
order by m.data_source, m.year_month;
Acute Inpatient Days PKPY
Besides looking at the total number of visits normalized for eligibility, it's common to analyze the number of acute inpatient days per 1,000 members per year (PKPY).
Trending Visits, Length of Stay, and Total Cost
with enrollment as (
select data_source, year_month, count(*) as member_months
from core.member_month
group by data_source, year_month
), eligible_people as (
select distinct data_source, person_id, year_month from core.member_month
), inpatient as (
select e.data_source, strftime(e.encounter_end_date, '%Y%m') as year_month,
sum(e.length_of_stay) as inpatient_days
from core.encounter e
join eligible_people m
on e.person_id = m.person_id and e.data_source = m.data_source
and strftime(e.encounter_end_date, '%Y%m') = m.year_month
where e.encounter_type = 'acute inpatient' and e.encounter_source_type = 'claim'
group by e.data_source, strftime(e.encounter_end_date, '%Y%m')
)
select m.data_source, m.year_month, m.member_months,
coalesce(i.inpatient_days, 0) as inpatient_days,
12000.0 * coalesce(i.inpatient_days, 0) / nullif(m.member_months, 0) as days_pkpy
from enrollment m
left join inpatient i on m.data_source = i.data_source and m.year_month = i.year_month
order by m.data_source, m.year_month;
Paid and Allowed Amounts
If you have claims data, you can calculate the paid and allowed amounts spent on acute inpatient visits. Because the encounter grouper in Encounter Grouper groups multiple claims into distinct visits, this allows you to analyze the paid and allowed amounts per visit, as opposed to per claim.
Total Paid and Allowed Amounts
select
sum(paid_amount) as paid_amount
, sum(allowed_amount) as allowed_amount
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
Total Paid and Allowed Amounts by Month
select
strftime(encounter_end_date, '%Y%m') as year_month
, sum(paid_amount) as paid_amount
, sum(allowed_amount) as allowed_amount
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1
order by 1
Length of Stay
Length of stay is computed as the difference between discharge date and admission date and typically reported as an average.
Average Length of Stay by Month
select
strftime(encounter_end_date, '%Y%m') as year_month
, avg(length_of_stay) as alos
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
group by 1
order by 1
Mortality
Mortality is computed by counting the number of discharges with a discharge disposition = 20 (the numerator) and dividing this number by the total number of acute inpatient visits (the denominator). It's important to exclude patients that have not been discharged or for which a discharge disposition is not available.
Mortality Rate by Month
with mortality_flag as (
select
data_source
, strftime(encounter_end_date, '%Y%m') as year_month
, case
when discharge_disposition_code = '20' then 1
else 0
end mortality_flag
from core.encounter
where encounter_type = 'acute inpatient'
and encounter_source_type = 'claim'
and discharge_disposition_code is not null
and encounter_end_date is not null
)
select
data_source
, year_month
, count(1) as acute_inpatient_visits
, sum(mortality_flag) as mortality_count
, 1.0 * sum(mortality_flag) / nullif(count(1), 0) as mortality_rate
from mortality_flag
group by 1,2
order by 1,2
Readmissions
The unadjusted 30-day readmission rate below uses the CMS-inspired readmissions methodology which is computed in the Quality Measures data mart.
30-day Readmission Rate by Month
with readmit as
(
select
strftime(discharge_date, '%Y%m') as year_month
, sum(case when index_admission_flag = 1 then 1 else 0 end) as index_admissions
, sum(case when index_admission_flag = 1 and unplanned_readmit_30_flag = 1 then 1 else 0 end) as readmissions
from quality_measures.readmission_summary
group by strftime(discharge_date, '%Y%m')
)
select
year_month
,index_admissions
,readmissions
,1.0 * readmissions / nullif(index_admissions, 0) as readmission_rate
from readmit
order by year_month
30-day Readmission Rate by MS-DRG
with readmit as
(
select
drg_code
, sum(case when index_admission_flag = 1 then 1 else 0 end) as index_admissions
, sum(case when index_admission_flag = 1 and unplanned_readmit_30_flag = 1 then 1 else 0 end) as readmissions
from quality_measures.readmission_summary
group by 1
)
select
drg_code
, index_admissions
, readmissions
, 1.0 * readmissions / nullif(index_admissions, 0) as readmission_rate
from readmit
order by index_admissions desc
Readmissions Data Quality
The package readmissions implementation excludes certain encounters from the calculation if they are missing certain fields. Here we break these down to show the different reasons encounters were excluded. encounter_augmented is a package implementation table, so pin the package version for queries against these flags; it is not a stable Core Data Quality relation.
Disqualified Encounters
Let's find how many encounters were disqualified.
select count(*) encounter_count
from quality_measures.encounter_augmented
where disqualified_encounter_flag = 1
Disqualification Reason
We can see the reason(s) why an encounter was disqualified by unpivoting the disqualification reason column.
with disqualified_unpivot as (
select data_source, encounter_id
, disqualified_reason
, flagvalue
from quality_measures.encounter_augmented
unpivot(
flagvalue for disqualified_reason in (
invalid_discharge_disposition_code_flag
, invalid_drg_flag
, invalid_primary_diagnosis_code_flag
, missing_admit_date_flag
, missing_discharge_date_flag
, admit_after_discharge_flag
, missing_discharge_disposition_code_flag
, missing_drg_flag
, missing_primary_diagnosis_flag
, no_diagnosis_ccs_flag
, overlaps_with_another_encounter_flag
)
) as unpvt
)
select data_source, encounter_id
, disqualified_reason
, row_number () over (partition by data_source, encounter_id order by disqualified_reason) as disqualification_number
from disqualified_unpivot
where flagvalue = 1
Discharge Location
Based on the discharge disposition field, it is often helpful to group these into the most common locations for analysis.
Discharge Location
select case when discharge_disposition_code = '01' then 'Home'
when discharge_disposition_code = '03' then 'SNF'
when discharge_disposition_code = '06' then 'Home Health'
when discharge_disposition_code = '62' then 'Inpatient Rehab'
when discharge_disposition_code = '20' then 'Expired'
else 'Other'
end as discharge_location
,count(*) as encounters
,cast(sum(paid_amount) as decimal(18,2)) as total_paid_amount
,cast(sum(paid_amount)/ nullif(count(*), 0) as decimal(18,2)) as paid_per_encounter
from core.encounter
where encounter_source_type = 'claim'
group by
case when discharge_disposition_code = '01' then 'Home'
when discharge_disposition_code = '03' then 'SNF'
when discharge_disposition_code = '06' then 'Home Health'
when discharge_disposition_code = '62' then 'Inpatient Rehab'
when discharge_disposition_code = '20' then 'Expired'
else 'Other'
end
order by count(*) desc
AHRQ PQIs
The Agency for Healthcare Research and Quality (AHRQ) develops and maintains various measures to assess the quality, safety, and effectiveness of healthcare services. These measures include the Prevention Quality Indicators (PQIs), Inpatient Quality Indicators (IQIs), Patient Safety Indicators (PSIs), and Pediatric Quality Indicators (PDIs). They are used by healthcare providers, policymakers, and researchers to identify issues, monitor progress, and compare performance to improve patient outcomes and reduce costs.
The Prevention Quality Indicators (PQIs) are a set of measures developed by AHRQ that focus on ambulatory care-sensitive conditions, which are health issues that can often be effectively managed or prevented through timely and appropriate primary care interventions.
PQIs Summary
To summarize and view the various location of encounters that qualify for each PQI measure, we can start with the summary table below:
Summary Encounters
select *
from ahrq_quality_indicators.pqi_summary
Summary by Name and Description
We can aggregate across years and join in the name and description of each measure.
select p.data_source
, p.pqi_number
, m.pqi_name
, m.pqi_description
, sum(num_count) as pqi_encounters
from ahrq_quality_indicators.pqi_rate p
left join value_sets._value_set_pqi_measures m on p.pqi_number = m.pqi_number
group by
p.data_source
, p.pqi_number
, m.pqi_name
, m.pqi_description
order by pqi_encounters desc
Summary by Facility
To view the number of PQIs at each facility in our claims dataset, we can group the summary table by facility.
select p.data_source
, p.facility_npi
, l.name
, count(*) as pqi_encounters_count
from ahrq_quality_indicators.pqi_summary p
left join core.location l on p.facility_npi = l.location_id
and p.data_source = l.data_source
group by 1,2,3
order by 3 desc
PQIs by Rate
When calculated as a rate, PQIs are typically calculated per 100,000 population in a metropolitan area or county. When used on a claims dataset, it can be helpful to view the rates per 100,000 members instead. The numerator and denominator for each measure and year is precalculated as shown below.
Rate
select *
from ahrq_quality_indicators.pqi_rate
Aggregate by Rate
If you would like to aggregate the rate to a different level, we can use the numerator and denominator tables and calculate the rate.
with num as (
select
data_source
, year_number
, pqi_number
, count(encounter_id) as num_count
from ahrq_quality_indicators.pqi_num_long
group by
data_source
, year_number
, pqi_number
)
, denom as (
select
data_source
, year_number
, pqi_number
, count(person_id) as denom_count
from ahrq_quality_indicators.pqi_denom_long
group by
data_source
, year_number
, pqi_number
)
select
d.data_source
, d.year_number
, d.pqi_number
, d.denom_count
, coalesce(num.num_count, 0) as num_count
, 100000.0 * coalesce(num.num_count, 0) / nullif(d.denom_count, 0) as rate_per_100_thousand
from denom as d
left join num
on d.pqi_number = num.pqi_number
and d.year_number = num.year_number
and d.data_source = num.data_source
order by d.data_source
, d.year_number
, d.pqi_number
Exclusions
Each of the PQI measures has a list of codes that exclude a encounter from a the measure. These codes are summarized in value sets which can be queried as well.
Exclusion Value Sets
To view the list of value sets that are excluded in each of the measures, we can query the value set table.
select distinct value_set_name
, pqi_number
from value_sets._value_set_pqi
order by pqi_number
Exclusions by PQI Number
To summarize the number of encounters excluded by each measure, use the code below. Note that if in encounter was excluded in this logic it does not necessarily mean that it would have been in the numerator, just that it is excluded regardless of whether or not the encounter qualified for each measure.
select data_source
, pqi_number
, count(distinct encounter_id) as excluded_encounters
from ahrq_quality_indicators.pqi_exclusion_long
group by data_source
, pqi_number
order by pqi_number
Chronic Conditions
The examples use distinct observed condition labels per (person_id, data_source) across the available package output. They do not implement a new CMS lookback window or imply that no observed condition means no disease. Apply the same cohort and time window to both counts and denominators. CMS Chronic Conditions issue #48 is outside the 1.0 scope.
Chronic diseases are one of the biggest drivers of healthcare utilization and expenditure. Here we provide an examples of the types of analytics you can do with Tuva related to chronic conditions.
Prevalence of Chronic Conditions
In this query we show how often each chronic condition occurs in the patient population.
with patients as (
select data_source, count(*) as patients
from core.patient
group by data_source
), conditions as (
select distinct data_source, person_id, condition
from chronic_conditions.cms_chronic_conditions_long
)
select c.data_source, c.condition, count(*) as patients_with_condition,
100.0 * count(*) / nullif(p.patients, 0) as percent_of_patients
from conditions c
join patients p on c.data_source = p.data_source
group by c.data_source, c.condition, p.patients
order by c.data_source, patients_with_condition desc;
Distribution of Chronic Conditions
In this query we show how many patients have 0 chronic conditions, how many patients have 1 chronic condition, how many patients have 2 chronic conditions, etc.
with conditions as (
select distinct data_source, person_id, condition
from chronic_conditions.cms_chronic_conditions_long
), condition_count as (
select p.data_source, p.person_id, count(c.condition) as condition_count
from core.patient p
left join conditions c
on p.person_id = c.person_id and p.data_source = c.data_source
group by p.data_source, p.person_id
)
select data_source, condition_count, count(*) as patients,
100.0 * count(*) / sum(count(*)) over (partition by data_source) as percent
from condition_count
group by data_source, condition_count
order by data_source, condition_count;
CMS-HCCs
These summaries retain data_source, payer, and payment_year, which are part of the risk-score grain. Risk-factor distributions also retain model_version; their denominator is factor records, not people. Set cms_hcc_payment_year to the intended payment year before building the package.
CMS-HCC is the risk adjustment model used by CMS. Analyzing risk scores based on the output of this model is an important use case for value-based care analytics.
Average CMS-HCC Risk Scores
select data_source, payer, payment_year,
count(*) as patient_payer_records,
avg(blended_risk_score) as average_blended_risk_score,
avg(normalized_risk_score) as average_normalized_risk_score,
avg(payment_risk_score) as average_payment_risk_score
from cms_hcc.patient_risk_scores
group by data_source, payer, payment_year;
Average CMS-HCC Risk Scores by Patient Location
select risk.data_source, risk.payer, risk.payment_year,
patient.state, patient.city, patient.zip_code,
avg(risk.payment_risk_score) as average_payment_risk_score
from cms_hcc.patient_risk_scores risk
join core.patient patient
on risk.person_id = patient.person_id and risk.data_source = patient.data_source
group by risk.data_source, risk.payer, risk.payment_year,
patient.state, patient.city, patient.zip_code;
Distribution of CMS-HCC Risk Factors
select data_source, payer, payment_year, model_version, risk_factor_description,
count(*) as factor_records,
100.0 * count(*) / sum(count(*)) over (
partition by data_source, payer, payment_year, model_version
) as percent_of_factor_records
from cms_hcc.patient_risk_factors
group by data_source, payer, payment_year, model_version, risk_factor_description
order by data_source, payer, payment_year, model_version, factor_records desc;
Risk Weighted by Member Months
select data_source, payer, payment_year,
sum(payment_risk_score_weighted_by_months)
/ nullif(sum(member_months), 0) as weighted_risk_score
from cms_hcc.patient_risk_scores
group by data_source, payer, payment_year;
Stratified CMS-HCC Risk Scores
select data_source, payer, payment_year,
sum(case when payment_risk_score < 1.00 then 1 else 0 end) as low_risk,
sum(case when payment_risk_score = 1.00 then 1 else 0 end) as average_risk,
sum(case when payment_risk_score > 1.00 then 1 else 0 end) as high_risk,
sum(case when payment_risk_score is null then 1 else 0 end) as missing_risk,
avg(payment_risk_score) as population_average
from cms_hcc.patient_risk_scores
group by data_source, payer, payment_year;
Top 10 CMS-HCC Conditions
select data_source, payer, payment_year, model_version, risk_factor_description,
count(distinct person_id) as patients
from cms_hcc.patient_risk_factors
where factor_type = 'Disease'
group by data_source, payer, payment_year, model_version, risk_factor_description
order by patients desc
limit 10;
Demographics
Here we demonstrate the different types of patient demographics in Tuva and how you can use them in analysis.
Age Distribution
with patient_age as (
select
data_source
, person_id
, date_part('year', age(current_date, birth_date)) as age
from core.patient
)
, age_groups as (
select
data_source
, person_id
, age
, case
when age >= 0 and age < 2 then '00-02'
when age >= 2 and age < 18 then '02-18'
when age >= 18 and age < 30 then '18-30'
when age >= 30 and age < 40 then '30-40'
when age >= 40 and age < 50 then '40-50'
when age >= 50 and age < 60 then '50-60'
when age >= 60 and age < 70 then '60-70'
when age >= 70 and age < 80 then '70-80'
when age >= 80 and age < 90 then '80-90'
when age >= 90 then '>= 90'
else 'Missing Age'
end as age_group
from patient_age
)
select
data_source
, age_group
, count(distinct person_id) as patient_count
, cast(100.0 * count(distinct person_id)/sum(count(distinct person_id)) over(partition by data_source) as numeric(38,1)) as percent
from age_groups
group by 1,2
order by 1,2
Sex Distribution
select data_source, sex, count(*) as patients,
100.0 * count(*) / sum(count(*)) over (partition by data_source) as percent
from core.patient
group by data_source, sex
order by data_source, sex;
Race Distribution
select data_source, race, count(*) as patients,
100.0 * count(*) / sum(count(*)) over (partition by data_source) as percent
from core.patient
group by data_source, race
order by data_source, race;
Members by State and Zip Code
select state
,zip_code
,count(*) as member_count
from core.patient
group by
state
,zip_code
order by count(*) desc
ED Visits
Analyzing ED claims data helps identify high utilizers of emergency services, often indicating overuse of EDs for conditions that can be managed with proper primary care.
ED Visits Trended
Trending ED Visit Volume, PKPY, and Cost
with enrollment as (
select data_source, year_month, count(*) as member_months
from core.member_month
group by data_source, year_month
), eligible_people as (
select distinct data_source, person_id, year_month from core.member_month
), ed as (
select e.data_source, strftime(e.encounter_end_date, '%Y%m') as year_month,
count(*) as visits, sum(e.paid_amount) as paid_amount
from core.encounter e
join eligible_people m
on e.person_id = m.person_id and e.data_source = m.data_source
and strftime(e.encounter_end_date, '%Y%m') = m.year_month
where e.encounter_type = 'emergency department' and e.encounter_source_type = 'claim'
group by e.data_source, strftime(e.encounter_end_date, '%Y%m')
)
select m.data_source, m.year_month, m.member_months,
coalesce(e.visits, 0) as ed_visits,
12000.0 * coalesce(e.visits, 0) / nullif(m.member_months, 0) as visits_pkpy,
e.paid_amount / nullif(e.visits, 0) as paid_per_visit,
coalesce(e.paid_amount, 0) as ed_paid_amount
from enrollment m
left join ed e on m.data_source = e.data_source and m.year_month = e.year_month
order by m.data_source, m.year_month;
ED Spend as Percent of Total Spend
select data_source
,year_month
,sum(emergency_department_paid) as ed_paid
,sum(total_paid) as total_paid
,cast(sum(emergency_department_paid) as decimal(18,2))/nullif(cast(sum(total_paid) as decimal(18,2)), 0) as ed_percent_of_total_paid
from core.cost
group by data_source
,year_month
order by data_source
,year_month
ED Visits by Member and Year
select
data_source
, strftime(encounter_end_date, '%Y') AS year_nbr
, person_id
, COUNT(*) AS ed_visits
from core.encounter
where encounter_type = 'emergency department'
and encounter_source_type = 'claim'
group by data_source
, strftime(encounter_end_date, '%Y')
, person_id
ORDER BY ed_visits desc
, year_nbr
, person_id
;
Frequency Distribution of ED Visits
with visits as (
select
data_source
, person_id
, COUNT(*) AS ed_visits
from core.encounter
where encounter_type = 'emergency department'
and encounter_source_type = 'claim'
group by data_source
, person_id
)
,members as (
select distinct person_id
,data_source
from core.member_month
)
,members_total as (
select count(*) as total_member_count
from members
)
,members_with_visits as (
select m.person_id
,m.data_source
,coalesce(v.ed_visits,0) as ed_visits
from members m
left join visits v on m.person_id = v.person_id
and
m.data_source = v.data_source
)
select ed_visits
,count(*) as member_count
,count(*) / nullif(cast(max(total_member_count) as real), 0) as percent_of_total_members
from members_with_visits
cross join members_total
group by ed_visits
order by ed_visits
;
Count of ED NPIs
select data_source
,count(distinct facility_npi) as ed_facilities_count
from core.encounter e
where encounter_type = 'emergency department'
and encounter_source_type = 'claim'
group by
data_source
order by ed_facilities_count desc
Visit by Facility
select
facility_npi
, COUNT(*) AS ed_visits
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/ nullif(count(*), 0) as decimal(18,2))as paid_per_visit
from core.encounter e
where encounter_type = 'emergency department'
and encounter_source_type = 'claim'
group by
facility_npi
ORDER BY ed_visits desc
;
Admit Source and Type
select
admit_source_code
, admit_source_description
, admit_type_code
, admit_type_description
, count(*) AS ed_visits
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/ nullif(count(*), 0) as decimal(18,2))as paid_per_visit
from core.encounter e
where encounter_type = 'emergency department'
and encounter_source_type = 'claim'
group by
admit_source_code
, admit_source_description
, admit_type_code
, admit_type_description
ORDER BY ed_visits desc
;
ED Classification
The Tuva Project utilizes the NYU algorithm to classify ED visits, helping to identify care patterns that are not being met by primary care providers.
Of the different classifications in the NYU algorithm, the categories usually classified as "potentially preventable" are:
- Emergent, Primary Care Treatable
- Non-Emergent
- Emergent, ED Care Needed, Preventable/Avoidable
ED Classification
select coalesce(s.ed_classification_description,'Not Classified') as ed_classification_category
, count(*) as visit_count
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/ nullif(count(*), 0) as decimal(18,2))as paid_per_visit
from core.encounter e
left join ed_classification.summary s on e.encounter_id = s.encounter_id
and e.data_source = s.data_source
where e.encounter_type = 'emergency department' and e.encounter_source_type = 'claim'
group by coalesce(s.ed_classification_description,'Not Classified')
order by visit_count desc
Members with at least One Potentially Preventable ED Visit
with enc as
(
select e.person_id
,strftime(e.encounter_end_date, '%Y') as year_nbr
,e.data_source
,count(distinct e.encounter_id) as potentially_preventable
,sum(e.paid_amount) as paid_amount
from core.encounter e
inner join ed_classification.summary s on e.encounter_id = s.encounter_id
and e.data_source = s.data_source
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
and ed_classification_description in ('Emergent, Primary Care Treatable','Non-Emergent','Emergent, ED Care Needed, Preventable/Avoidable')
group by e.person_id
,e.data_source
,strftime(e.encounter_end_date, '%Y')
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,sum(case when enc.potentially_preventable >=1 then 1 else 0 end) as members_with_potentially_preventable
,count(*) as total_members
,sum(case when enc.potentially_preventable >=1 then 1 else 0 end)/ nullif(count(*), 0) as potentially_preventable_percent_of_total
,sum(enc.paid_amount)/ nullif(sum(enc.potentially_preventable), 0) as avg_cost_potentially_preventable
from member_year my
left join enc on my.year_nbr = enc.year_nbr
and
enc.data_source = my.data_source
and
enc.person_id = my.person_id
group by my.data_source
,my.year_nbr
Primary Diagnosis Codes for Avoidable Categories
select coalesce(s.ed_classification_description,'Not Classified') as ed_classification_category
, e.primary_diagnosis_code
, e.primary_diagnosis_description
, count(*) as visit_count
, sum(cast(e.paid_amount as decimal(18,2))) as paid_amount
, cast(sum(e.paid_amount)/ nullif(count(*), 0) as decimal(18,2))as paid_per_visit
from core.encounter e
left join ed_classification.summary s on e.encounter_id = s.encounter_id
and e.data_source = s.data_source
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
and ed_classification_description in ('Emergent, Primary Care Treatable','Non-Emergent','Emergent, ED Care Needed, Preventable/Avoidable')
group by coalesce(s.ed_classification_description,'Not Classified')
, e.primary_diagnosis_code
, e.primary_diagnosis_description
order by ed_classification_category
, visit_count desc
;
ED Diagnosis Grouping
The Tuva Project provides several ways of grouping diagnosis codes. CCSR (AHRQ) provides a hierarchy grouping of diagnosis codes, and is useful for recognizing patterns of care by what the patient was diagnosed with at the ED.
An encounter can have multiple primary diagnoses across its claims. The CCSR example deduplicates each encounter/category pair; an encounter can still contribute to multiple categories, so category totals must not be added together.
Chronic Conditions are a way of grouping members by conditions that they have been diagnosed with (within the relevant timespan, usually the last 1 or 2 years.)
ED Visits by CCSR Category and Body System
with categories as (
select distinct data_source, encounter_id, ccsr_category,
ccsr_category_description, ccsr_parent_category, body_system
from <ccsr_schema>.ccsr__long_condition_category
where diagnosis_rank = 1 and ccsr_category_rank = 1
)
select e.data_source, c.ccsr_category, c.ccsr_category_description,
c.ccsr_parent_category, c.body_system,
count(*) as visit_count, sum(e.paid_amount) as paid_amount,
sum(e.paid_amount) / nullif(count(*), 0) as paid_per_visit
from core.encounter e
left join categories c
on e.encounter_id = c.encounter_id and e.data_source = c.data_source
where e.encounter_type = 'emergency department' and e.encounter_source_type = 'claim'
group by e.data_source, c.ccsr_category, c.ccsr_category_description,
c.ccsr_parent_category, c.body_system
order by e.data_source, visit_count desc;
ED Visits by Chronic Condition Category
Since members often have more than one chronic condition, encounters are duplicated for each chronic condition causing the total amount to be inflated. The division of encounters by chronic condition is useful for comparision across disease states, and less so from the total standpoint.
with conditions as (
select distinct data_source, person_id, condition
from chronic_conditions.cms_chronic_conditions_long
)
select e.data_source, coalesce(c.condition, 'No observed chronic condition') as condition,
count(*) as visit_count, sum(e.paid_amount) as paid_amount,
sum(e.paid_amount) / nullif(count(*), 0) as paid_per_visit
from core.encounter e
left join conditions c
on e.person_id = c.person_id and e.data_source = c.data_source
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
group by e.data_source, coalesce(c.condition, 'No observed chronic condition')
order by e.data_source, visit_count desc;
Medical PMPM
Per Member Per Month (PMPM) spend is the starting point for any claims based analysis.
Calculate Member Months and Total Medical Spend
Select
data_source
, year_month
, cast(sum(medical_paid) as decimal(18,2)) as medical_paid
, count(*) as member_months
, cast(sum(medical_paid)/ nullif(count(*), 0) as decimal(18,2)) as pmpm
from core.cost
group by
data_source
, year_month
order by data_source
, year_month
Trending PMPM by Service Category
The cost table breaks out cost by service category at the member month level. Aggregate it to trend PMPM by data source and month.
select
data_source
, year_month
, count(*) as member_months
, sum(total_paid) / nullif(count(*), 0) as total_paid
, sum(medical_paid) / nullif(count(*), 0) as medical_paid
, sum(inpatient_paid) / nullif(count(*), 0) as inpatient_paid
, sum(outpatient_paid) / nullif(count(*), 0) as outpatient_paid
, sum(office_based_paid) / nullif(count(*), 0) as office_based_paid
, sum(ancillary_paid) / nullif(count(*), 0) as ancillary_paid
, sum(other_paid) / nullif(count(*), 0) as other_paid
, sum(pharmacy_paid) / nullif(count(*), 0) as pharmacy_paid
from core.cost
group by
data_source
, year_month
order by
data_source
, year_month
Trending PMPM by Claim Type
Here we calculate PMPM manually by counting member months and joining payments by claim type to them.
with claims as (
select member_month_id, claim_type, sum(paid_amount) as paid_amount
from core.medical_claim
where member_month_id is not null
group by member_month_id, claim_type
), claim_types as (
select distinct claim_type from core.medical_claim where claim_type is not null
)
select mm.data_source, mm.year_month, t.claim_type,
count(*) as enrollment_member_months,
sum(coalesce(c.paid_amount, 0)) as paid_amount,
sum(coalesce(c.paid_amount, 0)) / nullif(count(*), 0) as medical_pmpm
from core.member_month mm
cross join claim_types t
left join claims c
on mm.member_month_id = c.member_month_id and t.claim_type = c.claim_type
group by mm.data_source, mm.year_month, t.claim_type
order by mm.data_source, mm.year_month, t.claim_type;
PMPM by Chronic Condition
Here we calculate PMPM by chronic condition. Since members can and do have more than one chronic condition, payments and members months are duplicated. This is useful for comparing spend across chronic conditions, but should be used with caution given the duplication across conditions.
with conditions as (
select distinct data_source, person_id, condition
from chronic_conditions.cms_chronic_conditions_long
)
select cost.data_source,
coalesce(c.condition, 'No observed chronic condition') as condition,
count(*) as enrollment_member_months,
sum(cost.medical_paid) as medical_paid,
sum(cost.medical_paid) / nullif(count(*), 0) as medical_pmpm
from core.cost cost
left join conditions c
on cost.person_id = c.person_id and cost.data_source = c.data_source
group by cost.data_source, coalesce(c.condition, 'No observed chronic condition')
order by cost.data_source, enrollment_member_months desc;
Claims and Enrollment
Understanding the relationship between enrollment and claims is paramount for in-depth claims and population health analysis. It is important to analyze the proportion of the enrolled population that is actively utilizing healthcare services and to identify the characteristics of those who have not accessed care at all.
Members with Claims by Month
with used_member_months as (
select distinct member_month_id from core.medical_claim where member_month_id is not null
)
select mm.data_source, mm.year_month,
count(u.member_month_id) as enrollment_member_months_with_claims,
count(*) as total_enrollment_member_months,
100.0 * count(u.member_month_id) / nullif(count(*), 0) as percent_with_claims
from core.member_month mm
left join used_member_months u on mm.member_month_id = u.member_month_id
group by mm.data_source, mm.year_month
order by mm.data_source, mm.year_month;
Members with Claims
with medical_claims as (
select
data_source
, person_id
, cast(sum(paid_amount) as decimal(18,2)) AS paid_amount
from core.medical_claim
GROUP BY data_source
, person_id
)
, members as (
select distinct person_id
,data_source
from core.member_month
)
select mm.data_source
,sum(case when mc.person_id is not null then 1 else 0 end) as members_with_claims
,count(*) as members
,sum(case when mc.person_id is not null then 1 else 0 end) / nullif(count(*), 0) as percentage_with_claims
from members mm
left join medical_claims mc on mc.person_id = mm.person_id
and
mc.data_source = mm.data_source
group by mm.data_source
Claims with Enrollment
The inverse of the above. Ideally this number will be 100%, but there could be extenuating reasons why not all claims have a corresponding member with enrollment.
select data_source,
sum(case when enrollment_flag = 1 then 1 else 0 end) as claim_lines_with_enrollment,
count(*) as claim_lines,
100.0 * sum(case when enrollment_flag = 1 then 1 else 0 end)
/ nullif(count(*), 0) as percent_claim_lines_with_enrollment
from core.medical_claim
group by data_source;
Pharmacy
Pharmacy Claims and Enrollment
Members with Pharmacy Claims by Month
with used_member_months as (
select distinct member_month_id from core.pharmacy_claim where member_month_id is not null
)
select mm.data_source, mm.year_month,
count(u.member_month_id) as enrollment_member_months_with_claims,
count(*) as total_enrollment_member_months,
100.0 * count(u.member_month_id) / nullif(count(*), 0) as percent_with_claims
from core.member_month mm
left join used_member_months u on mm.member_month_id = u.member_month_id
group by mm.data_source, mm.year_month
order by mm.data_source, mm.year_month;
Members with Pharmacy Claims
with pharmacy_claim as (
select
data_source
, person_id
, cast(sum(paid_amount) as decimal(18,2)) AS paid_amount
from core.pharmacy_claim
GROUP BY data_source
, person_id
)
, members as (
select distinct person_id
,data_source
from core.member_month
)
select mm.data_source
,sum(case when mc.person_id is not null then 1 else 0 end) as members_with_claims
,count(*) as members
,sum(case when mc.person_id is not null then 1 else 0 end) / nullif(count(*), 0) as percentage_with_claims
from members mm
left join pharmacy_claim mc on mc.person_id = mm.person_id
and
mc.data_source = mm.data_source
group by mm.data_source
Pharmacy Claims with Enrollment
The inverse of the above. Ideally this number will be 100%, but there could be extenuating reasons why not all claims have a corresponding member with enrollment.
select data_source,
sum(case when enrollment_flag = 1 then 1 else 0 end) as claim_lines_with_enrollment,
count(*) as claim_lines,
100.0 * sum(case when enrollment_flag = 1 then 1 else 0 end)
/ nullif(count(*), 0) as percent_claim_lines_with_enrollment
from core.pharmacy_claim
group by data_source;
Understanding Retail Pharmacy Utilization
Prescribing Providers
select
data_source
,prescribing_provider_id
,sum(paid_amount) as pharmacy_paid_amount
,sum(days_supply) as pharmacy_days_supply
from core.pharmacy_claim
group by
data_source
,prescribing_provider_id
order by pharmacy_paid_amount desc
Pharmacy Names
select
data_source
,dispensing_provider_id
,sum(paid_amount) as pharmacy_paid_amount
,sum(days_supply) as pharmacy_days_supply
from core.pharmacy_claim
group by dispensing_provider_id
,data_source
order by pharmacy_paid_amount desc
Brand vs Generic Rx (Tuva 0.18 only)
These examples depended on total_cost_of_care.pharmacy_claim_expanded and total_cost_of_care.generic_available_list. Neither relation exists in Tuva 1.0, and the retired standalone Pharmacy data mart has no direct successor. Do not run these queries against Tuva 1.0. See the Tuva 1.0 Data Mart migration details for the supported replacements and migration scope.
Primary Care
Analyzing primary care utilization in claims data is crucial for understanding healthcare access, quality, and costs, as it provides insights into the frequency and types of services patients receive from their primary care providers. By examining claims data, researchers and policymakers can identify patterns, disparities, and potential areas for improvement in primary care delivery.
Primary Care Spend and PMPM
The specialty list and service-category filter below are an illustrative primary-care definition. Claims-derived provider IDs equal the source NPI, so the join uses (practitioner_id, data_source) to avoid a potentially non-unique NPI join. Counts use grouped encounter_id, rather than assuming a claim is a visit.
Primary Care Spend, % of Total, and PMPM
with primary_care as (
select
m.data_source
,mm.year_month AS year_month
,sum(paid_amount) as primary_care_paid_amount
from core.medical_claim m
inner join core.practitioner p on coalesce(m.rendering_npi,m.billing_npi) = p.practitioner_id
and m.data_source = p.data_source
inner join core.member_month mm on m.member_month_id = mm.member_month_id
where service_category_2 in ('office-based evaluation and management','outpatient hospital or clinic')
and
p.specialty in ('Family Medicine','Internal Medicine','Obstetrics & Gynecology','Pediatric Medicine','Physician Assistant','Nurse Practitioner')
group by mm.year_month
,m.data_source
)
,total_cost as
(
select data_source
,year_month
,sum(total_paid) as total_paid
from core.cost
group by data_source
,year_month
)
select pmpm.data_source
,pmpm.year_month
,cast(pc.primary_care_paid_amount as decimal(18,2)) as primary_care_paid_amount
,cast(tc.total_paid as decimal(18,2)) as total_paid
,cast(pc.primary_care_paid_amount/nullif(tc.total_paid, 0) as decimal(18,2)) primary_care_percent_of_total
,cast(pc.primary_care_paid_amount/nullif(pmpm.member_months, 0) as decimal(18,2)) as primary_care_pmpm
from (
select
data_source
,year_month
,count(*) as member_months
from core.cost
group by
data_source
,year_month
) pmpm
left join primary_care pc on pmpm.data_source = pc.data_source
and
pmpm.year_month = pc.year_month
left join total_cost tc on pmpm.data_source = tc.data_source
and
pmpm.year_month = tc.year_month
order by
pmpm.data_source
,pmpm.year_month
Primary Care Visits
Average Primary Care Visits per Member
with primary_care as
(
select
m.data_source
,left(mm.year_month,4) AS year_nbr
,count(distinct m.encounter_id) as visit_count
,m.person_id
from core.medical_claim m
inner join core.practitioner p on coalesce(m.rendering_npi,m.billing_npi) = p.practitioner_id
and m.data_source = p.data_source
inner join core.member_month mm on m.member_month_id = mm.member_month_id
where service_category_2 in ('office-based evaluation and management','outpatient hospital or clinic')
and
p.specialty in ('Family Medicine','Internal Medicine','Obstetrics & Gynecology','Pediatric Medicine','Physician Assistant','Nurse Practitioner')
group by left(mm.year_month,4)
,m.data_source
,m.person_id
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,coalesce(sum(pc.visit_count), 0) as primary_visit_count
,count(distinct my.person_id) as member_count
,coalesce(sum(pc.visit_count), 0)/nullif(count(distinct my.person_id), 0) as primary_care_visits_per_member
from member_year my
left join primary_care pc on my.data_source = pc.data_source
and
my.year_nbr = pc.year_nbr
and
my.person_id = pc.person_id
group by
my.data_source
,my.year_nbr
order by my.year_nbr
,my.data_source
Members with at Least One Primary Care Visit
with primary_care as
(
select
m.data_source
,left(mm.year_month,4) AS year_nbr
,count(distinct m.encounter_id) as visit_count
,m.person_id
from core.medical_claim m
inner join core.practitioner p on coalesce(m.rendering_npi,m.billing_npi) = p.practitioner_id
and m.data_source = p.data_source
inner join core.member_month mm on m.member_month_id = mm.member_month_id
where service_category_2 in ('office-based evaluation and management','outpatient hospital or clinic')
and
p.specialty in ('Family Medicine','Internal Medicine','Obstetrics & Gynecology','Pediatric Medicine','Physician Assistant','Nurse Practitioner')
group by left(mm.year_month,4)
,m.data_source
,m.person_id
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,sum(case when pc.visit_count >= 1 then 1 else 0 end) as at_least_one_pc_visit
,count(*) as member_count
,sum(case when pc.visit_count >= 1 then 1 else 0 end)/ nullif(count(*), 0) as percent_at_least_one_pc_visit
from member_year my
left join primary_care pc on my.data_source = pc.data_source
and
my.year_nbr = pc.year_nbr
and
my.person_id = pc.person_id
group by
my.data_source
,my.year_nbr
order by
my.year_nbr
,my.data_source
;
Primary Care Providers
Primary Care Providers
select
m.data_source
,coalesce(m.rendering_npi,m.billing_npi) as primary_care_provider_npi
,p.first_name || ' '|| p.last_name as primary_care_provider_name
,count(distinct m.encounter_id) as visit_count
,sum(paid_amount) as paid_amount
from core.medical_claim m
inner join core.practitioner p on coalesce(m.rendering_npi,m.billing_npi) = p.practitioner_id
and m.data_source = p.data_source
where service_category_2 in ('office-based evaluation and management','outpatient hospital or clinic')
and
p.specialty in ('Family Medicine','Internal Medicine','Obstetrics & Gynecology','Pediatric Medicine','Physician Assistant','Nurse Practitioner')
group by m.data_source
,coalesce(m.rendering_npi,m.billing_npi)
,p.first_name || ' '|| p.last_name
order by visit_count desc
Urgent Care
Urgent Care serves as a low-cost solution when compared to the Emergency Department. Analyzing the frequency and circumstances of Urgent Care use, and comparing these to Emergency Department statistics, provides a useful perspective on how people use immediate care options.
Urgent Care Utilization
The examples use Tuva's grouped encounter_id for visit counts. Null encounter IDs are not counted as visits; review ungrouped claims separately. Do not construct a visit identifier by concatenating person, source, and service-date text.
Urgent Care by Facility
select
mc.billing_npi
,l.name
,count(distinct mc.encounter_id) as visits
,sum(coalesce(mc.paid_amount,0)) as paid_amount
from core.medical_claim mc
left join core.location l on mc.billing_npi = l.location_id
and mc.data_source = l.data_source
where service_category_2 = 'urgent care'
group by mc.billing_npi
,l.name
order by paid_amount desc
Urgent Care PMPM and PKPY
with uc as
(
select mc.person_id
,mc.data_source
,left(mm.year_month,4) as year_nbr
,count(distinct mc.encounter_id) as visits
,sum(mc.paid_amount) as paid_amount
from core.medical_claim mc
join core.member_month mm on mc.member_month_id = mm.member_month_id
where service_category_2 = 'urgent care'
group by mc.person_id
,mc.data_source
,left(mm.year_month,4)
)
,member_year as (
select data_source
,person_id
,left(year_month,4) as year_nbr
,count(*) as member_months
from core.cost pmpm
group by
data_source
,person_id
,left(year_month,4)
)
select my.data_source
,my.year_nbr
,sum(member_months) as member_months
,cast(coalesce(sum(uc.visits), 0)/ nullif(sum(member_months), 0) * 12000 as decimal(18,2)) as urgent_care_pkpy
,cast(coalesce(sum(uc.paid_amount), 0)/ nullif(sum(member_months), 0) as decimal(18,2)) as urgent_care_pmpm
,coalesce(sum(uc.visits), 0) as urgent_care_absolute_visits
,cast(coalesce(sum(uc.paid_amount), 0) as decimal(18,2)) as urgent_care_absolute_paid
from member_year my
left join uc on uc.data_source = my.data_source
and
uc.person_id = my.person_id
and uc.year_nbr = my.year_nbr
group by my.data_source
,my.year_nbr
order by my.data_source
,my.year_nbr
Members with at least One Urgent Care Visit
with enc as
(
select mc.person_id
,left(mm.year_month,4) as year_nbr
,mc.data_source
,count(distinct mc.encounter_id) as urgent_care
,sum(mc.paid_amount) as paid_amount
from core.medical_claim mc
join core.member_month mm on mc.member_month_id = mm.member_month_id
where service_category_2 = 'urgent care'
group by mc.person_id
,mc.data_source
,left(mm.year_month,4)
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,sum(case when enc.urgent_care >=1 then 1 else 0 end) as members_with_at_least_one_uc
,count(*) as total_members
,sum(case when enc.urgent_care >=1 then 1 else 0 end)/ nullif(count(*), 0) as at_least_one_percent_total
,sum(enc.paid_amount)/ nullif(sum(enc.urgent_care), 0) as avg_cost_urgent_care
from member_year my
left join enc on my.year_nbr = enc.year_nbr
and
enc.data_source = my.data_source
and
enc.person_id = my.person_id
group by my.data_source
,my.year_nbr
Urgent Care and ED Comparison
ED and Urgent Care Visits by Year
with uc as
(
select mc.person_id
,left(mm.year_month,4) as year_nbr
,mc.data_source
,count(distinct mc.encounter_id) as visits
,sum(mc.paid_amount) as paid_amount
from core.medical_claim mc
join core.member_month mm on mc.member_month_id = mm.member_month_id
where service_category_2 = 'urgent care'
group by mc.person_id
,mc.data_source
,left(mm.year_month,4)
)
,ed as (
select e.person_id
,strftime(encounter_end_date, '%Y') as year_nbr
,data_source
,count(distinct e.encounter_id) as visits
,sum(e.paid_amount) as paid_amount
from core.encounter e
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
and exists (
select 1 from core.member_month mm
where mm.person_id = e.person_id and mm.data_source = e.data_source
and mm.year_month = strftime(e.encounter_end_date, '%Y%m')
)
group by e.person_id
,data_source
,strftime(encounter_end_date, '%Y')
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,coalesce(sum(uc.visits), 0) urgent_care_visits
,coalesce(sum(ed.visits), 0) ed_visits
from member_year my
left join ed on my.year_nbr = ed.year_nbr
and
ed.data_source = my.data_source
and
ed.person_id = my.person_id
left join uc on my.year_nbr = uc.year_nbr
and
uc.data_source = my.data_source
and
uc.person_id = my.person_id
and uc.year_nbr = my.year_nbr
group by my.data_source
,my.year_nbr
ED and Urgent Care Utilization by Member
with uc as
(
select mc.person_id
,mc.data_source
,count(distinct mc.encounter_id) as visits
,sum(mc.paid_amount) as paid_amount
from core.medical_claim mc
join core.member_month mm on mc.member_month_id = mm.member_month_id
where service_category_2 = 'urgent care'
group by mc.person_id
,mc.data_source
)
,ed as (
select e.person_id
,data_source
,count(distinct e.encounter_id) as visits
,sum(e.paid_amount) as paid_amount
from core.encounter e
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
and exists (
select 1 from core.member_month mm
where mm.person_id = e.person_id and mm.data_source = e.data_source
and mm.year_month = strftime(e.encounter_end_date, '%Y%m')
)
group by e.person_id
,data_source
)
,member_year as (
select distinct data_source
,person_id
from core.cost pmpm
)
select my.data_source
,my.person_id
,coalesce(uc.visits,0) as urgent_care_visits
,coalesce(ed.visits,0) as ed_visits
,cast(coalesce(uc.paid_amount,0) as decimal(18,2)) as urgent_care_paid_amount
,cast(coalesce(ed.paid_amount,0) as decimal(18,2)) as ed_paid_amount
from member_year my
left join ed on ed.data_source = my.data_source
and
ed.person_id = my.person_id
left join uc on uc.data_source = my.data_source
and
uc.person_id = my.person_id
where uc.person_id is not null
OR
ed.person_id is not null
order by ed_visits desc
Members with an ED Visit and no Urgent Care
with uc as
(
select mc.person_id
,left(mm.year_month,4) as year_nbr
,mc.data_source
,count(distinct mc.encounter_id) as visits
,sum(mc.paid_amount) as paid_amount
from core.medical_claim mc
join core.member_month mm on mc.member_month_id = mm.member_month_id
where service_category_2 = 'urgent care'
group by mc.person_id
,mc.data_source
,left(mm.year_month,4)
)
,ed as (
select e.person_id
,strftime(encounter_end_date, '%Y') as year_nbr
,data_source
,count(distinct e.encounter_id) as visits
,sum(e.paid_amount) as paid_amount
from core.encounter e
where e.encounter_type = 'emergency department'
and e.encounter_source_type = 'claim'
and exists (
select 1 from core.member_month mm
where mm.person_id = e.person_id and mm.data_source = e.data_source
and mm.year_month = strftime(e.encounter_end_date, '%Y%m')
)
group by e.person_id
,data_source
,strftime(encounter_end_date, '%Y')
)
,member_year as (
select distinct data_source
,left(year_month,4) as year_nbr
,person_id
from core.cost pmpm
)
select my.data_source
,my.year_nbr
,sum(case when uc.visits >= 1 then 1 else 0 end) members_with_at_least_one_uc
,sum(case when ed.visits >= 1 then 1 else 0 end) members_with_at_least_one_ed
,sum(case when ed.visits >= 1 and coalesce(uc.visits,0) = 0 then 1 else 0 end) members_with_ed_no_uc
from member_year my
left join ed on my.year_nbr = ed.year_nbr
and
ed.data_source = my.data_source
and
ed.person_id = my.person_id
left join uc on my.year_nbr = uc.year_nbr
and
uc.data_source = my.data_source
and
uc.person_id = my.person_id
and uc.year_nbr = my.year_nbr
group by my.data_source
,my.year_nbr