🚀🔥𝗚𝗢𝗢𝗚𝗟𝗘 𝗖𝗟𝗢𝗨𝗗 – 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗢𝗨𝗥𝗦𝗘𝗦! ☁️📊

Learn *Data Analytics, BigQuery, Data Engineering & Looker* for FREE!

✅ Beginner-Friendly Learning
✅ Build In-Demand Tech Skills
✅ Boost Your Resume & Career Opportunities

🎯 Perfect for Students, Freshers & Job Seekers!

🔗 𝗟𝗶𝗻𝗸 :- 👇

https://pdlink.in/4AttkUb

📲 Save & Share with your friends! 🔥

🚀 Data Science Roadmap 2026

📍 Phase 3: SQL for Data Science

📖 Topic 13: Common Table Expressions (CTEs) in SQL

Today, let's learn about one of the most useful SQL concepts: Common Table Expressions (CTEs).

CTEs help us write cleaner, more readable SQL queries by breaking complex queries into smaller, manageable steps.

They are frequently used in data analysis, reporting, and SQL interviews.

🔹 1. What is a CTE in SQL?

CTE stands for Common Table Expression.

It is a temporary named result set that you define using the WITH keyword and use within a single SQL statement.

Think of it as creating a temporary result that you can reference in your main query.

Example:

Suppose you want to find employees earning more than 60,000.

Without a CTE:

SELECT employee_name, salary
FROM employees
WHERE salary > 60000;

Using a CTE:

WITH high_salary_employees AS (
    SELECT employee_name, salary
    FROM employees
    WHERE salary > 60000
)
SELECT **
FROM high_salary_employees;

Here:

• The CTE is created using WITH.

• The inner query selects employees earning more than 60,000.

• The outer query retrieves the result from the CTE.

🔹 2. Basic Syntax of a CTE

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT **
FROM cte_name;

Important parts:

• WITH starts the CTE.

• cte_name is the name you give to the temporary result.

• AS introduces the query.

• The query inside parentheses defines the CTE.

• The main query uses the CTE.

🔹 3. CTE with WHERE Condition

You can use CTEs to filter records before applying further conditions.

Example: Find employees earning more than 50,000 and display only those earning more than 70,000.

