Любознательный сотрудник Яндекс Почты Амина задала...

Ниже приведён один из вариантов решения на чистом SQL (SQLite) с использованием рекурсивного CTE. Основная идея:

1. Выделяем «корни» всех тредов — сообщения, у которых reply_message_id IS NULL. Это начало каждой цепочки.
2. Рекурсивно «протягиваем» цепочку, подсоединяя к каждому сообщению то, которое отвечает именно на него, причём среди тех же двух пользователей (так как по условию один тред не выходит за рамки пары).
3. Для каждого «корневого» сообщения (т. е. для каждого треда) считаем его длину как MAX(depth) – «самая дальняя» глубина в рекурсивной цепочке.
4. В конце полученную длину «раздаём» обоим участникам треда (и отправителю, и получателю), после чего берём MAX по всем тредам для каждого пользователя.
5. Сортируем результат по убыванию длины треда, а при равенстве – по возрастанию user_id.

Пример запроса (названия столбцов и таблицы можно при необходимости подстроить под свои):

sql
WITH RECURSIVE chain AS (
    -- 1) Берём "корневые" сообщения, у которых нет reply_message_id.
    SELECT
        message_id,
        sender_id,
        receiver_id,
        reply_message_id,
        message_id AS root_id,  -- идентификатор «корня» (начала) треда
        1 AS depth              -- начальная глубина
    FROM messages
    WHERE reply_message_id IS NULL

    UNION ALL

    -- 2) Рекурсивно цепляем все ответы, которые продолжают ту же пару пользователей
    SELECT
        m.message_id,
        m.sender_id,
        m.receiver_id,
        m.reply_message_id,
        c.root_id,
        c.depth + 1
    FROM messages m
    JOIN chain c
       ON m.reply_message_id = c.message_id
       -- Один и тот же тред продолжается только у той же пары пользователей,
       -- вне зависимости от того, кто отправляет/получает.
       AND (
           (m.sender_id   = c.sender_id   AND m.receiver_id = c.receiver_id)
        OR (m.sender_id   = c.receiver_id AND m.receiver_id = c.sender_id)
       )
),
-- 3) Находим длину каждого треда (максимальную глубину для каждого root_id).
thread_length AS (
    SELECT
        root_id,
        MAX(depth) AS len
    FROM chain
    GROUP BY root_id
),
-- 4) "Раздаём" длину треда каждому пользователю, который был в этом треде.
user_thread AS (
    SELECT sender_id AS user_id, root_id
    FROM chain
    UNION
    SELECT receiver_id AS user_id, root_id
    FROM chain
),
-- Для каждого пользователя берём максимум из всех тредов, в которых он участвовал.
user_max_thread AS (
    SELECT
        user_id,
        MAX(len) AS max_thread_messages
    FROM user_thread
    JOIN thread_length USING (root_id)
    GROUP BY user_id
)
-- 5) Итоговая выборка с нужной сортировкой
SELECT
    user_id,
    max_thread_messages
FROM user_max_thread
ORDER BY
    max_thread_messages DESC,
    user_id ASC;

Краткое пояснение по шагам

1. CTE chain

   • Первая часть SELECT ... FROM messages WHERE reply_message_id IS NULL забирает все «начальные» сообщения тредов (где нет ответа на предыдущее). Каждому такому сообщению присваиваем root_id = message_id и depth = 1.
   • Вторая часть UNION ALL рекурсивно ищет сообщения, у которых reply_message_id совпадает с message_id из уже взятой строки. При этом мы проверяем, что новое сообщение идёт в общении тех же двух пользователей (порядок sender_id/receiver_id может меняться).
   • При переходе на «следующий уровень» глубину увеличиваем на 1.

2. CTE thread_length
   Для каждой цепочки (идентифицируемой полем root_id) считаем максимальную глубину depth. Это и есть длина данного треда.

3. CTE user_thread
   Каждый пользователь в цепочке может быть либо отправителем (sender_id), либо получателем (receiver_id). Чтобы учесть оба случая, делаем UNION и выписываем (пользователь, номер треда).

4. CTE user_max_thread
   Объединяем полученный набор (user_id, root_id) с длинами тредов (по root_id), берём MAX(len) – максимальную длину треда, в котором данный пользователь участвовал.

5. Итоговый SELECT
   Выбираем (user_id, max_thread_messages) и сортируем по убыванию длины треда, а при равенстве – по возрастанию идентификатора пользователя.

Такой запрос вернёт именно ту структуру, которую просит задача: для каждого пользователя (участвующего в таблице messages) — максимальную длину треда и нужную сортировку на выходе.