adamsanadi6
commented
8 months ago Old Query:
with latest_program_eligibility_encounter as ( select s.id student_id, pel."Programs interested in" as programs_interested_in, pel."HCL TechBee Status-Old" as hcl_techbee_status, pel."Masai School Status" as masai_status, pel."TechM - Status" as techm_status, pel."NavGurkul - Status" as navgurukul_status, pel."MagicB - Status" as magic_billion_status, row_number() over (partition by pel.individual_id order by pel.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_program_eligibility pel on pel.individual_id = s.id ), programs_interested_state_wise AS ( SELECT DISTINCT UNNEST(string_to_array(pel."Programs interested in", ', ')) AS programs, a."State" state from lahi.student s left join lahi.student_program_eligibility pel on pel.individual_id = s.id inner join lahi.address a on a.id = s.address_id ), latest_hcl_techbee_encounter as ( select s.id student_id, enc."HCL TechBee Status" as hcl_techbee_status, row_number() over (partition by enc.individual_id order by enc.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_hcl_techbee enl on enl.individual_id = s.id inner join lahi.student_hcl_techbee_hcl_techbee_encounter enc on enc.individual_id = s.id ), latest_masai_school_encounter as ( select s.id student_id, enc."MasaiSchool status" masai_school_status, row_number() over (partition by enc.individual_id order by enc.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_masai_school enl on enl.individual_id = s.id inner join lahi.student_masai_school_masai_school_followup enc on enc.individual_id = s.id ), latest_techm_encounter as ( select s.id student_id, enc."Tech Mahindra Status" techm_status, row_number() over (partition by enc.individual_id order by enc.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_techmahindra enl on enl.individual_id = s.id inner join lahi.student_techmahindra_techmahindra_followup enc on enc.individual_id = s.id ), latest_navgurukul_encounter as ( select s.id student_id, enc."NavGurukul Status" navgurukul_status, row_number() over (partition by enc.individual_id order by enc.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_navgurukul enl on enl.individual_id = s.id inner join lahi.student_navgurukul_navgurukul_followup enc on enc.individual_id = s.id ), latest_magic_b_encounter as ( select s.id student_id, enc."Magic billion status" magic_billion_status, row_number() over (partition by enc.individual_id order by enc.encounter_date_time desc nulls last) as visit_number from lahi.student s inner join lahi.student_magic_billion enl on enl.individual_id = s.id inner join lahi.student_magic_billion_magic_billion_encounter enc on enc.individual_id = s.id ) select a."State" , count(distinct s.id) filter (where s.gender = 'Female') as "Total females registered", count(distinct s.id) filter (where s.gender = 'Male') as "Total males registered", count(distinct s.id) filter (where s.gender = 'Other') as "Total other registered", count(distinct s.id) as "Total students registered", count(distinct s.id) filter (where s.gender = 'Female' and s."User" = 'Vindhya Team' and s."Status" = 'Completed') as "Vindhya survey completed for females", count(distinct s.id) filter (where s.gender = 'Male' and s."User" = 'Vindhya Team' and s."Status" = 'Completed') as "Vindhya survey completed for males", count(distinct s.id) filter (where s.gender = 'Other' and s."User" = 'Vindhya Team' and s."Status" = 'Completed') as "Vindhya survey completed for others", count(distinct s.id) filter (where s."User" = 'Vindhya Team' and s."Status" = 'Completed') as "Total Vindhya survey completed", count(distinct s.id) filter (where s.gender = 'Female' and s."User" = 'Vindhya Team' and s."Status" = 'Inprogress/Ongoing') as "Vindhya survey Inprogress/Ongoing for females", count(distinct s.id) filter (where s.gender = 'Male' and s."User" = 'Vindhya Team' and s."Status" = 'Inprogress/Ongoing') as "Vindhya survey Inprogress/Ongoing for males", count(distinct s.id) filter (where s.gender = 'Other' and s."User" = 'Vindhya Team' and s."Status" = 'Inprogress/Ongoing') as "Vindhya survey Inprogress/Ongoing for others", count(distinct s.id) filter (where s."User" = 'Vindhya Team' and s."Status" = 'Inprogress/Ongoing') as "Total Vindhya survey Inprogress/Ongoing", count(distinct s.id) filter (where s.gender = 'Female' and s."User" = 'Vindhya Team' and s."Status" = 'Dropped out') as "Vindhya survey Dropped out for females", count(distinct s.id) filter (where s.gender = 'Male' and s."User" = 'Vindhya Team' and s."Status" = 'Dropped out') as "Vindhya survey Dropped out for males", count(distinct s.id) filter (where s.gender = 'Other' and s."User" = 'Vindhya Team' and s."Status" = 'Dropped out') as "Vindhya survey Dropped out for others", count(distinct s.id) filter (where s."User" = 'Vindhya Team' and s."Status" = 'Dropped out') as "Total Vindhya survey Dropped out", count(distinct s.id) filter (where s.gender = 'Female' and usa.user_id is not null) as "Volunteers assigned to females", count(distinct s.id) filter (where s.gender = 'Male' and usa.user_id is not null) as "Volunteers assigned to males", count(distinct s.id) filter (where s.gender = 'Other' and usa.user_id is not null) as "Volunteers assigned to others", count(distinct s.id) filter (where usa.user_id is not null) as "Total Volunteers assigned", count(distinct pisw.programs) as "Programs interested in", count(distinct s.id) filter (where s.gender = 'Female' and pel.programs_interested_in is not null) as "No. of females assigned to programs", count(distinct s.id) filter (where s.gender = 'Male' and pel.programs_interested_in is not null) as "No. of males assigned to programs", count(distinct s.id) filter (where s.gender = 'Other' and pel.programs_interested_in is not null) as "No. of others assigned to programs", count(distinct s.id) filter (where pel.programs_interested_in is not null) as "Total students assigned to programs", count(distinct s.id) filter (where s.gender = 'Female' and 'Applied' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Applied females", count(distinct s.id) filter (where s.gender = 'Male' and 'Applied' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Applied males", count(distinct s.id) filter (where s.gender = 'Other' and 'Applied' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Applied others", count(distinct s.id) filter (where 'Applied' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Total applied students", count(distinct s.id) filter (where s.gender = 'Female' and 'In process' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "In process females", count(distinct s.id) filter (where s.gender = 'Male' and 'In process' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "In process males", count(distinct s.id) filter (where s.gender = 'Other' and 'In process' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "In process others", count(distinct s.id) filter (where 'In process' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Total In process students", count(distinct s.id) filter (where s.gender = 'Female' and 'Selected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Selected females", count(distinct s.id) filter (where s.gender = 'Male' and 'Selected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Selected males", count(distinct s.id) filter (where s.gender = 'Other' and 'Selected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Selected others", count(distinct s.id) filter (where 'Selected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Total Selected students", count(distinct s.id) filter (where s.gender = 'Female' and 'Rejected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Rejected females", count(distinct s.id) filter (where s.gender = 'Male' and 'Rejected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Rejected males", count(distinct s.id) filter (where s.gender = 'Other' and 'Rejected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Rejected others", count(distinct s.id) filter (where 'Rejected' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Total Rejected students", count(distinct s.id) filter (where s.gender = 'Female' and 'Dropped Out / Not Interested Anymore' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Dropped Out / Not Interested females", count(distinct s.id) filter (where s.gender = 'Male' and 'Dropped Out / Not Interested Anymore' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Dropped Out / Not Interested males", count(distinct s.id) filter (where s.gender = 'Other' and 'Dropped Out / Not Interested Anymore' in (hcl.hcl_techbee_status,masai.masai_school_status, techm.techm_status,nav.navgurukul_status,magic.magic_billion_status, pel.programs_interested_in) ) as "Dropped Out / Not Interested others", count(distinct s.id) filter (where 'Dropped Out / Not Interested Anymore' in ( hcl.hcl_techbee_status, masai.masai_school_status, techm.techm_status, nav.navgurukul_status, magic.magic_billion_status, pel.programs_interested_in) ) as "Total Dropped Out / Not Interested students" from lahi.student s inner join lahi.address a on a.id = s.address_id left join user_subject_assignment usa on usa.subject_id = s.id left join latest_program_eligibility_encounter pel on pel.student_id = s.id and pel.visit_number = 1 left join latest_hcl_techbee_encounter hcl on hcl.student_id = s.id and hcl.visit_number = 1 left join latest_masai_school_encounter masai on masai.student_id = s.id and masai.visit_number = 1 left join latest_techm_encounter techm on techm.student_id = s.id and techm.visit_number = 1 left join latest_navgurukul_encounter nav on nav.student_id = s.id and nav.visit_number = 1 left join latest_magic_b_encounter magic on magic.student_id = s.id and magic.visit_number = 1 left join programs_interested_state_wise pisw on pisw.state = a."State" where s.is_voided is false group by 1;
Dinesh2019
Existing Structure:
Proposed Structure: