Beta

Total items belonging to the categories on all tree levels

Task

For each category in the tree-like categories table, calculate how many records in the items table belong to it and its parent categories across all the tree levels.

Notes

  • Items can only belong to the leaves of the category tree
  • There will be multiple roots for separate category trees
  • Order the result by the id column

Input tables

-----------------------------------
| Table | Column | Type |
|------------+-------------+------|
| categories | id | int |
| | parent | int |
|------------+-------------+------|
| items | id | int |
| | category_id | int |
-----------------------------------

Output table

------------------
| Column | Type |
|--------+-------|
| id | int |
| total | int |
------------------

Example

Category tree:---(1)             [(3 + 6) items]
|
|---(2) [3 items]
|
|---(3) [(5 + 1 + 0) items]
|
|---(4) [5 items]
|
|---(5) [1 item]
|
|---(6) [0 items]
Resulting table: id | total
-------+-------
1 | 9
2 | 3
3 | 6
4 | 5
5 | 1
6 | 0

Solution:

with recursive count_item as (
select c.id,
c.parent,
(select count(i.id) from items as i where i.category_id = c.id) as total
from categories as c
), h as
(select id, parent, total
from count_item
where id not in (select parent from categories where parent is not null)
union all
select c.id, c.parent, c.total + h.total as total
from h
join count_item as c on h.parent = c.id)
select id, sum(total)::int as total
from h
group by id
order by id
;

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data