CTE Patterns for Common Problems

Ratios and Percentages

Calculating ratios is one of the most common analytical tasks. It’s also where queries silently break if you’re not careful.

The NULLIF Pattern

Division by zero crashes your query. NULLIF prevents this:

-- Without protection: crashes if denominator is 0
SELECT numerator / denominator AS ratio;

-- With NULLIF: returns NULL instead of crashing
SELECT numerator / NULLIF(denominator, 0) AS ratio;

NULLIF(x, 0) returns NULL when x equals 0, and returns x otherwise. NULL divided by anything is NULL, so your query continues instead of failing.

The Full Pattern

Inspections by Risk Category

An inspection can have several violations in different risk categories. Use a CTE to first collapse the data to one row per inspection, keeping only its highest-severity category, then pivot with SUM(CASE WHEN ...) in the final SELECT. Treat inspections with no risk category as their own group, and order by the total inspection count.

The Pre-Aggregate-and-Join Pattern

Remember correlated subqueries from Lesson 4.1? They run once per row, which can be slow. Here’s the faster alternative.

The Problem: Correlated Subquery

-- Slow: runs the AVG calculation for every single row
SELECT
  e1.first_name,
  e1.salary,
  e1.department
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

For a table with 10,000 employees across 50 departments, that’s 10,000 separate AVG calculations.

The Solution: Pre-Aggregate with CTE

Now we calculate 50 averages (one per department), not 10,000. Same result, dramatically faster.

Highest Target Under Manager

Use a CTE to pre-aggregate, then filter based on the aggregated values.

Replacing Self-Referencing Queries

When you need to compare rows within the same table (like employees to their managers), the CTE approach is cleaner.

The Correlated Way

-- Find employees earning more than their manager
SELECT
  e.first_name,
  e.salary
FROM employee e
WHERE e.salary > (
  SELECT m.salary
  FROM employee m
  WHERE m.id = e.manager_id
);

The CTE Way (Clearer and Often Faster)

WITH manager_salaries AS (
  SELECT
    id,
    salary AS manager_salary
  FROM employee
  WHERE id IN (
    SELECT DISTINCT manager_id
    FROM employee
  )
)
SELECT
  e.first_name,
  e.salary
FROM employee e
JOIN manager_salaries m
  ON e.manager_id = m.id
WHERE e.salary > m.manager_salary;

Or even simpler with a self-join (from Module 3):

SELECT
  e.first_name,
  e.salary
FROM employee e
JOIN employee m
  ON e.manager_id = m.id
WHERE e.salary > m.salary;

Employee and Manager Salaries

Compare employees to their managers using the approach you find clearest.

When Subqueries Still Make Sense

CTEs are preferred, but simple subqueries have their place:

• Simple scalar lookups: WHERE x = (SELECT MAX(x)...) is perfectly readable
• Quick EXISTS checks: sometimes clearer than a CTE plus JOIN
• One-off filters: when a CTE would be overkill
• Reading legacy code: you’ll encounter subqueries constantly

The goal isn’t to eliminate subqueries. It’s about choosing the right tool for each situation. When queries get complex, CTEs are almost always the better choice.

Dialect Notes

CTEs work across all major SQL dialects with minor variations:

• PostgreSQL, MySQL 8.0+, SQL Server, Oracle: all support CTEs with WITH
• Recursive CTEs use WITH RECURSIVE in PostgreSQL/MySQL, just WITH in SQL Server/Oracle
• Recursive CTEs are advanced content covered in Premium Learning Paths

Key Takeaways

• NULLIF(x, 0) prevents divide-by-zero errors; use it for all ratios
• Use 100.0 (not 100) to force decimal division
• Pre-aggregate-and-join beats correlated subqueries for performance
• Calculate once in a CTE instead of once per row
• Simple subqueries still have their place; choose the clearest approach
• Debug by running each CTE independently

What’s Next

Congratulations! You’ve completed Module 4: Subqueries and CTEs. You now have powerful tools for breaking complex problems into manageable steps, comparing rows to aggregates, and writing queries that are actually readable six months later.

In the next module, you’ll tackle Window Functions. They’re asked in many data analyst interviews and let you do things that would be incredibly complex with CTEs alone: running totals, rankings, and period-over-period comparisons without self-joins.