SQL: CTEs, Windows, Views & Performance

Chapter roadmap

Write readable analytical queries with common table expressions and window functions, package logic in views, and reason about indexes and execution plans. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Structure queries with CTEs
Use window functions
Create useful views
Explain index tradeoffs and inspect plans

06.1

Common table expressions

A CTE names a query result for use by the following statement. It can separate a complex question into readable stages and prevent repeated subqueries. A recursive CTE can walk hierarchies or generate sequences, though syntax and recursion limits vary by database.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

WITH customer_totals AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT customer_id, lifetime_value
FROM customer_totals
WHERE lifetime_value >= 1000;

Result

customer_id | lifetime_value
44          | 1250.00
81          | 1822.75

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

06.2

Window functions

A window function calculates across related rows without collapsing them. OVER defines the window, PARTITION BY creates groups, and ORDER BY defines sequence. Ranking, running totals, moving averages, and comparisons with previous rows are common uses.

Example

SELECT
    employee_id, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;

Result

employee_id | department | salary | salary_rank | department_average
12          | Design     | 92000  | 1           | 81500

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

06.3

Views and reusable query interfaces

A view stores a query definition and behaves like a virtual table. It can centralize approved business logic, simplify access, and hide sensitive columns. Views do not automatically guarantee performance and should be documented with their grain and filtering assumptions.

Example

CREATE VIEW active_customer_summary AS
SELECT c.customer_id, c.full_name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.active = TRUE
GROUP BY c.customer_id, c.full_name;

Result

Applications can query active_customer_summary without repeating its join and grouping logic.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

06.4

Indexes and execution plans

An index provides an additional structure for locating rows, often accelerating selective filters, joins, and ordering. It costs storage and makes writes more expensive. Use the database's plan command to see scans, joins, estimated rows, and whether an index serves the actual query.

Example

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at);

-- Database-specific syntax:
EXPLAIN SELECT *
FROM orders
WHERE customer_id = 44
ORDER BY ordered_at DESC;

Result

The plan can use the composite index to locate one customer's orders in date order.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A6.1
Rewrite a nested analytical query as two or more named CTE stages and document each stage's grain.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A6.2
Produce ranking, running total, previous-row comparison, and rolling-average reports with window functions.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A6.3
Create a view that exposes a safe, reusable reporting interface without private columns.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A6.4
Compare query plans before and after a targeted index, recording read cost and the index's write tradeoff.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

Analytics Portfolio

Analytics Portfolio — Build a small normalized database and a set of executive reports using joins, CTEs, windows, conditional aggregation, and views. Add only evidence-based indexes, capture plans, and write a README defining every report's purpose and grain.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

CTE
recursive query
window function
partition
frame
view
index
selectivity
execution plan