Welcome, I'm John at Maven Analytics. I have over 15 years of business intelligence experience, having worked with companies ranging from Fortune 500 to early-stage startups. I’ve performed leadership roles across analytics, marketing, SaaS and product teams.

You can write JOINs. You can write subqueries. Your queries produce correct results. But there's a good chance they look something like this when anyone else tries to read them: a subquery inside a subquery inside a WHERE clause, with no clear indication of what each step is actually doing.

That's not a skill gap. That's the natural place you land after learning SQL query by query, tutorial by tutorial. And there's a specific thing that fixes it.

It's called a CTE, a Common Table Expression. It's the query structure that separates junior analyst SQL from senior analyst SQL. Not because it does something you couldn't do with subqueries, but because it makes complex logic readable, testable, and maintainable — in a way that nested subqueries never can.

Over the next 5 days, you'll build a complete quarterly business review for Apex Retail using CTEs, window functions, and returns data. Each day adds a layer. By Day 4, you'll have a report that would take most junior analysts a day to write, and would be difficult for them to read.

Today: CTEs. What they are, why they exist, and how to write them.

What a CTE actually is

A CTE is a named, temporary result set that you define at the top of a query and then reference below, like giving a step a name instead of nesting it anonymously inside another query.

The syntax:

WITH cte_name AS (
  -- your query here
)
SELECT * FROM

WITH cte_name AS (
  -- your query here
)
SELECT * FROM

WITH cte_name AS (
  -- your query here
)
SELECT * FROM

That's it. The CTE runs first, gets named, and then the main SELECT reads from it as if it were a table.

You can chain multiple CTEs together, each one referencing the previous:

WITH step_one AS (
  -- first calculation
),
step_two AS (
  -- uses step_one
  SELECT * FROM step_one WHERE ...
)
SELECT * FROM

WITH step_one AS (
  -- first calculation
),
step_two AS (
  -- uses step_one
  SELECT * FROM step_one WHERE ...
)
SELECT * FROM

WITH step_one AS (
  -- first calculation
),
step_two AS (
  -- uses step_one
  SELECT * FROM step_one WHERE ...
)
SELECT * FROM

This is the structure senior analysts use for complex, multi-step analysis. Each step is visible, named, and testable independently.