CTE with JOIN

How can we calculate employee counts and then attach department names?

Advanced⏱ 3 Minutes⭐ 1160 XP

🤔 THE QUESTION

How can we calculate employee counts and then attach department names?

💡 WHAT IS IT?

CTEs can be joined to physical dimension tables just like standard database tables.

🎯 WHAT IS IT USED FOR?

Combining pre-aggregated metrics with dimension metadata, clean multi-step joins.

💻 EXAMPLE

WITH department_counts AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, dc.employee_count
FROM department_counts dc
JOIN departments d
ON dc.department_id = d.department_id;

🎯 Mission Objectives

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

✓ CTE + JOIN
✓ Dimension enrichment
✓ Pre-aggregated joins
✓ Clean SQL pipeline

🚀 Start Mission

← Prev (CTE with Aggregation)Next Lesson (Multiple CTEs) →