subqueries within same table?

[dstroma]

Hi,

I have a MySQL table that lists users of a web site and the users that referred them (if applicable).

Like this:

USER - REFERRER
10 - 1
11 - 1
12 - 2
13 - 10
14- 12
15- 12

To count how many referrals a user has, I simply do this:
SELECT referrer, COUNT(*) FROM referral_list GROUP BY referrer

I am trying to figure out how to select how many second-level referrals a user has. For example, user 1 has two referrals (user 10 and user 11), and also has one second-level referral (user 13, because user 1 referred user 10 who referred user 13). User 2 has one referral and two second-level referrals (user 2 referred user 12, who referred 14 and 15). Any ideas on how to go about selecting second-level referrals? I figured I need a subquery, but I just can't make it work.

The closest I have come is
SELECT user, referrer FROM referral_list WHERE user IN (SELECT referrer FROM referral_list)

But this only lists IF a user has a second-level referral, not how many they have.

I hope someone can help because I am stumped. Thanks!