I have a service where users can give their friends a referral code and if a new user signs up using a code, both referrer and referee get a bonus.
I have a Users table that includes a field for a unique referral code generated for each user. This is the code they can share with their friends.
If someone signs up using one of these codes, it gets writtten to a referer code field for the new users record - this value will always correspond with a referral code from a pre-existing user record in the same table.
For each referal code I want to calculate the number of times its been used by a referree - whch mean counting the number of times that same code appears in the referrer code field in my Users table.
What’s the best way to calculate this value?