CTE Business Query

How can we identify departments with high total salary costs?

Advanced⏱ 3 Minutes⭐ 1200 XP

🤔 THE QUESTION

How can we identify departments with high total salary costs?

💡 WHAT IS IT?

CTEs simplify enterprise business logic by separating cost aggregation from dimension lookups.

🎯 WHAT IS IT USED FOR?

Budget threshold audits, department expenditure analysis, enterprise financial reporting.

💻 EXAMPLE

WITH department_cost AS (
    SELECT
        department_id,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, dc.total_salary
FROM department_cost dc
JOIN departments d
ON dc.department_id = d.department_id
WHERE dc.total_salary > 1000000;

🎯 Mission Objectives

Practice typing production-grade SQL code for CTE Business Query.

✓ CTE business query
✓ Budget expenditure
✓ JOIN with threshold
✓ Financial reporting

🚀 Start Mission

← Prev (CTE + Ranking Preparation)Next Lesson (CTE Pipeline) →