SQL: как определить достижения «Звезда школы» и «Хатико»

Условие

В школьной социальной сети «Звезда школы» — достижение за наибольшее число друзей. «Хатико» получает школьник с наибольшим числом отправленных заявок среди тех, у кого ни одна исходящая заявка не подтверждена. Выведите ID и название достижения.

Решение на PostgreSQL

Схема в условии не задана. Предположим, что есть students(id) и friend_requests(sender_id, receiver_id, status); участники заявок — школьники, подтверждённая дружба двусторонняя, статус confirmed. «Количество заявок» ниже означает число записей, а не уникальных адресатов. При равенстве максимума возвращаем всех победителей.

WITH friends AS (
    SELECT sender_id AS user_id, receiver_id AS friend_id
    FROM friend_requests
    WHERE status = 'confirmed' AND sender_id <> receiver_id
    UNION
    SELECT receiver_id, sender_id
    FROM friend_requests
    WHERE status = 'confirmed' AND sender_id <> receiver_id
), friend_counts AS (
    SELECT s.id AS user_id, COUNT(f.friend_id) AS n
    FROM students s
    LEFT JOIN friends f ON f.user_id = s.id
    GROUP BY s.id
), unconfirmed_senders AS (
    SELECT sender_id AS user_id, COUNT(*) AS n
    FROM friend_requests
    GROUP BY sender_id
    HAVING COUNT(*) FILTER (WHERE status = 'confirmed') = 0
)
SELECT user_id, 'Звезда школы' AS achievement
FROM friend_counts
WHERE n = (SELECT MAX(n) FROM friend_counts)
UNION ALL
SELECT user_id, 'Хатико' AS achievement
FROM unconfirmed_senders
WHERE n = (SELECT MAX(n) FROM unconfirmed_senders);

UNION в первом CTE устраняет повторные пары друзей. LEFT JOIN учитывает школьников без друзей: если друзей нет ни у кого, все делят максимум 0. Если по бизнес-правилу за нулевой результат достижение не выдают, добавьте AND n > 0 в первую итоговую выборку.

Если под «Хатико» понимается максимум исходящих заявок вообще среди всех школьников, а не максимум среди кандидатов без подтверждений, максимум нужно вычислять до фильтрации по подтверждениям. Эту неоднозначность стоит уточнить до решения.