select project, uri subject_uri, var_patient_uri
from (
 select agg.*, detail.uri from (
  select 'proms_consent' as project, created, q1.var_patient_uri, q1.var_registration, var_sys_status_label
       , var_sys_date_submitted
       , row_number() over (partition by q1.var_patient_uri order by var_sys_status, var_sys_date_submitted asc, created desc) from proms_consent.cases q1
  join (
  select count(1), var_patient_uri, var_moment, var_registration,var_id from proms_consent.cases  group by 2,3,4,5 having count(1) > 1
  ) q2
  on q1.var_patient_uri = q2.var_patient_uri and q2.var_registration = q2.var_registration
    ) agg
  join proms_consent.cases detail
    on agg.var_patient_uri = detail.var_patient_uri
   and agg.created = detail.created
  where row_number = 1
) q3