SQL CTE Practice Questions with Solutions

A Common Table Expression (CTE) is a temporary named result set that exists only during the execution of a single SQL query.

CTEs improve query readability, simplify complex SQL statements, and make large queries easier to maintain. SQL CTE Practice questions with solutions help to understand the concepts.

A CTE is created using the WITH keyword.

Unlike subqueries, CTEs can be referenced multiple times within the same query, making them ideal for reporting and data analysis.

Why Use SQL CTE?

CTEs help you:

• Improve query readability
• Break complex queries into smaller logical parts
• Reuse query results within the same statement
• Simplify joins and aggregations
• Improve report development
• Build recursive queries

CTEs are widely used in:

• Data Analytics
• Business Intelligence
• Financial Reporting
• HR Dashboards
• Sales Analysis
• SQL Interviews

Basic Syntax

WITH cte_name AS
(
    SELECT column1,
           column2

    FROM table_name
)

SELECT *

FROM cte_name;

Sample Table Used Throughout This Chapter

employees

1. SQL CTE to Display Employees Earning More Than ₹50,000

Problem Statement

The HR department wants to generate a report of employees earning more than ₹50,000.

Instead of writing a long query, use a Common Table Expression (CTE).

SQL Solution

WITH high_salary AS
(
    SELECT
        employee_name,
        department,
        salary

    FROM employees

    WHERE salary > 50000
)

SELECT *

FROM high_salary;

Sample Output

Explanation

The CTE named high_salary temporarily stores employees whose salary exceeds ₹50,000.

The main query simply retrieves all rows from the CTE.

This approach makes the SQL query cleaner and easier to understand.

Concepts Covered

• WITH Clause
• Basic CTE
• Temporary Result Set

2. SQL CTE to Display IT Department Employees

Problem Statement

A company wants to generate a report showing only employees working in the IT department.

Use a CTE.

SQL Solution

WITH it_employees AS
(
    SELECT
        employee_name,
        salary

    FROM employees

    WHERE department = 'IT'
)

SELECT *

FROM it_employees;

Sample Output

Explanation

The CTE stores only IT department employees.

The outer query retrieves the prepared result set.

CTEs make department-wise reports much easier to build.

Concepts Covered

• CTE
• WHERE Clause
• Department Reports

3. SQL CTE to Calculate Average Salary by Department

Problem Statement

The HR manager wants to calculate the average salary of each department.

Use a Common Table Expression.

SQL Solution

WITH department_salary AS
(
    SELECT
        department,
        AVG(salary) AS average_salary

    FROM employees

    GROUP BY department
)

SELECT *

FROM department_salary;

Sample Output

Explanation

The CTE performs the aggregation first.

The final query simply displays the department-wise average salary.

Using a CTE keeps aggregation logic separate from the final report.

Concepts Covered

• CTE
• GROUP BY
• AVG()

4. SQL CTE to Display Premium Products

Problem Statement

An e-commerce company wants to generate a report showing products priced above ₹20,000.

Instead of filtering directly in the main query, use a Common Table Expression (CTE).

Sample Table

products

SQL Solution

WITH premium_products AS
(
    SELECT
        product_name,
        category,
        price

    FROM products

    WHERE price > 20000
)

SELECT *

FROM premium_products;

Sample Output

Explanation

The CTE named premium_products stores only expensive products.

The outer query simply displays the prepared dataset.

This approach improves readability, especially when multiple reports use the same filtered data.

Concepts Covered

• WITH Clause
• CTE
• Product Database

5. SQL CTE to Generate High-Value Customer Report

Problem Statement

A retail company wants to identify customers who have spent more than ₹10,000.

Use a Common Table Expression to simplify the report.

Sample Table

customer_orders

SQL Solution

WITH high_value_customers AS
(
    SELECT
        customer_name,
        total_purchase

    FROM customer_orders

    WHERE total_purchase > 10000
)

SELECT *

FROM high_value_customers;

Sample Output

Explanation

The CTE creates a temporary table containing only high-value customers.

The final query retrieves this information, making the SQL easier to understand and maintain.

This type of report is commonly used for:

• Loyalty Programs
• Marketing Campaigns
• Customer Segmentation
• Sales Analysis

Concepts Covered

• CTE
• Customer Analytics
• Business Reporting

Why Use a CTE Instead of a Subquery?

Consider the following query using a subquery:

SELECT *

FROM
(
    SELECT
        customer_name,
        total_purchase

    FROM customer_orders

    WHERE total_purchase > 10000
) AS customer_data;

Now compare it with a CTE:

WITH high_value_customers AS
(
    SELECT
        customer_name,
        total_purchase

    FROM customer_orders

    WHERE total_purchase > 10000
)

SELECT *

FROM high_value_customers;

Why the CTE version is better

• Easier to read
• Easier to debug
• Easier to reuse
• Better suited for complex reports
• Preferred in modern SQL development

6. SQL CTE with JOIN to Display Employee and Department Details

Problem Statement

A company stores employee information and department information in separate tables.

Generate a report showing each employee along with their department name using a CTE.

