/* Užduotis 2 Socialiniame tinkle naudotojai turi šiuos pasiekimus: 1. „Mokyklos žvaigždė“ skiriama mokiniui, turinčiam daugiausiai draugų. 2. „Hachiko“ skiriama mokiniui, kuris išsiuntė daugiausiai draugystės prašymų, tačiau nė vienas mokinys nepateikė patvirtinimo. Išveskite tų mokinių ID, kurie gavo pasiekimą, taip pat jų pasiekimo pavadinimą. */

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

• sender_id — кто отправил заявку
• receiver_id — кто получил заявку
• status — статус заявки (confirmed или pending)

-- 1. «Звезда школы»: школьник с наибольшим количеством подтверждённых друзей
WITH confirmed_friends AS (
  SELECT sender_id AS user_id FROM friend_requests WHERE status = 'confirmed'
  UNION ALL
  SELECT receiver_id AS user_id FROM friend_requests WHERE status = 'confirmed'
),
friend_counts AS (
  SELECT user_id, COUNT(*) AS friends_count
  FROM confirmed_friends
  GROUP BY user_id
),
max_friends AS (
  SELECT MAX(friends_count) AS max_count FROM friend_counts
),
star_school AS (
  SELECT user_id, 'Звезда школы' AS achievement
  FROM friend_counts, max_friends
  WHERE friends_count = max_count
),

-- 2. «Хатико»: школьник, который отправил наибольшее количество заявок, но ни одна не подтверждена
unconfirmed_requests AS (
  SELECT sender_id AS user_id, COUNT(*) AS sent_count
  FROM friend_requests
  WHERE status != 'confirmed'
  GROUP BY sender_id
),
confirmed_requests AS (
  SELECT sender_id AS user_id, COUNT(*) AS confirmed_count
  FROM friend_requests
  WHERE status = 'confirmed'
  GROUP BY sender_id
),
hachiko_candidates AS (
  SELECT u.user_id, u.sent_count
  FROM unconfirmed_requests u
  LEFT JOIN confirmed_requests c ON u.user_id = c.user_id
  WHERE COALESCE(c.confirmed_count, 0) = 0
),
max_unconfirmed AS (
  SELECT MAX(sent_count) AS max_sent FROM hachiko_candidates
),
hachiko AS (
  SELECT user_id, 'Хатико' AS achievement
  FROM hachiko_candidates, max_unconfirmed
  WHERE sent_count = max_sent
)

SELECT * FROM star_school
UNION ALL
SELECT * FROM hachiko;

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