/* Тапсырма 2 Әлеуметтік желіде пайдаланушылар келесі жетістіктерге ие: 1. «Мектеп жұлдызы» — ең көп достары бар оқушыға беріледі. 2. «Хатико» — ең көп достасу сұрауын жіберген оқушыға беріледі, бірақ ешқандай оқушы достығын растамады. Жетістік алған оқушылардың идентификаторларын және олардың жетістігінің атауын шығарыңыз. */

Мәселені шешу үшін 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;

Бұл сұрау оқушылардың идентификаторларын және олардың жетістіктерінің атауларын шығарады.