PostgreSQL Advanced Querying - Complete Notes

A practical and interview-focused guide to composing complex queries with CTEs, recursion, window functions, DISTINCT ON, LATERAL joins and conditional expressions.

00. The advanced-query mental model

An advanced query is usually not one clever trick. It is a sequence of small relational steps: define the correct rows, enrich them, calculate across related rows, and finally shape the output.

Start with the logical processing model from SQL Fundamentals. FROM and JOIN build rows, WHERE filters them, grouping creates summaries, and the select list shapes the result. The tools in this chapter let a query express additional stages without moving the work into application loops.

Examples reuse the shop schema from SQL Fundamentals:

customers(customer_id, email, full_name, city, active, created_at)
orders(order_id, customer_id, status, ordered_at)
order_items(order_id, product_id, quantity, unit_price)
products(product_id, name, category, price, stock)

01. Common table expressions (CTEs)

A common table expression is a named result available to one statement. It can make a long query read from top to bottom like a data pipeline.

WITH paid_order_totals (order_id, customer_id, ordered_at, total) AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.ordered_at,
        SUM(oi.quantity * oi.unit_price)
    FROM orders AS o
    JOIN order_items AS oi USING (order_id)
    WHERE o.status = 'paid'
    GROUP BY o.order_id, o.customer_id, o.ordered_at
)
SELECT customer_id, COUNT(*) AS paid_orders, SUM(total) AS lifetime_value
FROM paid_order_totals
GROUP BY customer_id
ORDER BY lifetime_value DESC, customer_id;

The name paid_order_totals exists only inside this statement. The optional column list defines a stable interface for the CTE. Without it, output names come from the inner query. Multiple CTEs are comma-separated, and a later CTE can read an earlier one.

WITH order_totals AS (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi USING (order_id)
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.order_id, o.customer_id
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
    FROM order_totals
    GROUP BY customer_id
),
qualified AS (
    SELECT *
    FROM customer_totals
    WHERE order_count >= 3 AND spent >= 10000
)
SELECT c.customer_id, c.full_name, q.order_count, q.spent
FROM qualified AS q
JOIN customers AS c USING (customer_id)
ORDER BY q.spent DESC, c.customer_id;

Inlining, materialization and optimizer behavior

PostgreSQL can fold a non-recursive, side-effect-free CTE into its parent query. Folding lets the optimizer push parent filters into the CTE and choose one plan across both levels. By default, a qualifying CTE referenced once is normally folded. A CTE referenced more than once is normally materialized: PostgreSQL calculates it once and stores the intermediate result for reuse.

-- Force a separately calculated intermediate result.
WITH expensive AS MATERIALIZED (
    SELECT product_id, category, expensive_score(product_id) AS score
    FROM products
)
SELECT * FROM expensive WHERE category = 'books';

-- Invite parent predicates to reach the underlying table.
WITH products_stage AS NOT MATERIALIZED (
    SELECT * FROM products
)
SELECT *
FROM products_stage
WHERE product_id = 42;

Treat these keywords as measured tuning tools. First confirm semantics, inspect the plan with EXPLAIN (ANALYZE, BUFFERS), and then compare alternatives with representative data. Volatile functions and data-modifying CTEs have additional semantic reasons not to inline.

Data-modifying CTEs

A top-level WITH can contain INSERT, UPDATE or DELETE. Its RETURNING result, not the target table, becomes the CTE's rows. This supports atomic move-and-log patterns.

WITH removed AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND ordered_at < CURRENT_DATE - INTERVAL '1 year'
    RETURNING *
)
INSERT INTO archived_orders
SELECT * FROM removed;

02. Recursive CTEs

A recursive CTE grows a result from starting rows. It is the standard SQL tool for trees, graph reachability, dependency chains, ancestor paths and generated sequences.

WITH RECURSIVE cte_name (columns...) AS (
    anchor_query
  UNION ALL
    recursive_query_that_references_cte_name
)
SELECT * FROM cte_name;

1. The anchor produces the initial working rows.
2. The recursive term runs using the current working rows.
3. New rows become the next working set and are also added to the result.
4. Execution stops when an iteration produces no rows.

PostgreSQL implements this process iteratively even though SQL calls it recursive. A termination condition is essential. UNION ALL preserves all generated rows and is usually faster. Plain UNION removes duplicates at each stage and can sometimes stop a cycle, but it can also hide legitimate duplicate paths and does not solve every graph cycle.

