Referred friends points query

Question

I'm trying to figure out what is the best way to achieve this, I really appreciate any input.

Part of my MYSQL Table:

ID , Username , Invited_by
1  , A        , 
2  , B        , 1
3  , C        , 2
4  , D        , 2
5  , E        , 4
6  , F        , 5

So i want to figure out who invited the most users, the trick part is that if A invited B and B invited C then i will count That A invited 2, what i'm trying to achieve is this.

ID , Username , Invited
1  , A        , 5
2  , B        , 4
3  , C        , 0
4  , D        , 2
5  , E        , 1
6  , F        , 0

Explanation

• F invited no one, same for C
• E invited F so he get 1 point
• D invited E so it means after E registered he invited F so D get 2 points
• B invited C and D, D then invited 2 people so B get 4 points
• A invited B which he invited 4 so A get 5 people

I know it's complex, thats why i'm trying to figure out the optimal solution for it.

Thanks,

UPDATE

So after i tried different approaches, i believe the best approach that i came up with is the following:

• Adding 1 field to the users table as 'total_invites' for example
• building a query that will calculate the number for invitations for each user, starting from the newest users till old users since the new users are likely didn't have the chance to invite anyone yet.
• run the query on the database "cronjob or on limited rows each time"
• once i reach to a balanced table the logic will change instead of calculating the results once i need the report i will increase the counter for each account and related accounts whenever a new referred user sign in.

NOTES

• Even building a query for a single user seems complicated and i think i have to do n queries until i reach the bottom of the invitation tree for that user
• i guess once the table reach a balance state the logic will become much easier.

Please if you have any thoughts or references that might help i will be very thankful.

Answer 1

I dont know if this what you looking for but it will be little long with many left joins.

 select t1.ID , t1.Username , count(t2.ID) + count(t3.ID) +  count(t4.ID)  as Invited 
 from table1 t1
 left join table1 t2 On t1.ID = t2.Invited_by
 left join table1 t3 On t2.ID = t3.Invited_by
 left join table1 t4 On t3.ID = t4.Invited_by
 group by t1.ID

DEMO HERE

Output:

 ID     USERNAME    INVITED
  1       A            5
  2       B            4
  3       C            0
  4       D            2
  5       E            1
  6       F            0

if you concerned with deeply friends list then check this function procedure to loop throw them . get a recursive parent list

EDIT2:

an good aproche for what im thinking , its up to you of course if you wanna do it.

is instead of inserting the Id of previous invited by person you can Concatenate all previous Invited by persons. Like that

    (1, 'A', NULL),
    (2, 'B', 'A'),
    (3, 'C', 'A,B'),
    (4, 'D', 'A,B'),
    (5, 'E', 'A,B,D'),
    (6, 'F', 'A,B,D,E')

everytime you insert an invited person do it with concat previous values with the new invited by person . the you have already looped them. and you dont need to use many queries , just one.

take a look at this DEMO