select
  i.institution_guid,
  j.job_guid,
  j.started_at,
  coalesce(j.finished_at, j.updated_at) as finished_at,
  j.status as final_status_type,
  jsc.final_event as final_event_type,
  coalesce(ceiling(date_part('second', coalesce(j.finished_at, j.updated_at) - j.started_at)), -1) as job_duration
FROM
	jobs j
	inner join members m on j.member_id = m.id
	inner join institutions i on m.institution_id = i.id
	inner join (select job_id, max(event) as final_event from job_status_changes group by job_id, event) jsc on jsc.job_id = j.id
where
	(j.finished_at is not null or j.updated_at is not null)
	and jsc.final_event in (3,4,5,6,7,8,9)
order by job_duration;