9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the SQL CTE guide PDF from Uncodemy: WITH clause syntax, multiple and recursive CTEs, readability tips and tested examples.
This page offers Uncodemy's SQL CTE guide as a PDF download. A common table expression, or CTE, is a named temporary result defined with WITH that you can use in the query that follows. CTEs make complex queries readable by breaking them into steps. The guide covers syntax, chaining, recursive CTEs, comparisons with subqueries and common mistakes.
Download the guide below. Each download is its own file, and everything in it is also explained on this page.
WITH clause syntax, chaining several CTEs, recursive CTEs, CTE vs subquery vs view vs temporary table, common mistakes and practice tasks, with examples on a customers and orders dataset.
A short form asks for your details before the download starts. Your details are used only to share study material and course information.
A common table expression is a named result set that exists only for the duration of one statement. You define it with the WITH keyword before the main query, give it a name and a query, and then refer to it as if it were a table. The main benefit is clarity. Instead of nesting a subquery inside a subquery, you write each step as a named block and read the query from top to bottom like a recipe.
CTEs do not store data permanently and, in most databases, they do not by themselves make a query faster; their value is in readability and in enabling recursion.
The examples use these tables: customers(customer_id, name, city, signup_date), orders(order_id, customer_id, order_date, status, order_total), order_items(order_id, product_id, quantity, unit_price) and products(product_id, product_name, category). Dates are text in YYYY-MM-DD form, so the examples run in SQLite and need small changes in other databases.
Revenue per customer calculated in a CTE, then filtered in the main query.
WITH customer_revenue AS (
SELECT customer_id, SUM(order_total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
)
SELECT customer_id, revenue
FROM customer_revenue
WHERE revenue > 500
ORDER BY revenue DESC;You can define several CTEs one after another, separated by commas, and later ones can use earlier ones. This is where CTEs shine: each step does one thing, has a clear name and can be checked on its own by running the query up to that point. Name steps by what they contain, such as monthly_revenue or first_orders, rather than by number, so the query documents itself. If a step is wrong, you can select from that CTE alone to inspect it before moving on.
Monthly revenue first, then the change from the previous month using a window function in the second step.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
SUM(order_total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY strftime('%Y-%m', order_date)
),
with_change AS (
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
)
SELECT month,
revenue,
prev_revenue,
ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS pct_change
FROM with_change
ORDER BY month;A recursive CTE refers to itself. It has two parts joined by UNION ALL: an anchor query that returns the starting rows and a recursive query that uses the CTE's own previous result to produce the next rows. The recursion stops when the recursive part returns no new rows. Typical uses are generating a series of dates or numbers and walking hierarchies such as an organisation chart or a category tree.
Always make sure the recursion terminates, for example by moving toward an end condition, because a mistake can create an endless loop; many databases enforce a maximum recursion depth.
Produces every date in a range so you can show days with zero orders. Syntax for generating dates differs a little between databases.
WITH RECURSIVE days AS (
SELECT DATE('2026-09-01') AS d
UNION ALL
SELECT DATE(d, '+1 day') FROM days WHERE d < '2026-09-07'
)
SELECT days.d AS day,
COALESCE(COUNT(o.order_id), 0) AS orders
FROM days
LEFT JOIN orders o ON o.order_date = days.d AND o.status <> 'cancelled'
GROUP BY days.d
ORDER BY days.d;All four hold an intermediate result, but they differ in how long it lives and what each one is best used for.
| Option | Lifetime | Best for |
|---|---|---|
| CTE | One statement | Readable multi-step queries and recursion |
| Subquery | One statement | Short, single-use logic |
| View | Stored in the database | Reusable logic shared by many queries |
| Temporary table | Session or until dropped | Large intermediate results reused several times |
Try these three tasks on your own data.
Explain each query aloud in plain words; if you can, you have understood it.
Want to write SQL the way analysts do at work? Explore Uncodemy's Data Analytics course and learn from industry trainers.