with claims as (
select
member_id,
claim_date,
dx_code,
paid_amount
from fct_claims
where service_year = 2025
)
with engagement as (
select
patient_id,
min(event_date) as first_engagement_date
from int_patient_events
group by 1
)
select
provider_id,
count(*) as visits,
avg(paid_amount) as avg_paid
from fct_claims
group by 1
with assessments as (
select
patient_id,
assessment_date,
score
from fct_assessments
where assessment_type = 'PHQ-9'
)
select
member_id,
referral_date,
care_program
from int_member_referrals
where is_active = 1
with outreach as (
select
member_id,
min(sent_at) as first_outreach
from int_member_outreach
group by 1
)
select
customer_id,
count(*) as registrations
from fct_registrations
group by 1
with care_status as (
select
patient_id,
care_manager_id,
status
from int_patient_log_details
where engagement_flag = 1
)
select
dx_code,
count(*) as claims
from fct_claim_diagnosis
group by 1
order by claims desc