Meta-style Company ChallengeHardVerified answerSQLite live

Friendship Network Analysis

For each user with at least one accepted friend, show their friend count, their largest mutual-friend count with any direct friend, and their network rank.

  • Window functions
  • Joins
  • Subqueries
  • Aggregation
  • Type conversion

Challenge brief

Understand the request

Social Graph Team is building friend-count badges for the profile page and needs accepted connection counts and network rank for every connected user.

Compute accepted friend count per user and rank by network size, handling bidirectional friendships with UNION ALL.

Return

  • username
  • total_friends
  • max_mutual_friends (max mutual connections with any single friend)
  • network_rank (RANK by friends desc)

Constraints

  • Treat each accepted friendship as bidirectional
  • Return each user’s total friend count, maximum mutual-friend count with any direct friend, and network rank
  • Users with equal network metrics share a rank; then order by username

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

users

  • user_idINTEGER
  • usernameVARCHAR(50)

friendships

  • friendship_idINTEGER
  • user_id_1INTEGER
  • user_id_2INTEGER
  • statusVARCHAR(20)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The friendships table stores each pair once. To count both sides, UNION ALL: SELECT user_id_1 AS user_id, user_id_2 AS friend_id UNION ALL SELECT user_id_2, user_id_1. This makes the graph bidirectional. Then GROUP BY user_id and COUNT.

Hint 2

CTE user_friends: UNION ALL of both directions WHERE status = 'accepted'. CTE friend_counts: COUNT per user. RANK() OVER (ORDER BY total_friends DESC, mutual_friends DESC).

Hint 3

Start with the tables that establish the result grain for question 12, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH user_friends AS (SELECT user_id_1 AS user_id, user_id_2 AS friend_id FROM friendships WHERE status = 'accepted' UNION ALL SELECT user_id_2 AS user_id, user_id_1 AS friend_id FROM friendships WHERE status = 'accepted'), friend_counts AS (SELECT user_id, COUNT(friend_id) AS total_friends FROM user_friends GROUP BY user_id), mutual_friends AS (SELECT uf1.user_id, uf1.friend_id, COUNT(DISTINCT uf3.friend_id) AS mutual_count FROM user_friends uf1 INNER JOIN user_friends uf2 ON uf1.friend_id = uf2.user_id INNER JOIN user_friends uf3 ON uf1.user_id = uf3.user_id AND uf2.friend_id = uf3.friend_id WHERE uf2.friend_id != uf1.user_id GROUP BY uf1.user_id, uf1.friend_id), max_mutuals AS (SELECT user_id, MAX(mutual_count) AS max_mutual_friends FROM mutual_friends GROUP BY user_id) SELECT u.username, fc.total_friends, CAST(COALESCE(mm.max_mutual_friends, 0) AS INTEGER) AS max_mutual_friends, RANK() OVER (ORDER BY fc.total_friends DESC, COALESCE(mm.max_mutual_friends, 0) DESC) AS network_rank FROM users u INNER JOIN friend_counts fc ON u.user_id = fc.user_id LEFT JOIN max_mutuals mm ON u.user_id = mm.user_id ORDER BY network_rank, u.username;

Why this works

Without UNION ALL, a user who always appears as user_id_2 would miss some friendships. UNION ALL symmetrises the graph. All 4 active users have exactly 2 accepted friends so they all tie at rank 1.

Success check

4 users all tied at 2 friends and network_rank = 1

Expected result

Use this output to verify values, aliases, ordering, and row count.

usernametotal_friendsmax_mutual_friendsnetwork_rank
jane_smith201
john_doe201
mike_wilson201
sarah_jones201

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.