SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table referrals (ID int, pid int, ref varchar(10), ref_by varchar(10));
insert into referrals (ID,  pid,  ref,  ref_by) values
(1,    1,   'k',      'NAN'),
(2,    2,   'l',      'k'),
(3,    3,   'm',      'k'),
(4,    4,   'n',      'l')
4 rows affected

create table users(id int,   name   varchar(10));
insert into users (id,    name) values   
(1,     'john'),  
(2,     'Bob'),  
(3,     'Tim'),   
(4,     'Rob')
4 rows affected

select 
r.id, u.name, r.ref,
isnull(c.counter, 0) number_of_referals,
isnull(c.counter, 0) * 100 points_earned
from users u inner join referrals r
on r.pid = u.id
left join (select ref_by, count(*) counter from referrals group by ref_by) c
on c.ref_by = r.ref
order by r.id


id
name
ref
number_of_referals
points_earned


1
john
k
2
200

2
Bob
l
1
100

3
Tim
m
0
0

4
Rob
n
0
0