How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

Question

What would be the best way to aggregate across hierarchies?

I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) -

1. folder

Column	Type	Description
folder_id (pk)	uuid	UUID for folder
org_id (pk)	uuid	Org ID where the folder belongs
name	text	Textual name of the folder
type	text	What level is the folder at? Example -> home, floor, apartment_complex, locality, suburb
created_at	timestamptz	Time when the folder was created

2. folder_hierarchies

Column	Type	Description
folder_id (pk)	uuid	fk reference of folder_id from folder table
org_id (pk)	uuid	fk reference of org_id from folder table
parent (fk)	uuid	fk reference of folder_id from folder table - denoting parent of current folder
path	ltree	ltree definition for the current folder

3. work

Column	Type	Description
work_id (pk)	uuid	UUID for work
org_id	uuid	Org ID where the work was done
folder_id	uuid	Folder on which the work was done
created_at	timestamptz	Time when the work was done

The folder is nothing but a hierarchy, think of it as a file system, running at most 5 levels deep. work is done at the leaf-node of the folder hierarchy, i.e., if the type = home. Now, a home can have any type of parent, it could be one of -

• null (in which case parent is '00000000-0000-0000-0000-000000000000' and ltree is the folder_id of the home itself)
• floor (so ltree for home becomes floor_id.home_id)
• apartment_complex (so ltree for home becomes apartment_complex_id.floor_id.home_id)
• locality (so ltree for home becomes locality_id.apartment_complex_id.floor_id.home_id)
• suburb (so ltree for home becomes suburb_id.locality_id.apartment_complex_id.floor_id.home_id)

Below is a sample representation of the hierarchy -

Okay, so that's the setup.

What I'm looking to do is aggregate work count by various hierarchy levels -

1. Aggregate total work count by home (straightforward)
2. Aggregate total work count by floor
3. Aggregate total work count by apartment_complex
4. Aggregate total work count by locality
5. Aggregate total work count by suburb

The Postgres instance is queried by an engine that maintains pre-aggregations of data. Leveraging that, I thought the best way would be to separate each of the different rollups into different views (or cubes). Like one view for home <> floor mapping, another for home <> apartment_complex mapping and so on.

What would be the best way to do this?

Here's some sample work that I have done. It works, but I'm not proud of it, and I'm worried that the performance might be very slow.

1. Aggregate total work by floor ->

-- Maintain home <> floor mapping (pre-aggregation)
SELECT h.folder_id as home, h.parent as floor_id 
    FROM folder_hierarchies h JOIN folders f ON f.folder_id = h.parent 
    WHERE f.meta_label = 'floor';

-- Count work done per floor
SELECT
  `home_floor__floor_id` `home_floor__floor_id`,
  `work__created_at_day` `work__created_at_day`,
  sum(`work__count`) `work__count`
FROM
  dev_pre_aggregations.home_floor_rollup_home_floor AS `home_floor__rollup_home_floor`
  LEFT JOIN dev_pre_aggregations.work_rollup_work AS `work__rollup_work` ON `home_floor__home_id` = `work__home_id`
WHERE
  (
    `work__created_at_day` >= to_timestamp(?)
    AND `work__created_at_day` <= to_timestamp(?)
  )
GROUP BY
  1,
  2
ORDER BY
  3 DESC
LIMIT
  10000

2. Aggregate total work by suburb (using ltree)

-- Maintain home <> suburb mapping (pre-aggregation)
SELECT child.folder_id AS home_id, suburb.folder_id AS suburb_id
          FROM ${homesFolderHierarchiesTable} child
                 JOIN ${homesFolderHierarchiesTable} suburb ON child.path <@ suburb.path
                 JOIN ${homesFoldersTable} suburb_folder
                      ON suburb_folder.meta_label = 'suburb' AND suburb_folder.folder_id = suburb.folder_id
                 JOIN ${homesFoldersTable} child_folder
                      ON child_folder.meta_label = 'home' AND child_folder.folder_id = child.folder_id

-- Count work done per suburb
SELECT
  `home_suburb__suburb_id` `home_suburb__suburb_id`,
  sum(`work__count`) `work__count`
FROM
  dev_pre_aggregations.home_suburb_rollup_home_suburb AS `home_suburb__rollup_home_suburb`
  LEFT JOIN dev_pre_aggregations.work_rollup_work AS `work__rollup_work` ON `home_suburb__home_id` = `work__home_id`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10000