with t1 as (
select
dept,
count(distinct v.visit_id) as feb_2024_visits,
count(distinct patient_id) as feb_2024_unique_patients,
coalesce(sum(is_antibiotic)/nullif(count(prescription_id), 0), 0) as feb_2024_antibiotic_rate
from visits v 
left join prescriptions p on v.visit_id = p.visit_id
where date_format(visit_date, '%Y-%m') = '2024-02'
group by dept
),

t2 as (
select
v1.dept,
count(distinct case when v2.visit_id is not null then v1.visit_id end) / 
count(distinct v1.visit_id) as feb_2024_revisit_rate
from visits v1  # 2月就诊记录
left join visits v2 # 1月就诊记录
on v1.patient_id = v2.patient_id 
and v1.dept = v2.dept 
and DATEDIFF(v1.visit_date, v2.visit_date) BETWEEN 1 AND 30 # 就诊时间在当前日期的前30天内
where date_format(v1.visit_date, '%Y-%m') = '2024-02'
group by dept
)

select
t1.dept,
feb_2024_visits,
feb_2024_unique_patients,
round(feb_2024_revisit_rate*100, 2) as feb_2024_revisit_rate,
round(feb_2024_antibiotic_rate*100, 2) as feb_2024_antibiotic_rate
from t1
left join t2 on t1.dept = t2.dept
order by 1
;