tickets_priority.sql

SELECT *
FROM tickets
INNER JOIN (
  SELECT DISTINCT ON (ticket_id), ticket_id, first_message_response_time
  FROM tags_tickets
  INNER JOIN tags ON (tags_tickets.tag_id = tags.id)
  ORDER BY ticket_id, first_message_response_time
) first_message_response_times
ON (tickets.id = first_message_response_times.ticket_id)
INNER JOIN (
  SELECT DISTINCT ON (ticket_id), ticket_id, common_message_response_time
  FROM tags_tickets
  INNER JOIN tags ON (tags_tickets.tag_id = tags.id)
  ORDER BY ticket_id, common_message_response_time
) common_message_response_times
ON (tickets.id = common_message_response_times.ticket_id)
INNER JOIN (
  SELECT DISTINCT ON (ticket_id) ticket_id, created_at
  FROM ticket_events
  WHERE kind = 'message'
  ORDER BY id, created_at ASC ) first_messages
ON (tickets.id = first_messages.ticket_id)
INNER JOIN (
  SELECT DISTINCT ON (ticket_id) ticket_id, created_at
  FROM ticket_events
  WHERE kind = 'message'
  ORDER BY id, created_at DESC ) last_messages
ON (tickets.id = last_messages.ticket_id)
ORDER BY
  IF first_messages.created_at = last_messages.created_at
    THEN (now() + first_message_response_time - first_messages.created_at)
    ELSE (now() + common_message_response_time - last_messages.created_at)
  END IF