Intern

/* Вазифа 2 Дар шабакаи иҷтимоӣ, корбарон соҳиби дастовардҳои зерин мешаванд: 1. «Ситораи мактаб» ба донишҷӯе дода мешавад, ки бештар дӯст дорад. 2. «Хатико» ба донишҷӯе дода мешавад, ки бештар дархостҳои дӯстӣ ирсол кардааст, аммо ҳеҷ яке аз донишҷӯён дӯстиро тасдиқ накардааст. ID-ҳои донишҷӯёне, ки дастовардро гирифтаанд, ва номи дастовардҳои онҳо нишон диҳед. */

sobes.tech AI

Ҷавоб аз AI

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

Гузашта Оянда