Sample Tables

employees

departments

SQL Solution

WITH employee_details AS
(
    SELECT
        e.employee_name,
        d.department_name

    FROM employees e

    INNER JOIN departments d
    ON e.department_id = d.department_id
)

SELECT *

FROM employee_details;

Sample Output

Explanation

The CTE performs the INNER JOIN first and stores the result as a temporary table named employee_details.

The outer query simply retrieves the prepared report.

This makes complex join queries easier to read.

Concepts Covered

• CTE
• INNER JOIN
• Employee Database

7. SQL CTE with GROUP BY to Generate Department-wise Sales Report

Problem Statement

A company wants to calculate the total sales generated by each department.

Use a CTE.

Sample Table

sales

SQL Solution

WITH department_sales AS
(
    SELECT
        department,
        SUM(amount) AS total_sales

    FROM sales

    GROUP BY department
)

SELECT *

FROM department_sales;

Sample Output

Explanation

The CTE calculates total sales for each department.

The final query simply displays the summarized report.

Concepts Covered

• CTE
• GROUP BY
• SUM()

8. SQL CTE to Display Top-Selling Products

Problem Statement

A retail company wants to identify products that sold more than 100 units.

Use a CTE.

Sample Table

product_sales

SQL Solution

WITH top_products AS
(
    SELECT
        product_name,
        units_sold

    FROM product_sales

    WHERE units_sold > 100
)

SELECT *

FROM top_products;

Sample Output

Explanation

The CTE filters products that sold more than 100 units.

The main query retrieves the filtered dataset.

This report is commonly used in inventory and sales analysis.

Concepts Covered

• CTE
• Filtering
• Product Analytics

9. SQL CTE to Display Customers with Multiple Orders

Problem Statement

An online shopping platform wants to identify customers who have placed more than one order.

Use a CTE.

Sample Table

orders

SQL Solution

WITH repeat_customers AS
(
    SELECT
        customer_name,
        COUNT(*) AS total_orders

    FROM orders

    GROUP BY customer_name

    HAVING COUNT(*) > 1
)

SELECT *

FROM repeat_customers;

Sample Output

Explanation

The CTE groups orders by customer and counts how many orders each customer has placed.

Only customers with more than one order are included.

Concepts Covered

• CTE
• COUNT()
• GROUP BY
• HAVING

10. SQL CTE to Calculate Branch-wise Revenue

Problem Statement

A company has multiple branches and wants to calculate total revenue generated by each branch.

Use a CTE.

Sample Table

branch_sales

SQL Solution

WITH revenue_report AS
(
    SELECT
        branch,
        SUM(revenue) AS total_revenue

    FROM branch_sales

    GROUP BY branch
)

SELECT *

FROM revenue_report;

Sample Output

Explanation

The CTE aggregates revenue for each branch before the final report is generated.

This type of report is useful for:

• Branch Performance
• Revenue Analysis
• Business Intelligence
• Financial Dashboards

Concepts Covered

• CTE
• SUM()
• GROUP BY
• Revenue Reporting

11. SQL Query Using Multiple CTEs

Problem Statement

A company wants to generate a report showing:

• Employees earning more than ₹50,000
• Departments where these employees work

Use multiple Common Table Expressions in a single query.

Sample Tables

employees

departments

SQL Solution

WITH high_salary AS
(
    SELECT
        employee_name,
        department_id,
        salary

    FROM employees

    WHERE salary > 50000
),

department_info AS
(
    SELECT
        department_id,
        department_name

    FROM departments
)

SELECT
    h.employee_name,
    d.department_name,
    h.salary

FROM high_salary h

INNER JOIN department_info d

ON h.department_id = d.department_id;

Sample Output

Explanation

This query creates two separate CTEs:

• high_salary
• department_info

The final query joins both CTEs to generate a complete report.

Concepts Covered

• Multiple CTEs
• JOIN
• Report Generation

12. SQL CTE with ROW_NUMBER()

Problem Statement

Display employees ranked by salary using ROW_NUMBER() inside a CTE.

SQL Solution

WITH salary_rank AS
(
    SELECT
        employee_name,
        salary,

        ROW_NUMBER() OVER
        (
            ORDER BY salary DESC
        ) AS salary_rank

    FROM employees
)

SELECT *

FROM salary_rank;

Sample Output

Explanation

The CTE calculates rankings first.

The outer query simply displays the ranked result.

This technique is commonly used in:

• Leaderboards
• Salary Reports
• Sales Rankings
• Business Dashboards

Concepts Covered

• CTE
• ROW_NUMBER()
• Window Function

13. Recursive CTE to Display Employee Hierarchy

Problem Statement

A company stores managers and employees in the same table.

Generate the employee hierarchy using a Recursive CTE.

Sample Table

employees

SQL Solution

WITH RECURSIVE employee_hierarchy AS
(
    SELECT
        employee_id,
        employee_name,
        manager_id

    FROM employees

    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id

    FROM employees e

    INNER JOIN employee_hierarchy h

    ON e.manager_id = h.employee_id
)

SELECT *

FROM employee_hierarchy;

