Make top 10 by checking 3 tables

Question

I have 3 tables:

sponsors

id | id_patrocinador | id_usuario

quotas

id | id_user | quantidade

users

id | nome | login

What I need to do is a TOP 10 where I list 10 users who have more sponsored with active quotas. For example:

1 - João - 10 indicados ativos
2 - Maria - 9 indicados aitvos
...

I tried to do the SELECT in various ways but I couldn’t get any certain result.

Answer 1

RESOLVED

I set up an SQL:

SELECT u.nome, u.login, (SELECT COUNT(DISTINCT p.id) FROM patrocinadores AS p INNER JOIN cotas AS c ON c.id_user = p.id_usuario WHERE c.status = 1 AND p.id_patrocinador = u.id) AS quantidade FROM usuarios AS u ORDER BY quantidade DESC LIMIT 10

Answer 2

I am without SQL on my machine now Try this:

select users.id ,users. ,Count(sponsors.id) from usuarios Inner Join sponsors on usuarios.id = sponsors.id_usuario group by usuarios.id, usuarios.name order by 3 desc