MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users(
   id         INTEGER  NOT NULL PRIMARY KEY 
  ,surname    VARCHAR(9) NOT NULL
  ,othernames VARCHAR(5) NOT NULL
);
INSERT INTO users(id,surname,othernames) VALUES (1,'Doe','John');
INSERT INTO users(id,surname,othernames) VALUES (2,'Doe','Jane');
INSERT INTO users(id,surname,othernames) VALUES (3,'Doe','Mary');
CREATE TABLE subscriptions(
   id                     INTEGER  NOT NULL PRIMARY KEY 
  ,user_id                INTEGER  NOT NULL
  ,parent_subscription_id INTEGER 
);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (1,1,NULL);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (2,2,1);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (3,3,1);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (4,4,2);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (5,5,3);
INSERT INTO subscriptions(id,user_id,parent_subscription_id) VALUES (6,6,3);
	select u.surname, u.othernames, count(s.parent_subscription_id) referrals
	from subscriptions s
	left join subscriptions p on p.id = s.parent_subscription_id
	inner join users u on u.id = coalesce(p.user_id, s.user_id)
	group by u.id, u.surname, u.othernames


surname
othernames
referrals


Doe
John
2

Doe
Jane
1

Doe
Mary
2