mysql - How to do a summation calculation for a referral program in ruby on rails? -

for e-commerce application, have order table , referral table belongs each completed order. once order completed, order_id stored in referral table.

class order < applicationrecord      belongs_to :product      belongs_to :buyer, class_name: "user"     belongs_to :seller, class_name: "user"     has_one :referral 

for each order, if referred purchase, have referral_id in order table belongs users order(the person sent referral). if wasn't referred purchase, referral_id null.

there reward milestones referrals, example 1 referral $5 , 3 referrals $20...

so if user has made 3 orders has generated referral_id:1, referral_id:2, , referral_id:3, how go order table , count number of referrals user per referral_id(1,2 , 3) , calculate rewards each 1 , sum three. if referral_id:1 had 1 referral , referral_id:2 , referral_id:3 had 3 referrals 5+20+20=45 in rewards.

and best store number in table?

i want lowest performance/speed penalty on app.

hope question finds well, i'm new ror :)