old

WITH RECURSIVE oktmos ("id", parent_id) AS (
  SELECT o."id", o.parent_id
  FROM oktmo o
  WHERE o."id" = 71875000
  UNION ALL
  SELECT o."id", o.parent_id
  FROM oktmo o INNER JOIN oktmos ON (oktmos."id" = o.parent_id)
),
schools_in_municipality AS (
      SELECT sc."id", sc."shortname"
      FROM school sc
        JOIN address ad ON ad."id" = sc."address_id"
      WHERE ad.oktmo_id IN (SELECT oktmos."id" FROM oktmos)
)
SELECT
     s.id,
     s.shortname,
     st.name,
     st.number as stream_number,
     (SELECT count(cl.id) FROM school_class cl WHERE st.id = cl."schoolStream_id" AND cl.school_id = s.id AND cl.year_id=1) AS class_count,
     (SELECT count(c.id) FROM personal_card c WHERE c.stream_id = st.id AND c.school_id = s.id AND c.year_id=1 AND c.status='ACTIVE') AS student_count,
     (SELECT count(distinct addresses.child_id) FROM(
                                                      SELECT ch_a.id as child_id,ar.type,
                                                             (SELECT ar2.type FROM child_address_refine car2 JOIN address a2 ON a2.id = car2."addressList_id" JOIN address_refine ar2 on ar2.id=a2.id WHERE ar2.type='TEMP_REGISTRATION' AND car2.child_id = ch_a.id) AS temp_reg,
                                                             (select ar2.type from child_address_refine car2 JOIN address a2 ON a2.id = car2."addressList_id" JOIN address_refine ar2 on ar2.id=a2.id WHERE ar2.type='CURRENT_LOCATION' AND car2.child_id = ch_a.id) AS cur_loc
                                                      FROM child ch_a JOIN child_address_refine car on car.child_id = ch_a.id JOIN address a ON a.id = car."addressList_id" JOIN address_refine ar on ar.id=a.id where ar.type='REGISTRATION'
                                                    ) AS addresses JOIN personal_card c ON c.child_id = addresses.child_id JOIN child ch ON c.child_id = ch.id WHERE addresses.cur_loc IS NULL AND addresses.temp_reg IS NULL AND c.stream_id = st.id AND c.school_id = s.id  AND c.year_id=1 AND c.status='ACTIVE'
     ) AS residence,
     (SELECT count(distinct ch.id) FROM personal_card c JOIN child ch ON c.child_id = ch.id JOIN child_address_refine car ON car.child_id = ch.id JOIN address a ON a.id = car."addressList_id" JOIN address_refine ar ON ar.id=a.id where c.stream_id = st.id AND c.school_id = s.id AND (ar.type='TEMP_REGISTRATION' OR ar.type = 'CURRENT_LOCATION') AND c.year_id=1 AND c.status='ACTIVE') AS temp_reg,
     (SELECT count(c.id) FROM personal_card c JOIN child ch ON c.child_id = ch.id WHERE c.stream_id = st.id and c.school_id = s.id and ch.gender='MALE' AND c.year_id=1 AND c.status='ACTIVE') AS male_student,
     (SELECT count(c.id) FROM personal_card c JOIN child ch ON c.child_id = ch.id WHERE c.stream_id = st.id and c.school_id = s.id and ch.gender='FEMALE' AND c.year_id=1 AND c.status='ACTIVE') AS female_student
   FROM schools_in_municipality s CROSS JOIN ou_stream st
   WHERE st.number IN (1,2,3,4,5,6,7,8,9)