How to find levels in a hierarchy by dates using SQLite

Question

Problem

How to find a level for every individual in a hierarchy, by dates, using SQLite with recursive expression.

Details

I have found the following post detailing the use of recursive queries for doing computation on hierarchical data in SQLite and that one on StackOverflow for basic recursive queries on SQLite3.

Now, there is a good detailed example on the documentation of SQLite about how to approach computation for hierarchical data from a single employee perspective (example at part 3.2).

What have I managed to do?

Well, so far I know how to compute the levels for a selected individual (woohoo), but I can't bridge the gap and make this query apply to all the individuals by dates.

Here is my partial query that does the job for 1 individual:

WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
        SELECT org_1.id, org_1.boss_id, org_1.date_interest
        FROM org org_1
        WHERE id = 4 -- Here is the input for the individual
    UNION
        SELECT org_1.id, org_1.boss_id, org_1.date_interest
        FROM org org_1
        JOIN supervisor_of so
            ON so.boss_id = org_1.id
            AND so.date_interest = org_1.date_interest
)
SELECT *,
    COUNT(id) AS level
FROM supervisor_of
GROUP BY date_interest
ORDER BY date_interest

And the output:

| id   | boss_id | date_interest | level |
| ---- | ------- | ------------- | ----- |
| 4    | 2       | 2             | 3     |
| 4    | 2       | 3             | 3     |

But I can't manage to wrap around my head to get this result:

| id   | boss_id | date_interest | level |
| ---- | ------- | ------------- | ----- |
| 1    |         | 1             | 1     |
| 2    | 1       | 1             | 2     |
| 3    | 1       | 1             | 2     |
| 1    |         | 2             | 1     |
| 2    | 1       | 2             | 2     |
| 3    | 1       | 2             | 2     |
| 4    | 2       | 2             | 3     |
| 1    |         | 3             | 1     |
| 2    | 1       | 3             | 2     |
| 3    | 1       | 3             | 2     |
| 4    | 2       | 3             | 3     |
| 5    | 4       | 3             | 4     |

Here is how to load the data for making this test:

CREATE TABLE org(
  id TEXT,
  boss_id TEXT,
  date_interest TEXT
);
-- 1st Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 1);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 1);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 1);
-- 2nd Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(4, 2, 2);
-- 3rd Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(4, 2, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(5, 4, 3);

Answer 1

Remove the WHERE clause from your code so that you query for all ids and in the 2nd part of the recursive CTE select supervisor_of.id and not org_1.id.
Finally you must group by id also:

WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
  SELECT org_1.id, org_1.boss_id, org_1.date_interest
  FROM org org_1
  UNION
  SELECT so.id, org_1.boss_id, org_1.date_interest
  FROM org org_1 JOIN supervisor_of so
  ON so.boss_id = org_1.id AND so.date_interest = org_1.date_interest
)
SELECT *, COUNT(*) AS level
FROM supervisor_of
GROUP BY date_interest, id
ORDER BY date_interest, id

See the demo.
Results:

id	boss_id	date_interest	level
1	null	1	1
2	1	1	2
3	1	1	2
1	null	2	1
2	1	2	2
3	1	2	2
4	2	2	3
1	null	3	1
2	1	3	2
3	1	3	2
4	2	3	3
5	4	3	4