MySQL - Calculate average number of friends per user

I came across this very interesting problem while practicing SQL -

Calculate the average number of friends a user has using a table which has - user ID and friend ID columns.

First thing that came to my mind was - Just take the average of number of friends per user.

But no, thats not it. Before thinking of syntax and what query to write, it is important to understand what does it mean by average friends per user?

Let us take this example table:

Table: UsersFriends

This table implies that each user can have multiple friends, and each relationship between user and friend is stored as a row.

Now, to calculate the average friends per user , here are the two steps

1. Count the friends of each user - how many friends does each user have?
2. Calculate the average number of friends for each user's friends - Tricky part!

Step 2 basically means that to calculate the average of friends per user, you first need the number of friends of that user's friends. For e.g.

UserID 1 friends are 2 and 3 (Step 1)

Now we calculate the friends of 2 which are 1 and 3; friends of 3 are 1 and 2

So UserID 1's friends' friends count = 4 (Step 2)

So, the average friends that UserID 1 would have is:

UserID 1's friends' friends / UserID's 1's friends

i.e. 4/2 - 2.0

Now , lets go to the syntax of query:

Step 1: Count the number of friends for each user. Let us create a CTE.

WITH FriendCount AS (

SELECT UserID AS FriendID, COUNT(*) AS NumFriends

FROM UserFriends

GROUP BY UserID

)

Step 2: Using this CTE, join it to the main table to get friends' friends count and the average:

SELECT uf.UserID, AVG(fc.NumFriends) AS AvgFriendsOfFriends

FROM UserFriends uf

JOIN FriendCount fc

ON uf.FriendID = fc.FriendID

GROUP BY uf.UserID;

And here is the output:

Try doing this with more data and just following the two simple steps!

I found this really interesting! Did you? :)