Analytical SQL: Window Functions, CTEs, and Query Optimization

Analytical SQL extends beyond basic CRUD operations. Window functions, common table expressions, advanced aggregations, and recursive queries enable complex data transformations that would otherwise require multiple queries or external processing. SQL is the most widely used language in data engineering — according to the 2024 Stack Overflow Survey, it ranks as the third most commonly used language overall and the most used language in data roles. Mastering analytical SQL is essential for any data engineer or analyst who works with data at scale.

Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing those rows into a single output. This distinguishes them from GROUP BY aggregations: window functions preserve individual row details while computing aggregate values. The PostgreSQL documentation describes window functions as the ability to perform calculations across sets of rows that are related to the current query row.

Basic Syntax

function(column) OVER (
    PARTITION BY column
    ORDER BY column
    frame_clause
)

The PARTITION BY clause divides the result set into logical groups. The ORDER BY clause determines the order of rows within each partition. The frame clause specifies which rows to include in the window frame relative to the current row. Understanding each component is essential for writing correct and efficient window function queries.

Ranking Functions

ROW_NUMBER assigns a unique integer to each row within a partition, starting at 1. It is the most commonly used ranking function and is frequently used for deduplication:

SELECT
    product,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
FROM sales;

RANK and DENSE_RANK handle ties differently: RANK leaves gaps in the sequence (1, 2, 2, 4), while DENSE_RANK does not (1, 2, 2, 3). Choose RANK when you need to know how many total rows precede a given value; choose DENSE_RANK when you need a contiguous ranking without gaps, such as for percentile calculations.

LAG and LEAD

LAG accesses a previous row value within the partition; LEAD accesses a next row value. These functions are essential for period-over-period comparisons, change detection, and time-series analysis:

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

LAG and LEAD accept an optional offset parameter (default 1) and an optional default value for rows outside the partition. They are commonly used for calculating day-over-day changes, running totals, and detecting anomalies by comparing current values to historical values.

Aggregate Window Functions

SUM, AVG, MIN, MAX, and COUNT can function as window functions by adding an OVER clause. This enables running totals, moving averages, and comparisons against group aggregates:

SELECT
    department,
    employee,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Aggregate window functions are useful for benchmarking individuals against their group, computing running totals that reset at partition boundaries, and calculating moving averages for time-series smoothing.

Framing

The frame clause controls which rows the window function considers relative to the current row. Three frame specifications are available:

SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS 7_day_moving_avg

Frame options include ROWS (physical row offset), RANGE (logical offset based on ORDER BY values), and GROUPS (groups of tied values). The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows from the start of the partition up to and including rows with the same ORDER BY value as the current row.

Common Table Expressions

CTEs make complex queries readable by breaking them into named subqueries that can be referenced multiple times within the same query. Unlike subqueries, CTEs support recursion and can be referenced by multiple downstream clauses:

WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
),
recent_orders AS (
    SELECT * FROM orders WHERE order_date >= '2024-06-01'
)
SELECT
    c.customer_id,
    c.total_spent,
    COUNT(r.order_id) AS recent_orders
FROM high_value_customers c
LEFT JOIN recent_orders r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.total_spent;

CTEs improve readability, enable code reuse within a query, and can improve performance by materializing intermediate results, though this behavior varies by database engine. Some databases optimize CTEs as inline views rather than materializing them, so performance testing is recommended for critical queries.

Recursive CTEs

Recursive CTEs are invaluable for hierarchical data — org charts, bill of materials, category trees, and graph traversal. They consist of an anchor member that initializes the recursion and a recursive member that references the CTE itself:

WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

The anchor member selects the root rows (employees with no manager). The recursive member iteratively adds the next level by joining child rows to their parents from the previous iteration. Each iteration adds one level of depth. Most databases enforce a recursion depth limit, typically 100-1000 iterations, to prevent infinite loops from cyclic data.

Advanced Aggregations

GROUPING SETS, CUBE, and ROLLUP

These extensions enable multiple grouping levels in a single query, replacing the need for multiple queries with different GROUP BY clauses combined with UNION ALL:

SELECT
    COALESCE(department, 'ALL') AS department,
    COALESCE(role, 'ALL') AS role,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY GROUPING SETS (
    (department, role),
    (department),
    (role),
    ()
);

ROLLUP produces hierarchical subtotals: department to role to grand total, creating a natural drill-down path. CUBE produces all possible grouping combinations across the specified columns — for N columns, CUBE generates 2^N grouping sets. GROUPING SETS gives explicit control over which combinations to compute, which is more efficient when you only need specific subtotal levels.

Query Optimization

Understanding Query Plans

EXPLAIN ANALYZE reveals the database execution plan. Key patterns to identify when analyzing plans:

• Seq Scan on a large table suggests a missing index or a query returning a large percentage of rows
• Nested Loop with high row estimates suggests a missing join index — nested loops are efficient for small result sets but degrade quickly as row counts grow
• Sort operations processing many rows suggest an index supporting ORDER BY or GROUP BY
• Hash Join is generally efficient for large datasets — the database builds a hash table on one side and probes it with the other

Indexing for Analytics

Indexing strategies differ significantly between OLAP and OLTP workloads:

• Bitmap indexes: Efficient for low-cardinality columns (status, category, region) in OLAP environments — they use bitwise operations for fast filtering
• Covering indexes: Include all columns needed by a query in the index itself, avoiding table access entirely through index-only scans
• Partial indexes: Index only rows matching a WHERE condition, reducing index size and maintenance overhead for targeted queries
• Columnar stores: For warehouse workloads, columnar storage often eliminates the need for traditional indexes because each column is stored and compressed independently

Practical Analytical Patterns

Keyset pagination (cursor-based) is faster than OFFSET/LIMIT for large datasets because it avoids scanning and discarding skipped rows:

SELECT * FROM orders
WHERE order_id > 12345
ORDER BY order_id
LIMIT 100;

Deduplication with window functions keeps the most recent record per unique identifier:

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM user_events
)
SELECT * FROM ranked WHERE rn = 1;

Histogram generation creates equal-width buckets for distribution analysis:

SELECT
    FLOOR(age / 10) * 10 AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY FLOOR(age / 10) * 10
ORDER BY age_group;

Pivoting with conditional aggregation transforms rows into columns without database-specific PIVOT syntax:

SELECT
    department,
    COUNT(*) FILTER (WHERE role = 'Engineer') AS engineers,
    COUNT(*) FILTER (WHERE role = 'Analyst') AS analysts,
    COUNT(*) FILTER (WHERE role = 'Manager') AS managers
FROM employees
GROUP BY department;

Frequently Asked Questions

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? ROW_NUMBER assigns a unique integer to each row with no ties sharing the same number — each row gets a distinct value. RANK assigns the same number to tied rows but leaves gaps in the sequence after ties. DENSE_RANK assigns the same number to tied rows without gaps. Use ROW_NUMBER for unique row identification, RANK when tie positions matter for ordering, and DENSE_RANK for contiguous rankings such as percentile calculations.

When should I use a CTE instead of a subquery? CTEs are more readable, can be referenced multiple times within the same query, and support recursion. Subqueries are more concise for simple single-use cases. As a rule of thumb, use a CTE whenever the subquery logic is non-trivial or used in multiple places within the query. CTEs also make debugging easier because you can test each CTE independently.

How do I optimize a slow analytical query? First, examine the query plan with EXPLAIN ANALYZE. Look for sequential scans on large tables, nested loops with high row estimates, and sort operations that process many rows. Common fixes include adding appropriate indexes, rewriting JOINs as EXISTS for anti-joins, filtering earlier in the query to reduce data volume, and breaking complex queries into CTEs for readability.

What is the difference between WHERE and HAVING? WHERE filters rows before aggregation, reducing the data that reaches the GROUP BY. HAVING filters groups after aggregation, applying conditions to aggregate results. Use WHERE whenever possible to reduce the data volume that must be processed in the aggregation phase. HAVING is only needed when the filter condition involves aggregated values like SUM or COUNT.

Can analytical SQL replace a programming language for data processing? For transformations that can be expressed as set operations — aggregations, joins, filtering, window functions — SQL is often more efficient and concise than Python or R. For operations requiring iteration, external API calls, or complex conditional logic, a programming language is more appropriate. The most effective data engineers use both, choosing SQL for set-based operations and Python for procedural logic.

Data Orchestration Guide — Data Warehousing Guide — Data Modeling Guide