new

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)
),
fs 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)
),
fs_st AS(
	SELECT fs.id AS school_id, fs.shortname, st.id AS st_id, st.name, st.number as stream_number from fs 
	CROSS JOIN ou_stream st
	WHERE st.number NOT IN (0)
),
fs_st_con AS(
 	SELECT pc.id, pc.school_id, pc.stream_id, ch.id AS child_id, ch.gender FROM personal_card pc
 	JOIN fs_st ON fs_st.school_id=pc.school_id AND fs_st.st_id = pc.stream_id
	JOIN child ch ON pc.child_id = ch.id
 	WHERE pc.status = 'ACTIVE' AND pc.year_id=1
),
f_m AS (SELECT school_id,stream_id,SUM(male) AS male_student,SUM(female) AS female_student 
	FROM(
		SELECT fs_st_con.school_id,fs_st_con.stream_id,
		CASE  
			WHEN fs_st_con.gender='MALE' THEN 1
			ELSE 0
		END AS male,
		CASE  
			WHEN fs_st_con.gender='FEMALE'THEN 1
			ELSE 0
		END AS female
		FROM fs_st_con
	)a GROUP BY school_id,stream_id 
),
temp_reg AS (
	SELECT c.school_id, c.stream_id, COUNT(distinct c.id) as temp_reg 
	FROM fs_st_con 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 (ar.type='TEMP_REGISTRATION' OR ar.type = 'CURRENT_LOCATION') GROUP BY c.school_id,c.stream_id
)
SELECT fs_st.school_id, fs_st.shortname, fs_st.name, fs_st.stream_number AS stream_number,
COALESCE(f_m.male_student, 0) AS male_student, COALESCE(f_m.female_student, 0) AS female_student,
COALESCE(temp_reg.temp_reg, 0) AS temp_reg,
COALESCE(f_m.male_student + f_m.female_student - COALESCE(temp_reg.temp_reg, 0), 0) AS residence,
COALESCE(f_m.male_student+f_m.female_student, 0) AS student_count,
(SELECT COUNT(sc.id) FROM school_class sc WHERE sc.school_id = fs_st.school_id AND sc."schoolStream_id" = fs_st.st_id AND sc.year_id = 1) AS class_count
FROM fs_st  LEFT JOIN f_m ON fs_st.school_id=f_m.school_id AND fs_st.st_id=f_m.stream_id
LEFT JOIN temp_reg ON fs_st.school_id=temp_reg.school_id AND fs_st.st_id=temp_reg.stream_id
ORDER BY fs_st.shortname, fs_st.stream_number