SELECT ass.id, fin.id, ass.display_label, fin.finding_status CASE WHEN SUM( CASE WHEN fin.finding_status = 'Closed' THEN 0 ELSE 1 END ) OVER (PARTITION BY ass.id) > 0 THEN 'Failed' ELSE 'Passed' END as assmnt_status FROM "sds_datalake"."ei_publish_cloud_3"."findings" as fin LEFT JOIN "sds_datalake"."ei_publish_cloud_3"."findigs_associated_with_assessments" as ass_fin on fin.p_id = ass_fin.source_p_id LEFT JOIN "sds_datalake"."ei_publish_cloud_3"."assessments" as ass on ass_fin.target_p_id = ass.p_id`