sqlite - Count Persons in tree of Groups with a single query

Question

I'm writing a query in SQLite, for Android, with a schema like this (extremely simplified here, just the fields I need)

GROUP
group_id primary_key, 
parent_group_id nullable

PERSON
person_id primary_key,
parent_group

I need to count the number of persons in a group and in its descendant groups, given the group_id of the group I want to count for. I think I need a CTE query and I've been reading all morning about them, but I'm not grasping how they work.

question from:https://stackoverflow.com/questions/65898084/count-persons-in-tree-of-groups-with-a-single-query

Answer 1

You're on the right track with needing a CTE. Something like:

WITH tree AS
  (SELECT g.group_id AS root,
          g.group_id AS parent,
          p.person_id AS person
   FROM "group" AS g
   LEFT JOIN person AS p ON g.group_id = p.parent_group
   WHERE g.group_id = @desired_group
 UNION ALL
   SELECT t.root, g.group_id, p.person_id
   FROM tree AS t
   JOIN "group" AS g ON t.parent = g.parent_group_id
   LEFT JOIN person AS p on g.group_id = p.parent_group)
SELECT count(DISTINCT person)
FROM tree;

Start by selecting the desired group and its members, and then recursively select all members of groups with the given parent group. Finally, count all the unique users that were found.

db<>fiddle example.