---

WITH x AS (
    SELECT lr.id lr_id,
           lo.patient_id p_id,
           lo.source_org_id org_id,
           o.text org_name,
           lo.source_dep_id dep_id,
           d.text dep_name,
           s.id serv_id,
           s.code serv_code,
           s.text serv_name,
           CASE
               WHEN p.birth_date IS NULL THEN 'unknown'
               WHEN EXTRACT(YEAR FROM ls.collect_date) - EXTRACT(YEAR FROM p.birth_date)
                    - CASE WHEN TO_CHAR(ls.collect_date, 'MMDD') < TO_CHAR(p.birth_date, 'MMDD')
                           THEN 1 ELSE 0 END < 18 THEN 'child'
               ELSE 'adult'
           END age_group
    FROM nlab.lab_research lr
    JOIN nlab.lab_sample ls ON ls.id = lr.lab_sample_id
    JOIN nlab.lab_order lo ON lo.id = ls.lab_order_id
    LEFT JOIN nlab.patient p ON p.id = lo.patient_id
    LEFT JOIN nlab.serv s ON s.id = lr.serv_id
    LEFT JOIN nlab.organization o ON o.id = lo.source_org_id
    LEFT JOIN nlab.dep d ON d.id = lo.source_dep_id
    WHERE ls.collect_date >= DATE '2026-01-01'
      AND ls.collect_date < DATE '2027-01-01'
      AND COALESCE(lr.cancel_status, 0) = 0
      AND COALESCE(ls.cancel_status, 0) = 0
      AND (UPPER(o.text) LIKE '%ПЕРИНАТАЛЬНЫЙ ЦЕНТР № 10%'
           OR d.text = 'РД 10'
           OR d.text LIKE 'РД 10 %')
)
SELECT org_id, org_name, dep_id, dep_name,
       serv_id, serv_code, serv_name,
       COUNT(*) research_count,
       SUM(CASE WHEN age_group = 'child' THEN 1 ELSE 0 END) child_count,
       SUM(CASE WHEN age_group = 'adult' THEN 1 ELSE 0 END) adult_count,
       SUM(CASE WHEN age_group = 'unknown' THEN 1 ELSE 0 END) unknown_count,
       COUNT(DISTINCT p_id) patient_count
FROM x
GROUP BY org_id, org_name, dep_id, dep_name, serv_id, serv_code, serv_name
ORDER BY dep_name, serv_name;