SQL Basic CTE
Watch a CTE resolve into its own temporary result, then get queried like a real table.
What & Why
The Basic CTE pattern is used to define one Common Table Expression and read it from the final SELECT. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants qualified leads isolated before source-level counting.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
Watch one named table feed the final aggregationStep 1 of 2
qualified_leads → GROUP BY source → final result
| id | source |
|---|---|
| 301 | google_ads |
| 303 | |
| 304 |
The qualified_leads CTE contains the ID and source for each lead with a qualification timestamp.
EXAMPLE QUERY
WITH qualified_leads AS (
SELECT id, source
FROM marketing.leads
WHERE qualified_at IS NOT NULL
)
SELECT
source,
COUNT(*) AS qualified_leads
FROM qualified_leads
GROUP BY source
ORDER BY qualified_leads DESC, source;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario