Что такое CTE (Common Table Expressions)?

-- Простой CTE
WITH large_accounts AS (
    SELECT id, client_id, balance
    FROM accounts
    WHERE balance > 1000000
)
SELECT c.name, la.balance
FROM large_accounts la
JOIN clients c ON c.id = la.client_id;

-- Несколько CTE
WITH
debits AS (
    SELECT account_id, sum(amount) AS total_debit
    FROM transactions
    WHERE type = 'DEBIT' AND created_at > '2024-01-01'
    GROUP BY account_id
),
credits AS (
    SELECT account_id, sum(amount) AS total_credit
    FROM transactions
    WHERE type = 'CREDIT' AND created_at > '2024-01-01'
    GROUP BY account_id
)
SELECT
    a.id,
    COALESCE(d.total_debit, 0) AS total_debit,
    COALESCE(c.total_credit, 0) AS total_credit,
    COALESCE(c.total_credit, 0) - COALESCE(d.total_debit, 0) AS net_flow
FROM accounts a
LEFT JOIN debits d ON d.account_id = a.id
LEFT JOIN credits c ON c.account_id = a.id;

-- Иерархия подразделений
WITH RECURSIVE department_tree AS (
    -- Якорный запрос (базовый случай)
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсивный запрос
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT * FROM department_tree ORDER BY level, name;

-- Удалить устаревшие записи и вернуть количество
WITH deleted AS (
    DELETE FROM sessions
    WHERE expires_at < now()
    RETURNING *
)
SELECT count(*) AS deleted_count FROM deleted;