Master SQL CTEs: Write Cleaner, More Powerful Queries

Imagine you are building a house. Instead of trying to hold up the roof, align the walls, and pour the foundation all at the exact same fraction of a second, you work in stages. You complete the framework, stabilize it, and then build on top of it.

Writing complex SQL queries should follow the exact same logic. Yet, many data professionals still write massive, deeply nested subqueries that read like ancient hieroglyphs.

If you want your code to be clean, readable, and highly maintainable, you need to master CTEs (Common Table Expressions). They are among the most powerful tools in an analyst’s toolkit — separating entry-level coders from seasoned professionals who stand out in interviews and on the job.

Let’s dive into what CTEs are, why they are essential, and how to apply them to both simple and highly complex scenarios.

What is a CTE?

In simple terms, a CTE (Common Table Expression) is a temporary result set defined within the scope of a single query.

Think of it as a temporary table that you build on the fly, give a recognizable name, and tell SQL to hold onto for a brief moment. You can then reference this temporary table just like a regular database table, as many times as you need — but only within that specific execution block.

Here is the basic syntax of a CTE:

SQL

WITH cte_name AS (
  SELECT * FROM table1
)
SELECT *
FROM cte_name;

Why Do You Need CTEs?

There are generally two situations you will encounter in your data career: when you want to use a CTE, and when you absolutely have to.

1. When You WANT to Use Them (For Readability)

Sometimes, you opt into a CTE purely for clarity, even if a subquery or a standard join would technically work.

When writing queries destined for a production dashboard or a team code review, CTEs allow you to define your business logic step-by-step. Instead of hiding filters and aggregates inside a massive, unreadable block of SQL, you construct your logic incrementally. It’s a massive favor to your future self and your teammates who will inevitably have to debug your work six months from now.

2. When You HAVE to Use Them (For Overcoming SQL Restrictions)

The most common limitation CTEs solve is nested aggregate functions. SQL explicitly forbids you from nesting aggregate functions directly; running AVG(SUM(column)) will immediately throw a syntax error.

A CTE fixes this seamlessly. You calculate the initial aggregation (the sum) in the CTE, and then take the second aggregation (the average) in the main outer query:

SQL

WITH order_counts AS (
  SELECT
    user_id,
    SUM(ordered) AS num_orders
  FROM table1
  GROUP BY 1
)
SELECT AVG(num_orders) AS avg_per_user
FROM order_counts;

Another classic scenario involves mixing data granularities. Imagine you need to show individual, daily-level orders, but you also want to include the company’s total monthly sales alongside every single daily row.

To accomplish this, you construct a CTE that aggregates metrics up to the monthly level, and then left-join it right back to your daily granular dataset:

SQL

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS monthly_orders
  FROM orders
  GROUP BY 1
)
SELECT
  o.order_id,
  o.order_date,
  m.monthly_orders
FROM orders o
LEFT JOIN monthly m
  ON DATE_TRUNC('month', o.order_date) = m.month;

Try doing that without a CTE or a subquery, and you are guaranteed to have a stressful afternoon!

The Key Benefits of CTEs

• They are highly reusable: This is one of their biggest strengths. If you need to reference a specific filtered dataset or calculated matrix multiple times within a massive query, a CTE holds that blueprint ready for deployment. (Note: They are strictly local. If you don’t run the CTE block with the query, it ceases to exist.)
• They drastically improve readability: Long-term code utility relies heavily on how easily it can be digested. CTEs break down monolithic queries into sections, letting a code reviewer absorb your technical logic piece-by-piece.
• They can be recursive: Though less frequent in day-to-day ad-hoc analysis, CTEs can reference themselves. This advanced programming concept is crucial for hierarchical data (like organizational charts or product categories), giving CTEs a functional capability that standard subqueries simply do not possess.

CTEs vs. Subqueries: How to Choose?

A CTE is essentially the inside-out version of a subquery. Outside of recursive operations, nearly every CTE can be rewritten as a subquery, and vice versa.

So how do you choose? Here is a reliable rule of thumb:

• Choose a CTE if your business logic is complex, if a temporary dataset needs to be referenced multiple times, or if the code is being checked into a shared repository or handed off to a teammate.
• Choose a Subquery if you are writing a quick, simple, one-off lookup embedded in a larger query for an immediate data request.

In day-to-day enterprise work, it is common to write production queries utilizing three to five (or more) CTEs stacked sequentially — some out of technical necessity, and others purely to keep the workflow clean.

Practical Code Lab

Let’s look at two concrete examples using a standard relational e-commerce schema (like AdventureWorks) to see how CTEs transform practical analytics problems.

Example 1: The Simple CTE (Customer Leaderboard)

The Goal: Identify the top 10 customers who have placed the most orders, returning their unique ID, full name, and total order volume sorted from highest to lowest.

SQL

-- 10 Customers with most orders: CustomerID, FirstName, LastName, CountofOrders
WITH CustomerByOrder AS (
  -- CTE function that isolates CustomerID, FirstName, LastName, and CountofOrders
  SELECT DISTINCT 
    c.CustomerID, 
    p.FirstName, 
    p.LastName,
    COUNT(s.SalesOrderID) OVER (PARTITION BY s.CustomerID) AS CountofOrders -- Counts number of orders per customer
  FROM [Sales].[Customer] c
  LEFT JOIN [Person].[Person] p
    ON c.PersonID = p.BusinessEntityID -- Shows all Customers but only matching Person profiles
  JOIN Sales.SalesOrderHeader s
    ON s.CustomerID = c.CustomerID
)
SELECT TOP 10 * FROM CustomerByOrder
ORDER BY CountofOrders DESC;

