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

Для решения задачи предположим, что есть таблицы:

• students(id, grade) — школьники с их классом.
• friend_requests(sender_id, receiver_id) — заявки в друзья.
• friends(student1_id, student2_id) — подтверждённые дружбы.

Задание 1: Нужно найти id школьников, которые отправили заявки тем, кто учится на 2 класса младше.

SELECT DISTINCT fr.sender_id
FROM friend_requests fr
JOIN students s_sender ON fr.sender_id = s_sender.id
JOIN students s_receiver ON fr.receiver_id = s_receiver.id
WHERE s_receiver.grade = s_sender.grade - 2;

Задание 2:

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

-- Количество друзей каждого школьника
WITH friend_counts AS (
  SELECT student_id, COUNT(*) AS friends_num FROM (
    SELECT student1_id AS student_id FROM friends
    UNION ALL
    SELECT student2_id AS student_id FROM friends
  ) AS all_friends
  GROUP BY student_id
),

-- Количество отправленных заявок и подтверждённых дружб
requests AS (
  SELECT fr.sender_id,
         COUNT(*) AS sent_requests,
         COUNT(f.student1_id) AS confirmed_friends
  FROM friend_requests fr
  LEFT JOIN friends f ON (fr.sender_id = f.student1_id AND fr.receiver_id = f.student2_id) OR (fr.sender_id = f.student2_id AND fr.receiver_id = f.student1_id)
  GROUP BY fr.sender_id
),

max_friends AS (
  SELECT MAX(friends_num) AS max_friends_num FROM friend_counts
),

max_requests AS (
  SELECT MAX(sent_requests) AS max_sent_requests FROM requests WHERE confirmed_friends = 0
)

SELECT fc.student_id, 'Звезда школы' AS achievement
FROM friend_counts fc, max_friends mf
WHERE fc.friends_num = mf.max_friends_num

UNION ALL

SELECT r.sender_id, 'Хатико' AS achievement
FROM requests r, max_requests mr
WHERE r.sent_requests = mr.max_sent_requests AND r.confirmed_friends = 0;

Этот запрос выведет id школьников и их достижения.