cte

-- Create hierarchical table (org chart)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  title VARCHAR(100),
  manager_id INT REFERENCES employees(id),

Recursive queries and hierarchical data with CTEs

postgresql recursive-cte hierarchical-data

by Maria Garcia 2 tabs

sql

-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders

Common Table Expressions (CTEs) for readable queries

sql cte common-table-expressions

by Maria Garcia 2 tabs