postgres 7 with recursive

using recursive query to find all sub levels given top level for tree architecture.

A

B             C

              BA BB       CA CB CC       

...........

tree view

find total base elements of the tree

with recursive tree_map(sub_level, current_level, quantity) as(
 with tree as (
  select * from (
   values(array['B','C'], 'A' , 1),
 
    (array['BA', 'BB'], 'B', 3),
    (array['CA','CB','CC'], 'C', 2),
 
    (array['BAA','BAB','BAC'], 'BA', 4),
    (array['BBA','BBB'], 'BB', 2),
    (array['CAA','CAB'], 'CA', 2),
    (array['CBA'], 'CB', 5),
    (array['CCA','CCB','CCC','CCD','CCE'], 'CC', 1),
 
    (array['x'], 'BAA', 10),
    (array['y'], 'BAB', 6),
    (array['z'], 'BAC', 8),
    (array['y'], 'BBA', 3),
    (array['x'], 'BBB', 5),
    (array['x'], 'CAA', 2),
    (array['y'], 'CAB', 10),
    (array['x'], 'CBA', 1),
    (array['y'], 'CCA', 7),
    (array['z'], 'CCB', 11)
    )
  as t(sub_level, current_level, quantity)
 )
 select sub_level, current_level, quantity from tree where current_level='A'
 union all
 select tree.sub_level, tree.current_level, tree.quantity
 from tree , tree_map
 where tree_map.sub_level @> array[tree.current_level]   --contains
)
select sub_level, sum(quantity) as total_quantity from tree_map
where sub_level @> array['x'] or sub_level @> array['y'] or sub_level @> array['z']
group by sub_level

--------------------------------------
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

----------------------------------
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

-----------------------------------
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

----------------------------------


https://www.postgresql.org/docs/current/queries-with.html