🔥 Now, let’s move to the next topic:

✅ CTE (Common Table Expressions)

🧠 1. What is a CTE?

A CTE (Common Table Expression) is a temporary result set
👉 defined using WITH
👉 used to simplify complex queries

Think like this 👇
👉 “Create a temporary table → use it in your query”

⚡ 2. Basic Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

🎯 3. Simple Example

👉 Get employees with salary > 50k

WITH high_salary AS (
    SELECT * FROM employees
    WHERE salary > 50000
)
SELECT * FROM high_salary;

✔ Makes query more readable

🔥 4. CTE with Aggregation

👉 Average salary per department

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_avg;

⚡ 5. CTE vs Subquery

CTE ->
More readable, Reusable Better for complex queries

Subquery -> Hard to read Not reusable

🎯 6. Real Example (Interview Level)

👉 Employees earning above department average

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)

SELECT e.name, e.salary, e.department
FROM employees e
JOIN dept_avg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

🎯 7. Practice Tasks

1. Create CTE for employees with salary > 40k
2. Find average salary using CTE
3. Get employees above average salary using CTE
4. Count employees per department using CTE
5. Find highest salary per department using CTE

🔥 Here are the solutions for CTE practice tasks

✅ 1. Create CTE for employees with salary > 40k

WITH high_salary AS (
    SELECT * FROM employees
    WHERE salary > 40000
)
SELECT * FROM high_salary;

✅ 2. Find average salary using CTE

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT * FROM avg_sal;

✅ 3. Get employees above average salary using CTE

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.*
FROM employees e, avg_sal a
WHERE e.salary > a.avg_salary;

👉 Alternative (JOIN style):

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.*
FROM employees e
JOIN avg_sal a
ON e.salary > a.avg_salary;

✅ 4. Count employees per department using CTE

WITH dept_count AS (
    SELECT department, COUNT(*) AS total_emp
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_count;

✅ 5. Find highest salary per department using CTE

WITH max_sal AS (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM max_sal;

⚡ Mini Challenge 🔥

👉 Find top 2 highest salary employees per department using CTE

⚡ Mini Challenge Solution 🔥

WITH ranked_emp AS (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked_emp
WHERE rn <= 2;

🔥 Pro Tip:
Whenever query looks messy:
👉 Replace subquery with CTE

Double Tap ❤️ For More