gistfile1.txt

class MatchConversations
  def self.query(current_user)
    Match.find_by_sql(<<-SQL
      SELECT DISTINCT * FROM (
        SELECT
          matches.*,
          messages.created_at as messages_created_at,
          messages.read_at as messages_read_at,
          messages.reading as messages_reading,
          dense_rank() OVER (
            PARTITION BY matches.id
            ORDER BY
              CASE WHEN messages.read_at IS NULL THEN 0 ELSE 1 END,
              messages.created_at DESC
        ) AS message_rank
        FROM matches JOIN users
        ON matches.friend_id = users.id
        LEFT OUTER JOIN messages 
        ON users.id = messages.sender_id
        AND messages.receiver_id = #{current_user.id}
      ) AS sorted_matches
      WHERE user_id = #{current_user.id}
      AND status = 'accepted' AND message_rank = 1
      ORDER BY messages_read_at DESC, messages_reading DESC, messages_created_at, created_at DESC;
    SQL
    )
  end
end

The 'SELECT DISTINCT *' is a code smell, the nested SELECT is another. Without knowing the business usage or schema its hard to rewrite but my guess is the JOINS could be rewritten to return more unique result sets. That also leads me to believe the matches and messages tables could be normalized. Finally adding indexes to the columns you are ordering by may help depending on data type and how transactional those tables are.

Also not sure what table the 4th column being ordered on line 24 is. Is that a duplicate of messages_created_at?