Решение задачи

Тема: SQL

Решение:

WITH friends (user1, user2) 
AS (VALUES (1, 2), (1, 3), (1, 4), (2, 3)),

-- compile all user appearances into one column, preserving duplicate entries with UNION ALL 
t1 AS (
SELECT user1 AS user_id
FROM friends
UNION ALL
SELECT user2 AS user_id
FROM friends)
-- grouping by user ID, count up all appearances of that user
SELECT 
   user_id, 
   COUNT(*) AS friend_count
FROM t1
GROUP BY 1
ORDER BY 2 DESC