WITH employee_data AS (
    SELECT employee_name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT **
FROM employee_data
WHERE salary > 70000;

The CTE first selects employees earning above 50,000. The outer query further filters the result.

🔹 4. CTE with GROUP BY

CTEs are very useful when working with aggregate functions.

Example: Calculate the average salary for each department.

WITH department_salary AS (
    SELECT
        department,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT **
FROM department_salary;

Result:

You can now use this result in another query.

For example, find departments with an average salary above 70,000.

WITH department_salary AS (
    SELECT
        department,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT **
FROM department_salary
WHERE average_salary > 70000;

🔹 5. Using Multiple CTEs

You can create multiple CTEs within a single SQL query. This is especially useful when solving complex business problems step by step.

Suppose you want to:

• Calculate total sales by product.

• Find products generating more than 1,00,000 in sales.

WITH product_sales AS (
    SELECT
        product_id,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
),
high_sales_products AS (
    SELECT **
    FROM product_sales
    WHERE total_sales > 100000
)
SELECT **
FROM high_sales_products;

• The first CTE calculates total sales.

• The second CTE filters high-performing products.

• The final query retrieves the result.

Important: Separate multiple CTE definitions with commas.

🔹 6. CTE with JOIN

You can combine CTEs with JOIN operations.

Example: Find total sales generated by each customer.

Suppose you have:

• customers

• orders

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    cs.total_sales
FROM customers c
INNER JOIN customer_sales cs
    ON c.customer_id = cs.customer_id;

The CTE calculates total sales for each customer. The main query joins that result with the customers table to retrieve customer names.

🔹 7. CTE vs Subquery

This is a common SQL interview question.

Example of a subquery:

SELECT **
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Equivalent approach using a CTE:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT **
FROM employees
WHERE salary > (
    SELECT avg_salary
    FROM average_salary
);

Both approaches solve the same problem.

🔹 8. CTE vs View

Another important interview concept.

CTE:

WITH employee_data AS (
    SELECT **
    FROM employees
)
SELECT **
FROM employee_data;

View:

CREATE VIEW employee_data AS
SELECT **
FROM employees;

The main difference is that a CTE is temporary within a statement, while a view has a persistent definition in the database.

🔹 9. Real-World Data Science Use Cases

CTEs are widely used in analytical SQL queries to:

• Calculate monthly revenue before comparing sales performance.

• Identify high-value customers.

• Find products with declining sales.

• Calculate department-wise employee statistics.

• Prepare clean datasets for reporting.

• Break complex business queries into smaller steps.

Example: Find customers whose total spending is greater than 50,000.

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spending
FROM customer_spending
WHERE total_spending > 50000;

This is useful for customer segmentation and identifying high-value customers.

🔹 10. Common Mistakes to Avoid

• Forgetting the WITH keyword.

• Forgetting parentheses around the CTE query.

• Missing commas between multiple CTE definitions.

• Referencing a CTE outside the SQL statement where it is defined.

• Assuming a CTE permanently stores data.

• Giving CTEs unclear names.

• Assuming CTEs always improve query performance.

Important: A CTE improves query organization and readability, but it does not automatically make a query faster. Its execution behavior depends on the database system and query optimizer.

🔹 11. SQL Interview Questions

Q1. What is a CTE in SQL?

A CTE is a temporary named result set defined using the WITH keyword and used within a single SQL statement.

Q2. Which keyword is used to create a CTE?

The WITH keyword.

Q3. Can we create multiple CTEs in one query?

Yes. Multiple CTEs can be defined in a single WITH clause, separated by commas.

Q4. What is the difference between a CTE and a view?

A CTE exists only for one SQL statement, whereas a view is a stored database object that can be reused across queries.

Q5. Do CTEs improve query performance?

Not necessarily. CTEs mainly improve readability and organization. Performance depends on the database optimizer and query structure.

🎯 Practice Questions

Try writing SQL queries for these problems:

• Create a CTE to find employees earning more than 60,000.

• Calculate department-wise average salary using a CTE.

• Find customers whose total spending exceeds 50,000.

• Create two CTEs to calculate and filter product sales.

• Use a CTE with JOIN to display customer names and their total orders.

🎯 Key Takeaways

• CTE stands for Common Table Expression.

• CTEs are created using the WITH keyword.

• They make complex SQL queries easier to understand.

• You can create multiple CTEs in one query.

• CTEs can be used with JOIN, GROUP BY, and WHERE.

• A CTE exists only within its SQL statement.

• CTEs and views are different.

• CTEs improve readability but do not guarantee better performance.

Double Tap ❤️ For More

𝟯 𝗙𝗥𝗘𝗘 𝗛𝘂𝗯𝗦𝗽𝗼𝘁 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝗳𝗼𝗿 𝗦𝘁𝘂𝗱𝗲𝗻𝘁𝘀 & 𝗙𝗿𝗲𝘀𝗵𝗲𝗿𝘀 𝗶𝗻 𝟮𝟬𝟮𝟲🎓

💫HubSpot Academy offers three free online certifications that match these interests.

💫Pick one free course and try it out on your next college event and the certification is free.

🔗 𝗟𝗶𝗻𝗸 :- 👇

https://pdlink.in/4iM5xaQ

📲 *Save & Share with your friends! 🔥*

𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗧𝗼 𝗚𝗲𝘁 𝗬𝗼𝘂𝗿 𝗗𝗿𝗲𝗮𝗺 𝗝𝗼𝗯 𝗜𝗡 𝗚𝗼𝗼𝗴𝗹𝗲🔥

Discover *4 FREE resources* to help you prepare for a career at Google!

✅ Improve Your Coding Skills
✅ Strengthen Interview Preparation
✅ Build Technical Knowledge
✅ Prepare for Top Tech Opportunities

🎓Perfect for Students, Freshers & Aspiring Software Engineers!

🔗 𝗟𝗶𝗻𝗸 👇:-

https://pdlink.in/4AwRPQy

📲 SAVE & SHARE with friends dreaming of working at Google!