Advanced SQL: Window Functions & Recursive

Module Overview

Standard SQL aggregations with GROUP BY collapse individual records into single summary rows. In complex analytical and financial reporting, however, developers must compute running totals, rankings, moving averages, and hierarchical graph traversals without losing original tuple identities. In this fifth installment, we master Window Functions, dissect the logical execution order of SQL queries, explore Common Table Expressions (CTEs), and implement Recursive SQL for complex tree traversals.

1. The Logical Execution Order of SQL Clauses

One of the primary misconceptions among database engineers stems from confusing the syntactic order of an SQL statement with its true logical evaluation pipeline:

FROM / JOIN: Identifies source relations, computes Cartesian cross products, and filters join predicates.
WHERE: Filters individual candidate rows before any aggregations take place.
GROUP BY: Partitions remaining tuples into discrete aggregation buckets.
HAVING: Evaluates filter conditions over the aggregated group metrics.
SELECT: Projects requested expressions, evaluates scalar functions, and shapes column aliases.
WINDOW: Computes analytical window functions over the partitioned and ordered result sets.
DISTINCT: Eliminates duplicate projected output rows.
ORDER BY: Sorts final records according to specified sort keys and directions.
LIMIT / OFFSET: Slices the requested window of rows for network transport to the client.

2. Anatomy of SQL Window Functions

Window functions perform calculations across a set of table rows related to the current row, while retaining the discrete identity of every individual row. The syntax conforms to:

FUNCTION() OVER (PARTITION BY [col1, ...] ORDER BY [col2, ...] [FRAME_SPECIFICATION])

A. Ranking Functions: ROW_NUMBER vs. RANK vs. DENSE_RANK

Understanding how ties are resolved across ordered partitions is essential for generating leaderboard tiers and paging data:

ROW_NUMBER(): Assigns a strictly unique, sequential integer to each row (1, 2, 3, 4, 5).
RANK(): Assigns identical rank numbers to tied values, skipping subsequent positions (1, 2, 2, 4, 5).
DENSE_RANK(): Assigns identical rank numbers to tied values without skipping gaps (1, 2, 2, 3, 4).

text

123456

SELECT 
  employee_id, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as seq_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) as rank_gap,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_nogap
FROM employees;

LAG() accesses data from previous rows, while LEAD() peers ahead into subsequent rows without requiring self-joins:

text

123456789101112

-- Month-over-month revenue growth analysis
SELECT 
  sale_month,
  monthly_revenue,
  LAG(monthly_revenue, 1) OVER (ORDER BY sale_month) as prev_month_revenue,
  (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY sale_month)) 
    / LAG(monthly_revenue, 1) OVER (ORDER BY sale_month) * 100 as mom_growth_pct,
  AVG(monthly_revenue) OVER (
    ORDER BY sale_month 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as trailing_3month_moving_avg
FROM revenue_summary;

3. Common Table Expressions (CTEs) & Modular SQL

A Common Table Expression (CTE) is a temporary, named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement via the WITH clause. CTEs replace hard-to-read nested subqueries with linear, top-to-bottom procedural pipelines.

4. Recursive CTEs: Traversing Hierarchies & Graphs

A Recursive CTE references its own self-defined result set, evaluating a mathematical fixed-point iteration until no new rows are produced. It is partitioned into two unified members:

Anchor Member: The non-recursive base query executed once to seed the initial result set.
Recursive Member: Joined directly to the CTE itself, executing iteratively on the output of the prior step until the termination predicate yields an empty set.

text

123456789101112131415161718

-- Traversing an organizational management hierarchy
WITH RECURSIVE OrgChart AS (
  -- 1. Anchor Member: Find Top-Level Executive
  SELECT employee_id, name, manager_id, 1 AS depth_level, CAST(name AS CHAR(255)) AS breadcrumb
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 2. Recursive Member: Join subordinates to existing parents
  SELECT e.employee_id, e.name, e.manager_id, o.depth_level + 1,
         CONCAT(o.breadcrumb, ' -> ', e.name)
  FROM employees e
  INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT depth_level, name, breadcrumb 
FROM OrgChart 
ORDER BY depth_level, name;

Next in Curriculum

Having conquered advanced query construction, in Part 6: Storage Engines: Slotted Pages, B+ Trees, Buffer Pools & Indexing, we open the database black box to inspect how pages, B+ trees, and buffer pools operate on disk.