Sample Output

Explanation

A Recursive CTE repeatedly executes until no more matching rows are found.

It is commonly used for:

• Organization Charts
• Folder Structures
• Category Trees
• Bill of Materials

Concepts Covered

• Recursive CTE
• UNION ALL
• Hierarchical Data

14. SQL CTE with Aggregate Comparison

Problem Statement

Display employees earning more than the average company salary using a CTE.

SQL Solution

WITH average_salary AS
(
    SELECT
        AVG(salary) AS avg_salary

    FROM employees
)

SELECT
    employee_name,
    salary

FROM employees

WHERE salary >
(
    SELECT avg_salary

    FROM average_salary
);

Sample Output

Explanation

The CTE calculates the company average salary once.

The main query compares every employee’s salary with that value.

Concepts Covered

• CTE
• Aggregate Functions
• AVG()

15. SQL Multi-Step Business Report Using Multiple CTEs

Problem Statement

A retail company wants to generate a report showing:

• Customers whose purchases exceed ₹10,000
• Their loyalty level

Use multiple CTEs.

Sample Table

customers

SQL Solution

WITH high_value AS
(
    SELECT
        customer_name,
        purchase_amount

    FROM customers

    WHERE purchase_amount > 10000
),

loyalty AS
(
    SELECT
        customer_name,
        purchase_amount,

        CASE

            WHEN purchase_amount >= 20000
            THEN 'Gold'

            ELSE 'Silver'

        END AS loyalty_level

    FROM high_value
)

SELECT *

FROM loyalty;

Sample Output

Explanation

This query uses multiple CTEs to solve a business problem in stages:

1. high_value filters premium customers.
2. loyalty assigns a loyalty category using a CASE statement.
3. The final query displays the completed report.

This modular approach is much easier to read and maintain than one large query.

Concepts Covered

• Multiple CTEs
• CASE Statement
• Business Reporting
• Multi-Step Query Design

Chapter Summary

In this chapter, you learned how to use Common Table Expressions (CTEs) to simplify SQL queries and improve readability.

A CTE is a temporary named result set created using the WITH keyword. Unlike subqueries, CTEs make complex SQL easier to understand, maintain, and debug.

Throughout this chapter, you practiced:

• Creating basic CTEs
• Filtering data using CTEs
• Using CTEs with GROUP BY
• Joining tables inside CTEs
• Using multiple CTEs in one query
• Using CTEs with window functions
• Writing recursive CTEs
• Comparing aggregated values
• Building multi-step business reports

These concepts are commonly used in modern SQL development, business intelligence, and data analytics projects.

Key Takeaways

• A CTE is created using the WITH keyword.
• A CTE exists only during the execution of a single SQL statement.
• CTEs improve query readability and organization.
• Multiple CTEs can be declared within one query.
• CTEs work well with JOIN, GROUP BY, CASE, and aggregate functions.
• Recursive CTEs are useful for hierarchical data.
• Window functions such as ROW_NUMBER() are frequently combined with CTEs.
• CTEs are widely used in reporting, dashboards, and ETL processes.
• Understanding CTEs is important for SQL interviews and real-world projects.
• CTEs often replace deeply nested subqueries with cleaner, more maintainable SQL.

Frequently Asked Questions (FAQs)

1. What is a Common Table Expression (CTE)?

A CTE is a temporary named result set that exists only for the duration of a single SQL query.

Example:

WITH high_salary AS
(
    SELECT employee_name,
           salary
    FROM employees
    WHERE salary > 50000
)

SELECT *

FROM high_salary;

2. Why should I use a CTE?

CTEs make SQL queries:

• Easier to read
• Easier to debug
• Easier to maintain
• Easier to reuse within the same query

3. What is the difference between a CTE and a Subquery?

4. Can multiple CTEs be used in one SQL query?

Yes.

Example:

WITH sales AS
(
    SELECT *
    FROM monthly_sales
),

customers AS
(
    SELECT *
    FROM customer_data
)

SELECT *

FROM sales;

5. What is a Recursive CTE?

A Recursive CTE repeatedly executes itself until no additional rows are returned.

It is commonly used for:

• Employee hierarchies
• Folder structures
• Category trees
• Organizational charts

6. Can I use JOIN inside a CTE?

Yes.

Example:

WITH employee_details AS
(
    SELECT
        e.employee_name,
        d.department_name

    FROM employees e

    INNER JOIN departments d
        ON e.department_id = d.department_id
)

SELECT *

FROM employee_details;

7. Can I use Aggregate Functions inside a CTE?

Yes.

Example:

WITH department_sales AS
(
    SELECT
        department,
        SUM(amount) AS total_sales

    FROM sales

    GROUP BY department
)

SELECT *

FROM department_sales;

8. Where are SQL CTEs used in real-world applications?

CTEs are widely used in:

• Business Intelligence Dashboards
• Financial Reporting
• HR Analytics
• Sales Reports
• Customer Analytics
• Inventory Management
• Banking Systems
• Healthcare Reporting
• ETL Pipelines
• Data Warehousing

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar