Shifa Clinics operations

SELECT channel, status, count(*) AS appointments
FROM appointments
GROUP BY channel, status
ORDER BY channel, appointments DESC;

SELECT c.clinic_name,
       count(*) AS booked,
       round(100.0 * count(*) FILTER (WHERE a.status = 'no_show') / count(*), 1) AS no_show_pct
FROM appointments a
JOIN clinics c ON c.clinic_id = a.clinic_id
WHERE a.channel <> 'walk_in' AND a.status IN ('completed', 'no_show')
GROUP BY c.clinic_name
ORDER BY no_show_pct DESC;

SELECT c.clinic_name,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM a.started_at - a.check_in_at) / 60) AS median_wait_min
FROM appointments a
JOIN clinics c ON c.clinic_id = a.clinic_id
WHERE a.status = 'completed' AND a.check_in_at IS NOT NULL
GROUP BY c.clinic_name
ORDER BY median_wait_min DESC;

SELECT date_trunc('month', a.scheduled_at)::date AS month, sl.service_line_name,
       sum(s.quantity * s.unit_price) AS revenue
FROM appointment_services s
JOIN appointments a ON a.appointment_id = s.appointment_id
JOIN services sv ON sv.service_code = s.service_code
JOIN service_lines sl ON sl.service_line_id = sv.service_line_id
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

SELECT ins.insurer_name, r.reason_code, count(*) AS rejections
FROM claim_rejections r
JOIN invoices i ON i.invoice_id = r.invoice_id
JOIN insurers ins ON ins.insurer_id = i.insurer_id
GROUP BY 1, 2
ORDER BY 1, 3 DESC;