Mock interview. You're interviewing for a senior data scientist job on a social app's friend suggestions team. The interviewer explains: "Our 'People you may know' list is mostly built from mutual friends. Let's explore the friend graph and build a simple version."
friendships stores each friendship once, with the smaller id in user_id and the larger in friend_id. To look at friendships from both sides, stack the table with its columns swapped.
"Start with the shape of the network. In one row: the number of users, the average and median number of friends, the most friends anyone has, and how many users have fewer than 5 friends."
Expected output
users, avg_friends (1 decimal), median_friends, max_friends, users_under_5; include every user in users, even with no friendsExample of the shape (made-up values):
| users | avg_friends | median_friends | max_friends | users_under_5 |
|---|---|---|---|---|
| 300 | 7.5 | 7 | 20 | 15 |
Hints, the later steps and the answer come with Pro.
Step 1 of 4
Write your query here. Ctrl + Enter submits.