Is it possible to select a user with the maximum number of refs in a 5-level ref?

Question

SQL

Hello!
Is this even possible? I have a user table, I have id and idUpline keys in it . (upline is the one who brought you. That is, you are his ref). Is it possible to somehow select 10 users who have the most people on all 5 levels of the ref?
PS: The request counting personal referrals looks like this:

SELECT mirror.username,COUNT(t1.idUpline) AS count
FROM user t1
LEFT JOIN user mirror ON t1.idUpline=mirror.id
GROUP BY t1.idUpline
ORDER BY count DESC
LIMIT 10

Will return 10 users with the maximum number of refs of the first level. Even if you drive this request in PHP code into a loop and run it 5 times, the result will still not be the same. After all, the top ten on the first round will be based on the number of first-level refs. And that's not exactly it.
Thanks in advance

Answer 1

Yes, you can, using the recursive WITH construct for T-SQL.
Here is my plate:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nchar](30) NOT NULL,
  [Referal] [int] NULL,
  [Payment] [real] NULL
) ON [PRIMARY]
GO

Accordingly:
ID - ID of the current user.
Referal - ID of the inviter, if there is no inviter, then NULL.
Here is a query for each ID that displays the number of referrals:

WITH T1 AS
(
  SELECT ID, Name, Referal, 0 AS distance, ID as TopParent
  FROM dbo.Table_3

  UNION ALL

  SELECT m.ID, m.Name, m.Referal, s.distance + 1 AS distance, S.TopParent
  FROM T1 AS S
    JOIN dbo.Table_3 AS M
      ON M.Referal = S.ID
),
T2 as
(select distinct TopParent as ID, count(ID)-1 as Members 
 from T1 
 group by TopParent )
 select * from T2 order by ID