Basic CTE: Filter and Aggregate

basic

HR wants to see department statistics for recent hires only. Use CTE to filter first, then aggregate.

-- Using CTE for clarity

WITH recent_hires AS (

SELECT

employee_id,

name,

department,

salary,

hire_date

FROM employees

WHERE hire_date > '2023-01-01'

)

SELECT

department,

COUNT(*) AS new_employee_count,

ROUND(AVG(salary), 0) AS avg_starting_salary,

MIN(hire_date) AS first_hire,

MAX(hire_date) AS latest_hire

FROM recent_hires

GROUP BY department

ORDER BY new_employee_count DESC;

department | new_employee_count | avg_starting_salary | first_hire | latest_hire

Engineering | 45 | 85000 | 2023-01-05 | 2024-01-20

Sales | 32 | 72000 | 2023-01-10 | 2024-01-18

Marketing | 18 | 68000 | 2023-01-15 | 2024-01-15

CTE named "recent_hires" filters employees hired after 2023. Main query aggregates these filtered employees by department. Much more readable than nesting the filter inside the GROUP BY query. The CTE name clearly communicates what data we're working with.

All

Multiple CTEs: Customer Segmentation

intermediate

Marketing needs customer segments based on order history. Use multiple CTEs to break down the logic into clear steps.

-- Multi-step customer segmentation

WITH

-- Step 1: Calculate customer order statistics

customer_stats AS (

SELECT

c.customer_id,

c.name,

c.email,