Temă 1 Afișați ID-urile tuturor elevilor care au trimis cereri de prietenie celor care studiază cu 2 clase mai mici. Temă 2 În rețeaua socială, utilizatorii au următoarele realizări disponibile: 1. „Steaua școlii” este acordată elevului care are cel mai mare număr de prieteni. 2. „Hachiko” este acordată elevului care a trimis cel mai mare număr de cereri de prietenie, dar niciuna nu a fost confirmată de alți elevi. Afișați ID-urile elevilor care au primit realizări, precum și numele realizărilor lor.

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

• 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 школьников и их достижения.