Напишите программу на Python

Question

Любознательный сотрудник Яндекс Почты Амина задалась вопросом, а кто из пользователей сервиса общается активнее всех. Из-за большой загруженности на работе Амина так и не смогла найти время для решения этой задачи и поэтому просит вас ей помочь. 
 
Вам дана таблица отправленных сообщений в Яндекс Почте: 
 
messages ( 
    message_id INTEGER NOT NULL, 
    sender_id INTEGER NOT NULL, 
    receiver_id INTEGER NOT NULL, 
    reply_message_id INTEGER 
); 
Где message_id — идентификатор отправленного сообщения (уникален в рамках общения любых двух пользователей), sender_id — идентификатор отправителя сообщения, receiver_id — идентификатор получателя сообщения (отличается от sender_id), reply_message_id — идентификатор сообщения, для которого данное сообщение является ответом (опциональное поле). Будем называть тредом такую цепочку из сообщений, где каждое последующее сообщение является ответом на предыдущее. В рамках одного треда сообщениями обмениваются только 2 пользователя. При этом у одного пользователя может быть несколько тредов как с разными пользователями, так и с одним. Для первого сообщения в треде reply_message_id пустой и равняется null. Рассмотрим пример:

message_id	sender_id	receiver_id	reply_message_id 
1	1	2	null 
3	2	1	1 
19	3	4	16 
16	4	3	1 
1	3	4	null 
4	1	2	3 
7	3	4	null 
17	4	3	7 
 
Пользователи  
Unexpected text node: ' 
                         
                    ' 
1 и  
Unexpected text node: ' 
                         
                    ' 
2 общались в рамках одного треда, который можно представить в виде цепочки из message_id:  
Unexpected text node: ' 
                         
                    ' 
1→3→4. Пользователи  
Unexpected text node: ' 
                         
                    ' 
3 и  
Unexpected text node: ' 
                         
                    ' 
4 общались в рамках двух тредов, которые также можно представить в виде цепочек из message_id:  
Unexpected text node: ' 
                         
                    ' 
1→16→19 и  
Unexpected text node: ' 
                         
                    ' 
7→17. 
Вашей задачей является создание запроса на SQLite, который для каждого пользователя посчитает максимальную длину треда, в котором он принимал участие (длиной треда является количество сообщений в нем). Результатом должна быть таблица с двумя колонками: user_id (идентификатор пользователя из sender_id/receiver_id) и max_thread_messages (максимальная длина треда для данного пользователя). Выходная таблица должна быть отсортирована по убыванию max_thread_messages (в случае равенства max_thread_messages предпочтение должно отдаваться пользователю с наименьшим user_id). 
 
Для примера выше ответом будет следующая таблица:

user_id	max_thread_messages 
1	3 
2	3 
3	3 
4	3 
 
Примечания 
Гарантируется, что одному сообщению может соответствовать только один ответ и для каждого непустого значения reply_message_id в таблице присутствует сообщение с соответствующим message_id в рамках определенной пары пользователей.

mexahuk6 · Answer

WITH thread_messages AS ( 
  SELECT 
    sender_id AS user_id, 
    COUNT(*) AS thread_length 
  FROM 
    messages 
  WHERE 
    reply_message_id IS NOT NULL 
  GROUP BY 
    sender_id, 
    receiver_id 
  UNION ALL 
  SELECT 
    receiver_id AS user_id, 
    COUNT(*) AS thread_length 
  FROM 
    messages 
  WHERE 
    reply_message_id IS NOT NULL 
  GROUP BY 
    sender_id, 
    receiver_id 
), 
max_thread_lengths AS ( 
  SELECT 
    user_id, 
    MAX(thread_length) AS max_thread_messages 
  FROM 
    thread_messages 
  GROUP BY 
    user_id 
) 
SELECT 
  user_id, 
  max_thread_messages 
FROM 
  max_thread_lengths 
ORDER BY 
  max_thread_messages DESC, 
  user_id ASC;