Friend Count

For this challenge, suppose we are working on an app similar to Facebook Messenger, called "Footbook Messenger". You're a data scientist studying social graphs. You want to cluster users by their social behavior, and compare users by how many friends they have.

The users are in the fb_users table, and their friendships are in friendships.

Table Name:

fb_users

`column_name`	`type`
id	INTEGER
name	VARCHAR
username	VARCHAR
email	VARCHAR
phone_number	VARCHAR

Table Name:

friendships

`column_name`	`type`	`reference`
friend_id_1	INTEGER	fb_users.id
friend_id_2	INTEGER	fb_users.id

When 2 users become friends, 2 rows in the friendships table get created, i.e. when Bob befriends Alice, there is 1 row where Bob's ID is friend_id_1 and Alice's is friend_id_2, and another row where Alice's ID is friend_id_1 and Bob's is friend_id_2.

Your task is to determine the distribution of friend counts, e.g. how many people have 5 friends, how many have 4 friends, etc. If you visualized it with a bar graph, it would look something like this:

Select the friend count, aliased to friend_count, and the number of users with that friend count, aliased it to users_having_friend_count. Order by the friend_count.

Next
Tweeter Features

Try running a query…

	`friend_count` `BIGINT`	`users_having_friend_count` `BIGINT`
1	2	2
2	3	5
3	4	2
4	5	6
5	6	3
6	7	6
7	9	1
7 rows

You haven't solved this challenge yet!
Are you sure you want to reveal the answer?

SELECT
  friend_count,
  count(*) AS users_having_friend_count
FROM ( 
  SELECT
    id,
    count(*) AS friend_count
  FROM fb_users f
  JOIN friendships fs
    ON f.id = fs.friend_id_1
  GROUP BY id
) count_friends
GROUP BY friend_count
ORDER BY friend_count;