good query

200600

SELECT DISTINCT "applicants_emails"."id" AS t0_r0, "applicants_emails"."applicant_id" AS t0_r1, "applicants_emails"."email_id" AS t0_r2, "applicants_emails"."status_description" AS t0_r3, "applicants_emails"."created_at" AS t0_r4, "applicants_emails"."updated_at" AS t0_r5, "applicants"."id" AS t1_r0, "applicants"."ssn" AS t1_r1, "applicants"."created_at" AS t1_r2, "applicants"."updated_at" AS t1_r3, "applicants"."score" AS t1_r4, "applicants"."identifier" AS t1_r5, "applicants"."email" AS t1_r6, "applicants"."last_name" AS t1_r7, "applicants"."middle_name" AS t1_r8, "applicants"."first_name" AS t1_r9, "applicants"."suffix" AS t1_r10, "applicants"."title" AS t1_r11, "applicants"."last_refresh" AS t1_r12, "applicants"."date_added" AS t1_r13, "applicants"."cas_id" AS t1_r14, "applicants"."association_id" AS t1_r15, "applicants"."application_complete" AS t1_r16, "applicants"."verified" AS t1_r17, "applicants"."verified_date" AS t1_r18, "applicants"."professional_pin" AS t1_r19, "applicants"."match_id" AS t1_r20, "applicants"."pre_submission_release_authorized" AS t1_r21, "applicants"."photo_file_name" AS t1_r22, "applicants"."photo_content_type" AS t1_r23, "applicants"."photo_file_size" AS t1_r24, "applicants"."photo_updated_at" AS t1_r25, "applicants"."id_number" AS t1_r26, "applicants"."id_number_type" AS t1_r27, "applicants"."visible_for_advisor" AS t1_r28, "applicants"."direct_applicant_complete" AS t1_r29, "applicants"."email_type_id" AS t1_r30, "applicants"."dentpin" AS t1_r31, "applicants"."date_submitted" AS t1_r32, "emails"."id" AS t2_r0, "emails"."body" AS t2_r1, "emails"."created_at" AS t2_r2, "emails"."subject" AS t2_r3, "emails"."user_id" AS t2_r4, "emails"."bcc_user" AS t2_r5, "emails"."reply_to_user" AS t2_r6, "emails"."cc_user" AS t2_r7, "emails"."sent_at" AS t2_r8, "emails"."email_template_id" AS t2_r9, "emails"."enqueued_at" AS t2_r10, "emails"."user_identity_id" AS t2_r11, "emails"."updated_at" AS t2_r12, "emails"."priority" AS t2_r13, applicants_emails.id, applicants_emails.*, "designations".* FROM "applicants_emails" INNER JOIN "emails" ON "emails"."id" = "applicants_emails"."email_id" INNER JOIN "applicants" ON "applicants"."id" = "applicants_emails"."applicant_id" INNER JOIN "designations" ON "designations"."applicant_id" = "applicants"."id" LEFT OUTER JOIN "statuses" ON "statuses"."id" = "designations"."application_status_id" AND "statuses"."type" IN ('ApplicationStatus') WHERE "applicants_emails"."applicant_id" IN (SELECT DISTINCT applicants.id FROM "applicants" WHERE "applicants"."association_id" = 313 AND "applicants"."id" IN (SELECT "applicant_designation_details_mv"."applicant_id" FROM "applicant_designation_details_mv" WHERE "applicant_designation_details_mv"."program_id" IN (SELECT "user_identity_programs"."program_id" FROM "user_identity_programs" WHERE "user_identity_programs"."user_identity_id" = 209110) AND "applicant_designation_details_mv"."status_show" = 't')) AND "emails"."user_identity_id" IN (207631, 207887, 208060, 208161, 208261, 208352, 208463, 208603, 208773, 209083, 209110, 213014, 217538, 219549, 220432, 220435, 220444, 220451, 220459, 220473, 220477, 220484, 220491, 220496, 220503, 220517, 220540, 220547, 221925, 221926, 221927, 221928, 221929, 245118) AND "statuses"."show" = 't' AND "designations"."program_id" IN (SELECT "programs"."id" FROM "programs" INNER JOIN "user_identity_programs" ON "programs"."id" = "user_identity_programs"."program_id" WHERE "programs"."type" IN ('Program') AND "user_identity_programs"."user_identity_id" = 209110) AND "designations"."applicant_id" IN (SELECT DISTINCT applicants.id FROM "applicants" WHERE "applicants"."association_id" = 313 AND "applicants"."id" IN (SELECT "applicant_designation_details_mv"."applicant_id" FROM "applicant_designation_details_mv" WHERE "applicant_designation_details_mv"."program_id" IN (SELECT "user_identity_programs"."program_id" FROM "user_identity_programs" WHERE "user_identity_programs"."user_identity_id" = 209110) AND "applicant_designation_details_mv"."status_show" = 't')) AND "designations"."local_status_id" = 200600 ORDER BY "designations"."rank" ASC, "designations"."school_name" ASC, "applicants"."last_name", "emails"."created_at" DESC