recursive_with.sql

WITH RECURSIVE tree_list AS (
   SELECT 
     id, item, parent_id, CAST(item AS varchar(1000)) AS path
  FROM
    tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    child.id, child.item, child.parent_id, CAST(parent.path || '->' || child.item As varchar(1000)) AS path
  FROM
    tree AS child
	INNER JOIN tree_list AS parent
	ON (child.parent_id = parent.id)
)
SELECT
  id, path
FROM tree_list
ORDER BY path;

recursive_with_ddl.sql

CREATE TABLE tree(id integer PRIMARY KEY, parent_id integer, item varchar(100));

INSERT INTO tree(id,parent_id, item)
VALUES (1, NULL, 'Paper'),
(2,1, 'Recycled'),
(3,2, '20 lb'),
(4,2, '40 lb'),
(5,1, 'Non-Recycled'),
(6,5, '20 lb'),
(7,5, '40 lb'),
(8,5, 'Scraps');

subquery.sql

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

with.sql

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;