0

I've a table with users by structure like this:

id   name  parent_id
1    Mike     
2    Jack     1
3    Sam      1
4    Kurt     1
5    Somebody 3
6    Tommy    4
6    etc..    2

How to get a max count of referral on first level nesting per user, by this example I expect result:

3 because Jack, Sam, Kurt is a referral of Mike on first level
Cœur
  • 37,241
  • 25
  • 195
  • 267
Benjamin
  • 531
  • 2
  • 6
  • 18
  • 1
    Please define your question properly. How is "first level" defined? By `parent_id IS NULL`? Add a table definition and your Postgres version. – Erwin Brandstetter Apr 18 '15 at 20:13

1 Answers1

1

Assuming "first level" is defined by parent_id IS NULL and the current version Postgres 9.4:

SELECT parent_id, count(*) AS referral_ct
FROM  (
   SELECT id AS parent_id
   FROM   tbl
   WHERE  t1.parent_id IS NULL
   ) t1
JOIN   tbl t2 USING (parent_id)
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  1;  -- to only get 1 row with max. referral_ct

With only few root nodes, JOIN LATERAL may be faster:

SELECT t1.id, t2.referral_ct
FROM  (
   SELECT id
   FROM   tbl
   WHERE  parent_id IS NULL
   ) t1
LEFT  JOIN LATERAL (
   SELECT parent_id, count(*) AS referral_ct
   FROM   tbl
   WHERE  parent_id = t1.id
   GROUP  BY 1
   ) t2 ON true
ORDER   BY 2 DESC
LIMIT   1;  -- to only get 1 row with max. referral_ct

Related, with more explanation:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Yes! That it ;) Thx! Can you help me fix this code in order to get a count of referral on 3 level nesting per user or that impossible ? I expect result of 6. – Benjamin Apr 19 '15 at 07:39
  • Please ask a new question for a new question, comments are not the place. This question is also still lacking information. – Erwin Brandstetter Apr 19 '15 at 12:58