SQL CTE Exercises

Use common table expressions to structure readable multi-step SQL queries. These 3 interactive challenges use real schemas and instant query validation.

Beginner Intermediate Advanced JOINs GROUP BY Window functions

3 SQL challenges

Open Practice Arena

CTE

Advanced

Top Earner Per Department (CTE)

Using a CTE, find the highest-paid employee in each department. Return columns: last_name, first_name, max_salary, department_id. Order results by: e.department_id.

50 XPSolve challenge

CTE

Advanced

Top Three Salaries with CTE

Use a CTE to return the three highest-paid employees.

50 XPSolve challenge

CTE

Concept guide

How to practice SQL CTE

A common table expression, introduced with WITH, gives a temporary name to a query result. CTEs make multi-step SQL easier to read, test and maintain.

What to master

• Give each CTE one clear responsibility
• Name intermediate columns and calculations clearly
• Test each step before composing the final query
• Use recursive CTEs only for genuinely recursive relationships

A reliable problem-solving workflow

1Break the problem into logical result sets.
2Write and test the first result set.
3Reference it from the next CTE or final SELECT.
4Verify that the composed query preserves the intended grain.

Is a CTE the same as a subquery?

Both can express intermediate results. A CTE is named at the beginning of a statement and is often easier to read or reuse within that statement.

Does a CTE always improve SQL performance?

No. A CTE primarily improves structure and readability. Its performance depends on the database engine, query plan and how the CTE is used.

Expert

PRO

Calculate how many customers from each acquisition month (cohort) placed orders in their first month. Return columns: cohort_size, cohort_month, retention_rate, first_month_orders. Order results by: c.cohort_month.

100 XPSolve challenge