Subqueries, CTEs, and Window Functions

Concept

Subqueries, common table expressions (CTEs), and window functions help solve questions that need more than one simple filter or group. They make analytical SQL easier to organize while preserving a clear definition of each intermediate result.

Subqueries

A subquery is a query inside another query. This example finds completed orders above the overall completed-order average:

SELECT
  order_id,
  customer_id,
  revenue
FROM orders
WHERE status = 'completed'
  AND revenue > (
    SELECT AVG(revenue)
    FROM orders
    WHERE status = 'completed'
  );

The inner query returns one average value. The outer query compares every qualifying order to it. The expected result contains only above-average completed orders; it does not identify why those orders are larger.

CTEs: Named Steps

A CTE begins with WITH and gives an intermediate query a name. It often makes multi-step analysis easier to read and test.

WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(revenue) AS total_revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_revenue
FROM customer_revenue
WHERE total_revenue > 1000
ORDER BY total_revenue DESC;

The CTE first creates one row per customer, then the outer query filters and sorts that result. CTEs organize logic; they are not automatically a performance improvement, and implementation details differ across databases.

Window Functions

GROUP BY collapses rows into one row per group. A window function calculates over related rows while keeping each original row. OVER (...) defines the window; PARTITION BY creates independent groups and ORDER BY establishes order within each group.

SELECT
  customer_id,
  order_id,
  revenue,
  AVG(revenue) OVER (PARTITION BY customer_id) AS customer_average_revenue,
  SUM(revenue) OVER (
    ORDER BY order_date, order_id
  ) AS running_revenue
FROM orders
WHERE status = 'completed';

Each order remains visible while gaining its customer's average and a running company revenue total. Including order_id after order_date makes the running order deterministic when dates tie.

Ranking

SELECT
  product_id,
  category,
  revenue,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS category_row_number,
  RANK() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS category_rank
FROM product_revenue;

ROW_NUMBER() gives every row a unique sequence. RANK() gives tied values the same rank and leaves a gap after a tie. Use the first when exactly one row must be first, and the second when ties should share rank.

Best Practices

Build and inspect each CTE independently, name it after its grain, and keep the final query focused on the business question. State whether a ranking should include ties and whether a running metric should reset by customer, region, or month. SQL dialects differ in window-frame and date syntax, so verify production queries in the target system.

Data Science Perspective

These patterns create customer features, cohorts, top-N reports, experiment comparisons, and interview-ready analysis. A typical workflow is SQL extraction and aggregation followed by Pandas for deeper exploration or modeling.

Interview Perspective

Question: How does a window function differ from GROUP BY? Answer: a window function computes across related rows without collapsing them; GROUP BY returns one row per group. Follow-up: distinguish ROW_NUMBER from RANK when values tie.

Practice Questions

1. Find orders above the overall average completed-order revenue.
2. Use a CTE to return the top five customers by total completed revenue.
3. Rank products within each category by revenue.

Quick Quiz

1. What keyword starts a CTE? Answer: WITH.
2. Which clause creates independent window groups? Answer: PARTITION BY.
3. Does GROUP BY retain every input row? Answer: no.

GROUP BY Compared With a Window

To calculate total revenue by customer, GROUP BY customer_id returns one row per customer. To keep every order while also showing that customer's total, use SUM(revenue) OVER (PARTITION BY customer_id). The rows are not collapsed; each order receives a group-level value. This distinction is central to ranking, running totals, and comparing an order with a customer's typical order value.

For a monthly running total, first aggregate to one row per month in a CTE, then apply a window over the monthly result. For rankings, PARTITION BY category means restart the ranking for each category, while ORDER BY revenue DESC decides who is first. ROW_NUMBER() gives unique positions even on ties; RANK() gives tied products the same rank and skips the next number.

Query-Building Habit

Treat the inner query or CTE as a testable dataset. Run it independently, check its row grain and totals, then add the outer filter or window calculation. In practice, many teams use database -> SQL query -> clean analytical dataset -> Python/Pandas -> visualization or modeling, although the exact division of work depends on the team and data platform.

Next Lesson

Continue to Exploratory Data Analysis to turn queried data into structured questions and insights.