Basic CTE: Artist Album Counts
Common Table Expressions (CTEs) let you define named subqueries that make complex queries more readable. Let's start...
CTEWITHGROUP BY
Intermediate
7m25 pts
2195%
SQL Practice Hub
SQL Common Table Expressions Practice Questions
Name a step with WITH and use it like a table in the rest of the query.
28 questionsFreeRuns in your browser
- WITH
- Defines a named result at the top of the query.
- Chained CTEs
- Break a long query into readable steps.
- Recursive CTE
- Walks a hierarchy or generates a series of dates.
What you will practice
- Refactor deeply nested subqueries into a WITH chain that reads top to bottom
- Define several CTEs in one WITH clause and reference an earlier one from a later one
- Stage an aggregation in a CTE so a window function or filter can run on top of it
- Write a recursive CTE with its anchor and recursive members to traverse parent-child data
- Judge when a CTE is materialised and when a plain subquery or temp table is the better choice
Read the full overview
A common table expression is a named result set defined with WITH at the top of a query and referenced like a table further down. CTEs let a long query be written as a sequence of readable steps instead of nested subqueries, and a recursive CTE can walk a hierarchy or generate a series of dates. In interviews they are usually the difference between an answer a reviewer can follow and one they have to unpick from the inside out.
28 questionsPage 1 of 2
CTE: Top Tracks Per Genre
Use a CTE to find the longest track in each genre. CTEs are especially useful when you need to reference aggregated...
CTEWITHMAXJOINAdvanced10m35 pts27100%Multiple CTEs: Customer Analysis
Use multiple CTEs to perform a comprehensive customer analysis: calculate total spending and invoice count, then...
CTEWITHMultiple CTEsJOINAdvanced10m35 pts2392%Customer Spending vs Country Average
The marketing team wants to identify above-average spenders within each country to target premium campaigns. Compare...
CTEMultiple CTEsJOINAggregation+1Advanced12m35 pts467%Month-over-Month Revenue Growth Rate
Finance needs a revenue growth analysis showing monthly revenue alongside the previous month and the percentage growth...
CTEWindow FunctionsLAGDate Functions+1Advanced10m35 pts5100%Most Popular Genre per Country
The international marketing team needs to know the most popular music genre in each country by purchase count. This...
CTEWindow FunctionsROW_NUMBERJOIN+1Advanced12m40 pts4100%Customer Lifetime Value with Tier Classification
The CRM team wants to segment customers into value tiers based on their lifetime spending. Classify each customer as...
CTEWindow FunctionsNTILECASE WHEN+2Advanced10m35 pts480%Support Rep Sales Performance Comparison
Management wants to compare the sales performance of support representatives. Calculate each rep's customer count,...
CTEWindow FunctionsRANKJOIN+2Advanced10m35 pts5100%Cumulative Revenue by Country Over Time
The finance team needs a cumulative revenue growth chart for key markets. Show monthly revenue alongside the running...
CTEWindow FunctionsRunning TotalPARTITION BY+1Advanced10m35 pts267%Customer Genre Diversity Score
The product team wants to identify customers with the most diverse music taste — those who listen across many genres....
CTEWindow FunctionsDENSE_RANKJOIN+2Advanced12m40 pts3100%Artists Spanning Multiple Genres
The data team discovered that some artists have tracks in multiple genres. Find these cross-genre artists and show...
CTEJOINHAVINGSTRING_AGG+1Advanced10m35 pts3100%Customer Purchase Gap Analysis
The retention team wants to understand purchase frequency patterns. Calculate the average, minimum, and maximum gap (in...
CTEWindow FunctionsLAGDate Functions+1Advanced12m40 pts2100%Year-over-Year Genre Revenue Change
The analytics team wants to track how genre popularity changes over time. Calculate the year-over-year revenue change...
CTEWindow FunctionsLAGPARTITION BY+2Advanced12m40 pts0—Pareto Analysis: Top 20% Customers Revenue Share
Apply the Pareto principle (80/20 rule) to customer revenue. Find the top 20% of customers and show what percentage of...
CTEWindow FunctionsROW_NUMBERRunning Total+1Advanced15m40 pts0—Monthly Cohort Retention
Compute cohort retention: for each customer signup-month (first invoice), how many of that cohort were active in month...
CTEMultiple CTEsWindow FunctionsCohort Analysis+5Advanced15m40 pts0—
FAQ
SQL Common Table Expressions practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
What is a CTE in SQL?
A CTE (common table expression) is a named result defined with WITH that the rest of the query can use like a table.
- It exists only for that one statement.
- It turns nested subqueries into steps you read top to bottom.
- You can chain several, each building on the one before (shown here).
SQL
WITH paid AS (
SELECT * FROM orders WHERE status = 'paid'
),
by_customer AS (
SELECT customer_id, SUM(amount) AS total
FROM paid
GROUP BY customer_id
)
SELECT * FROM by_customer WHERE total > 500;Is a CTE faster than a subquery?
Not by itself. A CTE mainly makes the query easier to read.
- PostgreSQL 12+: a non-recursive CTE used once is inlined, so the plan matches a subquery.
- Used more than once, it may be computed once and reused.
- Force either behaviour with AS MATERIALIZED or AS NOT MATERIALIZED.
- Older PostgreSQL always materialized CTEs, which could block optimizations. Check EXPLAIN when speed matters.
When should I use a recursive CTE?
Use WITH RECURSIVE when rows point to other rows in the same table, like managers and their reports.
- Typical cases: org charts, category trees, bills of materials, date series.
- It has an anchor query (the start) and a recursive part that joins back until nothing new appears.
- Always add a stop condition or depth limit so a cycle in the data cannot loop forever.
SQL
WITH RECURSIVE chain AS (
SELECT id, manager_id, 1 AS depth
FROM employees WHERE id = 7
UNION ALL
SELECT e.id, e.manager_id, c.depth + 1
FROM employees e
JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.