schema.sql

-- =============================================================
-- Section 13 — Common Table Expressions (CTEs)
-- =============================================================

DROP SCHEMA IF EXISTS adv_cte CASCADE;
CREATE SCHEMA adv_cte;
SET search_path TO adv_cte;

-- Classic self-referencing employee table for the recursive CTE
CREATE TABLE employee (
    id         SERIAL PRIMARY KEY,
    full_name  VARCHAR(200) NOT NULL,
    title      VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employee(id),
    salary     INTEGER NOT NULL
);

-- Category tree for the other recursive CTE
CREATE TABLE category (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES category(id)
);

CREATE TABLE sale (
    id         SERIAL PRIMARY KEY,
    sold_at    DATE NOT NULL,
    amount_cents INTEGER NOT NULL
);