/* Uzdevums 2 Sociālajā tīklā lietotājiem ir šādi sasniegumi: 1. "Skolas zvaigzne" tiek piešķirta skolēnam ar visvairāk draugiem. 2. "Hachiko" tiek piešķirta skolēnam, kurš nosūtījis visvairāk draudzības pieprasījumu, bet neviens no skolēniem nav apstiprinājis draudzību. Izvadiet skolēnu ID, kuri saņēmuši sasniegumu, kā arī viņu sasnieguma nosaukumu. */

Для решения задачи можно использовать 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 школьников и название их достижения.