Example 2: The Complex Scenario (Joins, UNIONS, and Roll-up Reporting)

The Goal: Create a unified monthly financial breakdown. The query needs to output the product order amounts for each month of the year, compute a running cumulative total for each year, and explicitly generate custom “grand total” breakdown rows for financial reporting visibility.

While this query uses a sequential UNION structure to map out specific yearly reporting boundaries (2011 through 2014) alongside an ultimate total_of_all_years summary row, wrapping blocks like this inside CTEs can help manage the heavy lift of window calculations across merged rows.

SQL

-- Sum of orders for each month, total sum per year, and a final total of all years
-- Target Columns: Year, Month, Sum_Price, CumSum

SELECT 
  CAST(YEAR(h.OrderDate) AS VARCHAR) AS Year, 
  CAST(MONTH(h.OrderDate) AS VARCHAR) AS Month,
  CAST(SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) AS VARCHAR) AS Sum_Price,
  CASE 
    WHEN GROUPING(MONTH(h.OrderDate)) = 0 THEN 
      SUM(CASE WHEN MONTH(h.OrderDate) IS NOT NULL THEN SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) END) 
      OVER(PARTITION BY YEAR(OrderDate) ORDER BY MONTH(OrderDate))
  END AS CumSum
FROM Sales.SalesOrderDetail d 
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID
GROUP BY YEAR(h.OrderDate), MONTH(h.OrderDate)

UNION

-- 2011 Grand Total Row
SELECT 
  CAST(YEAR(h.OrderDate) AS VARCHAR) AS Year, 
  'grand_total' AS Month,
  'NULL' AS Sum_Price,
  CASE 
    WHEN GROUPING(YEAR(h.OrderDate)) = 0 THEN 
      SUM(CASE WHEN YEAR(h.OrderDate) IS NOT NULL THEN SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) END) 
      OVER(PARTITION BY YEAR(OrderDate) ORDER BY YEAR(OrderDate))
  END AS CumSum
FROM Sales.SalesOrderDetail d 
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID
WHERE YEAR(h.OrderDate) = 2011
GROUP BY YEAR(h.OrderDate)

UNION

-- 2012 Grand Total Row
SELECT 
  CAST(YEAR(h.OrderDate) AS VARCHAR) AS Year, 
  'grand_total' AS Month,
  'NULL' AS Sum_Price,
  CASE 
    WHEN GROUPING(YEAR(h.OrderDate)) = 0 THEN 
      SUM(CASE WHEN YEAR(h.OrderDate) IS NOT NULL THEN SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) END) 
      OVER(PARTITION BY YEAR(OrderDate) ORDER BY YEAR(OrderDate))
  END AS CumSum
FROM Sales.SalesOrderDetail d 
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID
WHERE YEAR(h.OrderDate) = 2012
GROUP BY YEAR(h.OrderDate)

UNION

-- 2013 Grand Total Row
SELECT 
  CAST(YEAR(h.OrderDate) AS VARCHAR) AS Year, 
  'grand_total' AS Month,
  'NULL' AS Sum_Price,
  CASE 
    WHEN GROUPING(YEAR(h.OrderDate)) = 0 THEN 
      SUM(CASE WHEN YEAR(h.OrderDate) IS NOT NULL THEN SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) END) 
      OVER(PARTITION BY YEAR(OrderDate) ORDER BY YEAR(OrderDate))
  END AS CumSum
FROM Sales.SalesOrderDetail d 
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID
WHERE YEAR(h.OrderDate) = 2013
GROUP BY YEAR(h.OrderDate)

UNION

-- 2014 Grand Total Row
SELECT 
  CAST(YEAR(h.OrderDate) AS VARCHAR) AS Year, 
  'grand_total' AS Month,
  'NULL' AS Sum_Price,
  CASE 
    WHEN GROUPING(YEAR(h.OrderDate)) = 0 THEN 
      SUM(CASE WHEN YEAR(h.OrderDate) IS NOT NULL THEN SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) END) 
      OVER(PARTITION BY YEAR(OrderDate) ORDER BY YEAR(OrderDate))
  END AS CumSum
FROM Sales.SalesOrderDetail d 
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID
WHERE YEAR(h.OrderDate) = 2014
GROUP BY YEAR(h.OrderDate)

UNION

-- Absolute Total Across All Documented Years
SELECT 
  'total_of_all_years' AS Year, 
  'NULL' AS Month,
  'NULL' AS Sum_Price,
  SUM(d.UnitPrice * (1 - d.UnitPriceDiscount)) AS CumSum 
FROM Sales.SalesOrderDetail d
JOIN Sales.SalesOrderHeader h 
  ON d.SalesOrderID = h.SalesOrderID;

Wrapping Up

Mastering CTEs isn’t just about learning syntax — it’s about adopting a clean-code mindset. By breaking complex data tasks down into logical steps, you ensure your queries run predictably and remain easy to maintain.

Next time you find yourself nesting a subquery inside another subquery, take a breath, pause, and write a CTE instead. Your future self (and your team) will thank you!

SQL #CTE #Subquery #Data #DataAnalytics

Like what you read? You can buy me a coffee at: https://buymeacoffee.com/databen