WITH Clauses (CTEs) for Clarity
Explore how to simplify complex SQL queries using WITH clauses and common table expressions (CTEs). Learn to write clear, modular SQL that breaks logic into named steps, making queries easier to understand, modify, and extend in practical data analysis scenarios.
Step 1: Start with a challenge
In the last lesson, you wrote nested subqueries like:
It works, but imagine now needing to use that average salary in multiple places or layer it with other filters. That’s where your query starts getting messy.
Ask yourself: How can I make this readable, like a set of clear steps?
That’s where CTEs come in.
Prompt: Rewrite
using a this query Also append the SQL code given above to this prompt, and let AI teach you something new. WITHclause so the average salary is defined first, then used in the main query.