CTE with Aggregation

How can we calculate department employee counts using a CTE?

Advanced⏱ 3 Minutes⭐ 1150 XP

🤔 THE QUESTION

How can we calculate department employee counts using a CTE?

💡 WHAT IS IT?

CTEs encapsulate aggregations so downstream queries can treat summary statistics as regular tabular data.

🎯 WHAT IS IT USED FOR?

Intermediate summary tables, metric staging, pre-calculated department statistics.

💻 EXAMPLE

WITH department_counts AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM department_counts;

🎯 Mission Objectives

Practice typing production-grade SQL code for CTE with Aggregation.

✓ CTE with GROUP BY
✓ Summary staging
✓ Encapsulated aggregation
✓ Modular SQL

🚀 Start Mission

← Prev (Basic CTE)Next Lesson (CTE with JOIN) →