#left join 一对多，1个visit_id会有多个处方id，故而人次和去重人数都需distinct
with sub as (
select
v.dept,
count(distinct v.visit_id) feb_2024_visits,
count(distinct v.patient_id) feb_2024_unique_patients,
round(ifnull(avg(if(pr.is_antibiotic = 1, 1, 0)),0)*100,2) feb_2024_antibiotic_rate
from visits v
left join prescriptions pr on v.visit_id = pr.visit_id
where v.visit_date >= '2024-02-01' and v.visit_date < '2024-03-01'
group by v.dept
),

#提取同病人id同科室上次看诊日期
visits_sub as (
select patient_id, dept, visit_date,
lag(visit_date,1)over(partition by patient_id, dept order by visit_date) visit_date_lag,
datediff(visit_date, lag(visit_date,1)over(partition by patient_id, dept order by visit_date)) days
from visits
),


#计算各科室复诊率
visits_agg as (
select 
dept,
round(ifnull(avg(case when days is null then 0 when days between 1 and 30 then 1 else 0 end),0)*100,2) feb_2024_revisit_rate
from visits_sub
where visit_date >= '2024-02-01' and visit_date < '2024-03-01'
group by dept
)

#结果呈现
select
dept,
feb_2024_visits,
feb_2024_unique_patients,
feb_2024_revisit_rate,
feb_2024_antibiotic_rate
from sub
left join visits_agg using(dept)
order by dept