Advanced SQL for Data Engineers

Window functions compute values across a set of rows related to the current row without collapsing the result set. Unlike GROUP BY, which produces one row per group, window functions keep every row and add a computed column. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, and NTILE are the most tested functions. The PARTITION BY clause defines the window groups. ORDER BY within the OVER clause defines the row ordering for ranking and running calculations.

-- Running total and rank within each department
SELECT
  employee_id,
  department,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS running_total,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees;

CTEs define temporary named result sets that exist only for the duration of a single query. They replace subqueries and make complex SQL readable by breaking it into named steps. Each CTE is defined in the WITH clause and can reference CTEs defined before it. CTEs do not imply materialization: the optimizer may inline them, merge them, or execute them separately depending on the query plan.

-- Multi-step analysis using CTEs
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
  FROM monthly_revenue
)
SELECT * FROM revenue_with_growth
WHERE growth < 0;  -- months with revenue decline

Recursive CTEs query hierarchical or graph-structured data. The recursive CTE has two parts: the anchor member (base case) and the recursive member (the step that references the CTE itself). The database executes the anchor first, then repeatedly executes the recursive member until it produces no new rows. Common use cases: org charts, bill of materials, category trees, and shortest path calculations.

-- Walk an org chart: find all reports under a manager
WITH RECURSIVE org_tree AS (
  -- Anchor: start with the manager
  SELECT employee_id, name, manager_id, 1 AS depth
  FROM employees
  WHERE employee_id = 100

  UNION ALL

  -- Recursive: find direct reports of the current level
  SELECT e.employee_id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.employee_id
  WHERE t.depth < 10  -- safety limit
)
SELECT * FROM org_tree ORDER BY depth, name;

LATERAL allows a subquery in the FROM clause to reference columns from preceding tables. Without LATERAL, each subquery in FROM is independent. With LATERAL, the subquery runs once per row of the preceding table, similar to a correlated subquery but in the FROM clause. This is the most efficient way to get the 'top N per group' pattern without window functions.

-- Top 3 orders per customer using LATERAL
SELECT c.customer_id, c.name, top_orders.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_id, amount, order_date
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.amount DESC
  LIMIT 3
) top_orders;

-- The LATERAL subquery references c.customer_id
-- It runs once per customer, returning up to 3 rows
-- Customers with no orders are excluded (use LEFT JOIN LATERAL to keep them)

Writing correct SQL is step one. Writing fast SQL is step two. Query optimization requires understanding indexes (B-tree, hash, GIN, GiST), execution plans (EXPLAIN ANALYZE), join algorithms (nested loop, hash join, merge join), and common anti-patterns (SELECT *, functions on indexed columns, implicit type casts, correlated subqueries). Data engineers must optimize queries that run against tables with billions of rows.

-- Read the execution plan
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.signup_date >= '2024-01-01'
GROUP BY c.name
HAVING COUNT(o.order_id) > 5;

-- Look for: Seq Scan (missing index?), Hash Join vs Nested Loop,
-- actual rows vs estimated rows (statistics stale?),
-- execution time per node

UNION, INTERSECT, and EXCEPT combine or compare result sets. UNION stacks rows vertically. INTERSECT returns rows that appear in both result sets. EXCEPT returns rows from the first set that do not appear in the second. Each has an ALL variant that skips deduplication. These operations follow set theory and are tested in interviews as an alternative to JOIN-based solutions.

-- Customers in both the US and EU databases (intersection)
SELECT customer_id, email FROM us_customers
INTERSECT
SELECT customer_id, email FROM eu_customers;

-- Customers in US but not in EU (set difference)
SELECT customer_id, email FROM us_customers
EXCEPT
SELECT customer_id, email FROM eu_customers;

-- Both operations deduplicate by default
-- Use INTERSECT ALL / EXCEPT ALL to preserve duplicates

Dynamic SQL constructs and executes SQL strings at runtime. It is used for queries where the table name, column list, or filter conditions are not known until execution time. In PostgreSQL, EXECUTE with format() builds safe dynamic queries. In application code, parameterized queries (prepared statements) prevent SQL injection while allowing dynamic values. Data engineers use dynamic SQL for metadata-driven pipelines, automated schema migrations, and generic data quality checks.

-- PostgreSQL: dynamic SQL in a function
CREATE OR REPLACE FUNCTION count_rows(table_name TEXT)
RETURNS BIGINT AS $$
DECLARE
  row_count BIGINT;
BEGIN
  EXECUTE format('SELECT COUNT(*) FROM %I', table_name)
  INTO row_count;
  RETURN row_count;
END;
$$ LANGUAGE plpgsql;

-- %I safely quotes the identifier (prevents SQL injection)
-- Use %L for literal values: format('...WHERE name = %L', val)