WITH RECURSIVE numbers(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n FROM numbers ORDER BY n;

Walking a hierarchy

Assume a separate employee hierarchy for the next examples:

CREATE TABLE employees (
    employee_id bigint PRIMARY KEY,
    manager_id  bigint REFERENCES employees(employee_id),
    full_name   text NOT NULL
);

WITH RECURSIVE organization AS (
    SELECT
        employee_id,
        manager_id,
        full_name,
        0 AS depth,
        ARRAY[employee_id] AS path
    FROM employees
    WHERE employee_id = 10

  UNION ALL

    SELECT
        e.employee_id,
        e.manager_id,
        e.full_name,
        o.depth + 1,
        o.path || e.employee_id
    FROM employees AS e
    JOIN organization AS o ON e.manager_id = o.employee_id
    WHERE NOT e.employee_id = ANY(o.path)
)
SELECT employee_id, manager_id, full_name, depth, path
FROM organization
ORDER BY path;

The anchor chooses manager 10. The recursive term finds direct reports of the previous level. The path array serves two purposes: ordering by it displays a depth-first traversal, and rejecting an id already in the path prevents a bad cycle from looping forever. A depth limit can provide an additional guard when business rules define a maximum hierarchy depth.

SEARCH and CYCLE

PostgreSQL supports SQL-standard clauses that generate ordering and cycle-tracking columns. The traversal's internal visitation order is not a presentation guarantee; use the generated column in the outer ORDER BY.

WITH RECURSIVE organization(employee_id, manager_id, full_name) AS (
    SELECT employee_id, manager_id, full_name
    FROM employees
    WHERE employee_id = 10
  UNION ALL
    SELECT e.employee_id, e.manager_id, e.full_name
    FROM employees AS e
    JOIN organization AS o ON e.manager_id = o.employee_id
)
SEARCH DEPTH FIRST BY employee_id SET traversal_order
CYCLE employee_id SET is_cycle USING traversal_path
SELECT employee_id, manager_id, full_name, is_cycle, traversal_path
FROM organization
WHERE NOT is_cycle
ORDER BY traversal_order;

03. Window functions

A window function calculates across rows related to the current row while keeping every input row. This is the key difference from GROUP BY, which combines a group into one row.

function_or_aggregate(arguments) OVER (
    PARTITION BY grouping_expressions
    ORDER BY sequencing_expressions
    ROWS BETWEEN frame_start AND frame_end
)

Omitting PARTITION BY creates one partition for the entire input. Window ORDER BY controls the calculation but does not guarantee final display order, so use a separate outer ORDER BY. The rows visible to windows are the virtual table remaining after FROM, WHERE, GROUP BY and HAVING.

ROW_NUMBER, RANK and DENSE_RANK

SELECT
    o.customer_id,
    o.order_id,
    o.ordered_at,
    ROW_NUMBER() OVER (
        PARTITION BY o.customer_id
        ORDER BY o.ordered_at DESC, o.order_id DESC
    ) AS row_number,
    RANK() OVER (
        PARTITION BY o.customer_id
        ORDER BY o.ordered_at DESC
    ) AS rank,
    DENSE_RANK() OVER (
        PARTITION BY o.customer_id
        ORDER BY o.ordered_at DESC
    ) AS dense_rank
FROM orders AS o;

Rows tied on all window ordering expressions are peers. If the requirement is exactly one stable row, add a unique tie-breaker such as order_id. If the requirement includes all rows tied for third place, use RANK() <= 3 or DENSE_RANK() <= 3 according to the desired gap semantics. PostgreSQL also supplies PERCENT_RANK, CUME_DIST and NTILE(n) for relative position and buckets.

Top N per group and window filtering

Window functions logically run after WHERE, and PostgreSQL permits window calls only in the select list and ORDER BY. Calculate first in a subquery or CTE, then filter at the outer level.

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY ordered_at DESC, order_id DESC
        ) AS position
    FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE position <= 3
ORDER BY customer_id, position;

LAG and LEAD

LAG(value, offset, default) reads an earlier row in window order; LEAD reads a later row. The offset defaults to 1 and the missing-row result defaults to null. They avoid a self-join for previous/next comparisons.

SELECT
    customer_id,
    order_id,
    ordered_at,
    LAG(ordered_at) OVER (
        PARTITION BY customer_id
        ORDER BY ordered_at, order_id
    ) AS previous_order_at,
    ordered_at - LAG(ordered_at) OVER (
        PARTITION BY customer_id
        ORDER BY ordered_at, order_id
    ) AS time_since_previous
FROM orders
ORDER BY customer_id, ordered_at, order_id;

Window frames: ROWS, RANGE and GROUPS

