Mail.ruПочтаМой МирОдноклассникиВКонтактеИгрыЗнакомстваНовостиКалендарьОблакоЗаметкиВсе проекты

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

Влад Нехаев Знаток (266), на голосовании 1 неделю назад
Любознательный сотрудник Яндекс Почты Амина задалась вопросом, а кто из пользователей сервиса общается активнее всех. Из-за большой загруженности на работе Амина так и не смогла найти время для решения этой задачи и поэтому просит вас ей помочь.

Вам дана таблица отправленных сообщений в Яндекс Почте:

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 в рамках определенной пары пользователей.
Голосование за лучший ответ
vk.com/mexahuk Мудрец (18828) 1 месяц назад
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;
Похожие вопросы