0:00CTEs - Common Table Expressions.. let you break one sequel statement into named, readable steps.
0:07The result is a query you can read top to bottom.
0:11Here's the plan. We'll write a basic CTE first..
0:14then work out when it actually beats a subquery.
0:17We'll chain several steps into one statement..
0:19walk a hierarchy with recursion..
0:22and finish with scope, plus one performance note worth knowing.
0:26Every example in this video runs on this one small employees table.
0:30The task.. average the salaries inside each department,
0:34and keep only the departments that average above six thousand.
0:38Without a CTE, you'd probably write this.
0:41The inner query calculates each department's average. The outer query filters those averages.
0:47One roe survives. Engineering, at seventy-five hundred.
0:52But notice where your eye has to start.. in the middle.
0:55You read this query inside out. The useful name, dept avg, only shows up after the calculation it names.
1:02A CTE flips that. Move the calculation to the top.. and name it first.
1:08Same calculation.. department averages. Only its position changes.
1:14Here's the exact same query as a CTE. Nothing about the logic changed.. it just moved to the top, with a name.
1:22With starts the CTE. Dept avg is its name. And As introduces the query that fills it with roes.
1:29The body is our aggregation from before, character for character.
1:33The final query then uses dept avg like a table that already exists.
1:37Define the step.. then use it. The whole statement now reads top to bottom.
1:42That's a Common Table Expression.. a named query result that lives inside a single statement, and nowhere else.
1:51New requirement. Keep the departments above the average of all department averages.. not a fixed six thousand.
1:58With derived tables, that means writing the same aggregation twice.
2:02Same five lines, top and bottom. Two copies to keep in sync.
2:08With a CTE, both copies collapse into one definition at the top.
2:12Write the calculation once..
2:14then reference the name twice. Once as the table.. once inside the filter.
2:19One caveat. What you're reusing is the definition, in your source code.
2:24The database may still compute it twice. That call belongs to the optimizer, not to you.
2:29Here's what comes back.
2:30The comparison average works out to sixty-one twenty-five, so Engineering still qualifies. Same output as before.. pure coincidence.
2:39So the tiebreaker. Used once, trivially.. a subquery is fine. A named step, or anything referenced twice.. reach for the CTE.
2:49One named step is useful. You can also chain several of them into a pipeline.
2:54Step one is our aggregation again.. one With, one named step.
2:59Now the detail everyone trips on. The next step starts with a comma.. not a second With.
3:05Step two filters those averages.
3:07And notice, it reads from dept avg. A later CTE can reference any earlier one in the chain.
3:14So the pipeline grows. Aggregate, then filter.
3:17We've covered step one, so let's fold it out of the way.
3:21The final query joins the surviving departments back to employees.
3:25Aggregate.. filter.. join back. Three steps, one statement, read straight down.
3:32And the result is every employee in the qualifying department, highest salary first.
3:37So far, every step read a flat table. Some data has levels.
3:42Here's an org chart. Ava runs the company.
3:45Ben, Cara, and Dan report to her.
3:47And Eve reports to Dan.. two levels below the root. Walking arbitrary depth takes a recursive CTE.
3:55Here's the whole thing. It reads in three parts.
3:57The anchor picks the starting roe. Ava.. the one whose manager is null.
4:02Union all welds the anchor's roes to everything found after them.
4:07And the recursive term is what does the walking. It joins employees back to org chart.. the roes already found.
4:16Watch it run. Pass one is just the anchor.. Ava, level one.
4:21Pass two joins against Ava, and finds her three reports.
4:25Pass three finds Eve. The pass after that finds nothing new.. so recursion stops.
4:31Then the final select simply reads the accumulated set.
4:35Every person, every level, in one pass over the hierarchy.
4:39That's the pattern to recognize.. anchor, union all, recursive term.
4:44One dialect note. Postgres, sequellite, and my-sequel eight require the recursive keyword. Sequel server does the same thing.. but rejects the keyword, so you omit it.
4:58One property changes how you use these. A CTE isn't a view, and it isn't a temporary table. Nothing gets stored.
5:06Watch the scope. This statement defines dept avg, then selects everything from it.
5:12Both departments come back. Exactly what you'd expect.
5:15Now a second, separate statement asks for dept avg again.
5:19Gone. The name lived and died inside the first statement.
5:23Which tells you what CTEs are really for.. readability first.
5:28One caution from history. Before Postgres twelve, every CTE was materialized.. a fence the query optimizer couldn't see through.
5:37Modern engines behave differently from each other. So when performance matters, check how yours handles it.
5:44So here's what you walk away with.
5:46You can write a CTE, and read the whole statement straight down.
5:50You know the tiebreaker.. if a step deserves a name, or gets referenced twice, it earns a With.
5:56You can chain steps into a pipeline.
5:59You can spot recursion from its three parts.. anchor, union all, recursive term.
6:04And you know the boundary. One statement.. nothing stored.
6:08That's CTEs. The next time you meet a deeply nested query, naming its steps is usually the fastest way to understand it.

SQL CTEs

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 6 min 24 sec

0 views

About this video

A multi-step answer in an interview has to stay readable while you write it. Common table expressions name each step with WITH, so the query reads top to bottom instead of inside out. One small employees table powers every example.

You'll review how to:

Write a basic CTE with the WITH clause, and read the scope and lifetime rules
Turn a repeated subquery into one named step
Chain several CTEs into a pipeline where each step builds on the last
Recognize a recursive CTE and the hierarchy-walk pattern it is used for

For engineers who want to structure a long query legibly under time pressure.

What changed

v103 Oct 2026abhydesai6 min 24 sec037acd5

First published version.

0 views0 likes on this version