Ranking and offset functions mainly use the partition and ordering. Aggregate windows and FIRST_VALUE, LAST_VALUE, and NTH_VALUE are frame-sensitive. With window ordering, the default is effectively RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, where current row includes its ordering peers. Duplicate ordering values can therefore move together.

WITH order_totals AS (
    SELECT o.customer_id, o.order_id, o.ordered_at,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi USING (order_id)
    GROUP BY o.customer_id, o.order_id, o.ordered_at
)
SELECT
    customer_id,
    order_id,
    ordered_at,
    total,
    SUM(total) OVER (
        PARTITION BY customer_id
        ORDER BY ordered_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_spend
FROM order_totals
ORDER BY customer_id, ordered_at, order_id;

-- Current order and previous six orders, even if months apart.
AVG(total) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at, order_id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- All orders in the previous seven calendar days.
SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
)

Bounds include UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING in valid order. PostgreSQL can also exclude the current row, its peer group, or peer ties with an EXCLUDE clause. Use that when a calculation needs neighbors but must omit the current observation.

Grouped aggregate versus aggregate window

-- Collapses orders into one row per customer.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- Keeps each order and repeats its customer's count for context.
SELECT order_id, customer_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;

Ordinary aggregates execute before windows. Therefore a window can process grouped results. For example, after producing one row per day with SUM(total), an outer window can calculate a running total across those daily rows.

Named windows

SELECT
    customer_id,
    order_id,
    ordered_at,
    LAG(ordered_at) OVER customer_timeline AS previous_order_at,
    ROW_NUMBER() OVER customer_timeline AS sequence_number
FROM orders
WINDOW customer_timeline AS (
    PARTITION BY customer_id
    ORDER BY ordered_at, order_id
)
ORDER BY customer_id, ordered_at, order_id;

A named window prevents subtle differences across repeated OVER clauses. Multiple window functions with compatible definitions may also share sorting work, but verify actual plans rather than promising it.

04. PostgreSQL DISTINCT ON

DISTINCT ON (expressions) keeps the first row from each equal group after sorting. It is a concise PostgreSQL-specific solution for top one per group.

SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    status,
    ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC, order_id DESC;

The DISTINCT ON expressions must match the leftmost ORDER BY expressions. Later ordering expressions decide which row wins inside each group. Here order_id DESC deterministically resolves identical timestamps. Without suitable ordering, the chosen row is unpredictable.

If final presentation must be ordered by newest order globally rather than by customer, wrap the DISTINCT ON query and apply a new outer ORDER BY ordered_at DESC.

05. LATERAL joins

A LATERAL item in FROM can reference columns from earlier items in the same FROM list. Conceptually, PostgreSQL evaluates it for each left-side row.

SELECT
    c.customer_id,
    c.full_name,
    recent.order_id,
    recent.status,
    recent.ordered_at
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.status, o.ordered_at
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.ordered_at DESC, o.order_id DESC
    LIMIT 2
) AS recent ON true
ORDER BY c.customer_id, recent.ordered_at DESC, recent.order_id DESC;

The subquery can use c.customer_id because it is lateral. LEFT JOIN LATERAL preserves customers with no orders, filling recent columns with null. ON true means correlation is already handled inside the subquery. Use CROSS JOIN LATERAL when left rows with no right result should disappear.

SELECT p.product_id, tag
FROM product_documents AS p
CROSS JOIN LATERAL jsonb_array_elements_text(p.document -> 'tags') AS tag;

Table functions in FROM can reference preceding items even when the word LATERAL is omitted, but writing it can clarify intent. LATERAL is especially useful for top N per parent, set-returning functions, and calculations that produce multiple columns.

06. CASE and conditional expressions

CASE is an expression, not a control-flow statement. It returns one value, so it can appear anywhere a compatible value is allowed: SELECT, WHERE, ORDER BY, aggregates and updates.

Searched and simple CASE

-- Searched CASE: each WHEN is an independent condition.
CASE
    WHEN total >= 10000 THEN 'platinum'
    WHEN total >= 5000  THEN 'gold'
    WHEN total >= 1000  THEN 'silver'
    ELSE 'standard'
END

-- Simple CASE: compare one expression for equality.
CASE status
    WHEN 'paid' THEN 'payment complete'
    WHEN 'shipped' THEN 'on the way'
    WHEN 'cancelled' THEN 'closed'
    ELSE 'open'
END

PostgreSQL checks WHEN arms from top to bottom and returns the first matching result. Put narrow or high-priority conditions before broad ones. If no arm matches and ELSE is absent, the result is null. All possible result arms must resolve to a compatible common type.

