Chapter 22: Advanced Queries

Advanced SQL features like window functions, pivoting, and recursive common table expressions (CTEs) let you express complex analytical and hierarchical logic directly in queries. These constructs build on core SELECT syntax but operate across multiple rows at once, providing cumulative, comparative, and hierarchical insights.

Window functions (OVER, PARTITION BY, RANK, LAG/LEAD)

Window functions compute values across related rows without collapsing them into groups. They use the OVER() clause, which can define partitions (logical subgroups) and orderings. Typical examples include running totals, row numbers, moving averages, and value comparisons.

Basic structure

A window function looks like an aggregate but uses OVER() to define its scope. The result set retains one row per input record.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS total_per_customer
FROM orders;

This computes the total amount per customer without grouping rows together. Each order retains its own detail.

ROW_NUMBER(), RANK(), and DENSE_RANK()

Ranking functions assign order within each partition. They differ in how they handle ties.

SELECT
  customer_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS dense_rank_num
FROM orders;

LAG() and LEAD()

These functions look backward or forward in the ordered set to compare values across rows. They are useful for computing differences between consecutive events or detecting changes.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount,
  amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS diff_prev
FROM orders;

Moving averages and cumulative totals

By defining a frame with ROWS or RANGE, you can compute sliding windows such as a 7-day moving average or cumulative sums.

-- 7-day moving average by date
SELECT
  order_date,
  AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

-- cumulative total
SELECT
  customer_id,
  order_date,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

Pivoting and unpivoting data

Pivoting transforms rows into columns, while unpivoting reverses that process. These operations help reshape data for reports or analytics.

Pivoting with aggregates

The concept is universal, though syntax varies widely. You can use conditional aggregation or vendor-specific PIVOT operators.

-- Portable approach: conditional aggregation
SELECT
  customer_id,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total,
  SUM(CASE WHEN status = 'paid'    THEN amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped_total
FROM orders
GROUP BY customer_id;

SQL Server and Oracle include a dedicated PIVOT clause:

-- SQL Server example
SELECT *
FROM (
  SELECT customer_id, status, amount FROM orders
) AS src
PIVOT (
  SUM(amount) FOR status IN ([pending], [paid], [shipped])
) AS p;

Unpivoting (normalizing columns)

Unpivoting converts columns back into rows, often for cleaner storage or joining with metadata tables.

-- SQL Server example
SELECT customer_id, status, amount
FROM orders_pivoted
UNPIVOT (
  amount FOR status IN ([pending], [paid], [shipped])
) AS u;

Portable alternatives rely on UNION ALL constructs:

-- Portable UNION ALL approach
SELECT customer_id, 'pending' AS status, pending_total AS amount FROM orders_pivoted
UNION ALL
SELECT customer_id, 'paid', paid_total FROM orders_pivoted
UNION ALL
SELECT customer_id, 'shipped', shipped_total FROM orders_pivoted;

Recursive CTEs

Recursive common table expressions let you query hierarchical or graph-like data. They start with a base case, then repeatedly join or union new rows until no further matches appear. Use them for trees, bill-of-materials, or dependency chains.

Structure of a recursive CTE

A recursive CTE has two parts: an initial query (the base case) and a recursive query that references the CTE itself. Combine them with UNION ALL.

WITH RECURSIVE subordinates AS (
  -- base case
  SELECT employee_id, manager_id, full_name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive step
  SELECT e.employee_id, e.manager_id, e.full_name, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level, employee_id;

Limiting recursion depth

Engines typically restrict recursion depth for safety. You can control it explicitly.

-- PostgreSQL / SQLite
WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM t WHERE n < 10
)
SELECT * FROM t;

-- SQL Server
WITH t AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM t WHERE n < 10
)
SELECT * FROM t
OPTION (MAXRECURSION 100);

Practical uses

• Exploding organizational hierarchies (manager → employee trees)
• Walking parent-child product assemblies
• Computing dependency graphs (modules, files, prerequisites)
• Generating date or number sequences without auxiliary tables