By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS id, 'SM' AS name, NULL AS ref_by UNION ALL
SELECT 2, 'MO', 1 UNION ALL
SELECT 3, 'Go', 2 UNION ALL
SELECT 4, 'ZZ', 1
)
SELECT
t1.name,
COUNT(t2.id) AS ref_count
FROM yourTable t1
LEFT JOIN yourTable t2
ON t1.id = t2.ref_by
GROUP BY
t1.name
ORDER BY
ref_count DESC
LIMIT 10;
| name | ref_count |
|---|---|
| SM | 2 |
| MO | 1 |
| Go | 0 |
| ZZ | 0 |