Conditional aggregation

SELECT
    customer_id,
    COUNT(*) AS all_orders,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
    MAX(ordered_at) FILTER (WHERE status = 'shipped') AS last_shipped_at
FROM orders
GROUP BY customer_id;

The CASE pattern is widely portable. PostgreSQL's aggregate FILTER syntax is often clearer because it separates the aggregate from its input condition. Remember that SUM(CASE WHEN condition THEN 1 END) returns null when nothing matches; adding ELSE 0 returns zero. COUNT(CASE WHEN condition THEN 1 END) works because COUNT(expression) ignores nulls.

Custom sorting

SELECT order_id, status, ordered_at
FROM orders
ORDER BY
    CASE status
        WHEN 'pending' THEN 1
        WHEN 'paid' THEN 2
        WHEN 'shipped' THEN 3
        WHEN 'cancelled' THEN 4
        ELSE 5
    END,
    ordered_at,
    order_id;

COALESCE, NULLIF, GREATEST and LEAST

SELECT
    COALESCE(city, 'Unknown') AS display_city,
    revenue / NULLIF(order_count, 0) AS average_order_value,
    GREATEST(stock, 0) AS nonnegative_stock,
    LEAST(discount_percent, 100) AS capped_discount
FROM customer_metrics;

Arguments must resolve to suitable common or comparable types. PostgreSQL ignores null arguments in GREATEST and LEAST, returning null only when all arguments are null. That null behavior differs from some databases, so do not assume it is portable.

07. Composing the tools

Real reports often combine these features. Keep each stage responsible for one grain and name that grain explicitly.

WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.ordered_at,
        SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi USING (order_id)
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.order_id, o.customer_id, o.ordered_at
),
order_analysis AS (
    SELECT
        order_totals.*,
        LAG(total) OVER customer_orders AS previous_total,
        SUM(total) OVER (
            PARTITION BY customer_id
            ORDER BY ordered_at, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_spend,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY ordered_at DESC, order_id DESC
        ) AS recency
    FROM order_totals
    WINDOW customer_orders AS (
        PARTITION BY customer_id
        ORDER BY ordered_at, order_id
    )
)
SELECT
    c.customer_id,
    c.full_name,
    a.order_id AS latest_order_id,
    a.total AS latest_total,
    a.total - a.previous_total AS change_from_previous,
    a.running_spend,
    CASE
        WHEN a.running_spend >= 10000 THEN 'high value'
        WHEN a.running_spend >= 5000 THEN 'growing'
        ELSE 'new'
    END AS segment
FROM order_analysis AS a
JOIN customers AS c USING (customer_id)
WHERE a.recency = 1
ORDER BY a.running_spend DESC, c.customer_id;

Check the grain at every boundary: order_totals has one row per order; order_analysis still has one row per order; the final filter keeps one row per customer. Stating grain makes accidental multiplication visible before it becomes a wrong total.

08. Common mistakes and debugging

1. Run each CTE body alone and inspect sample rows plus COUNT(*).
2. Write down the expected key and grain for each stage.
3. Test empty input, nulls, duplicates, tied timestamps and customers with no children.
4. Add explicit ordering whenever first, latest, previous or running has meaning.
5. Use EXPLAIN (ANALYZE, BUFFERS) only after correctness is established.
6. Compare plans and timing using production-shaped volumes and distributions.

09. Practice problems

10. Interview Q&A

11. One-screen cheat sheet

• CTE: a statement-local named result; use it to expose pipeline stages.
• Optimization: a CTE is not automatically a performance improvement.
• Recursive CTE: anchor + UNION ALL + recursive term + termination.
• Cycles: track a visited path or use CYCLE; do not trust outer LIMIT.
• Window: preserve detail rows while calculating across related rows.
• Partition: independent group; frame: current row's subset.
• Ranking: ROW_NUMBER separates ties, RANK gaps, DENSE_RANK does not.
• Determinism: latest and first need a unique tie-breaker.
• Running total: use explicit ROWS from unbounded preceding to current row.
• Filter window: calculate inside, filter outside.
• DISTINCT ON: group expressions must lead ORDER BY.
• LATERAL: the right FROM item can use the current left row.
• CASE: first matching arm wins; compatible result types are required.
• Null tools: COALESCE defaults; NULLIF can turn a zero divisor into null.

12. Official reference map

• WITH and recursive queries
• Window function tutorial
• Window function reference
• Window frame syntax and value expressions
• SELECT and DISTINCT ON
• Table expressions and LATERAL
• Conditional expressions