SELECT v.name as "Has Metadata",
(
  SELECT count(video_file_set_id) FROM mz_video_file videofile
  INNER JOIN mz_video_file_dimensions dimensions ON videofile.video_file_dimensions_id = dimensions.video_file_dimensions_id
  WHERE fileset.video_file_set_id = videofile.video_file_set_id AND videofile.video_file_type_id = 1 AND dimensions.width = 640 AND ( videofile.state_id = 1 or videofile.state_id = 2 )
) as has_preview,
(
  SELECT count(video_file_set_id) FROM mz_video_file videofile
  INNER JOIN mz_video_file_dimensions dimensions ON videofile.video_file_dimensions_id = dimensions.video_file_dimensions_id
  WHERE fileset.video_file_set_id = videofile.video_file_set_id AND videofile.video_file_type_id = 2 AND dimensions.width = 640 AND  ( videofile.state_id = 1 or videofile.state_id = 2 )
) as has_asset,
(
  SELECT ticket_state.name FROM mz_video_file videofile
  INNER JOIN mz_video_file_dimensions dimensions ON videofile.video_file_dimensions_id = dimensions.video_file_dimensions_id
  INNER JOIN mz_vf_qc_ticket ticket ON ticket.vf_qc_ticket_id = videofile.vf_qc_ticket_id
  INNER JOIN mz_vf_qc_ticket_state ticket_state ON ticket.vf_qc_ticket_state_id = ticket_state.vf_qc_ticket_state_id
  WHERE fileset.video_file_set_id = videofile.video_file_set_id AND videofile.video_file_type_id = 1 AND dimensions.width = 640 AND videofile.state_id = 2
) as qc_preview,
(
  SELECT ticket_state.name FROM mz_video_file videofile
  INNER JOIN mz_video_file_dimensions dimensions ON videofile.video_file_dimensions_id = dimensions.video_file_dimensions_id
  INNER JOIN mz_vf_qc_ticket ticket ON ticket.vf_qc_ticket_id = videofile.vf_qc_ticket_id
  INNER JOIN mz_vf_qc_ticket_state ticket_state ON ticket.vf_qc_ticket_state_id = ticket_state.vf_qc_ticket_state_id
  WHERE fileset.video_file_set_id = videofile.video_file_set_id AND videofile.video_file_type_id = 2 AND dimensions.width = 640 AND videofile.state_id = 2
) as qc_asset,
cp.short_name as content_provider_name, cpu.vendor_identifier,cpuset.salable_name, v.name, cpu.path, to_char(cpu.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified, e.reason, e.stack_trace, cpu.garbage_collection_date
FROM mz_content_provider_upload cpu
INNER JOIN mz_content_provider cp ON cpu.content_provider_id = cp.content_provider_id
INNER JOIN mz_cpu_set cpuset ON cpu.cpu_set_id = cpuset.cpu_set_id
LEFT JOIN mz_import_error e ON cpu.content_provider_upload_id = e.content_provider_upload_id
LEFT JOIN mz_video v ON cpu.vendor_identifier = v.vendor_identifier
LEFT JOIN mz_video_file_set fileset ON v.video_file_set_id = fileset.video_file_set_id
WHERE cp.short_name = 'Paramount'
ORDER BY has_asset, has_preview, v.name, last_modified