Window Functions vs CTEs: Concept

Window functions and CTEs are fundamentally different tools that people often confuse because both can work with aggregates. Window functions perform calculations across a set of rows related to the current row, while keeping all rows in the result. CTEs organize queries into named, reusable chunks. The key difference: Window functions are about WHAT you calculate (row-level analytics), CTEs are about HOW you structure your query (organization). You can use both together - CTE to prep data, window function to analyze it. Understanding this distinction prevents performance disasters.

**

**Window Functions: Row-Level Analytics**

Window functions calculate values for each row based on a "window" of related rows. Key characteristic: They don't reduce row count.

```sql

-- Every employee gets their department's average salary

SELECT

name,

salary,

AVG(salary) OVER (PARTITION BY department) as dept_avg

FROM employees;

-- Result: Same number of rows as input

-- Each row has its own dept_avg value

```

Common use cases:

1. **Rankings**: RANK(), DENSE_RANK(), ROW_NUMBER()

2. **Running totals**: SUM() OVER (ORDER BY date)

3. **Moving averages**: AVG() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

4. **Percentiles**: PERCENTILE_CONT() OVER ()

5. **Lead/Lag**: Compare current row with previous/next rows

**CTEs: Query Organization**

CTEs break complex queries into named, readable steps. Key characteristic: They organize query structure.

```sql

-- Break complex query into steps

WITH active_customers AS (

SELECT customer_id, signup_date

FROM customers

WHERE status = 'active'

),

recent_orders AS (

SELECT customer_id, COUNT(*) as order_count

FROM orders

WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY customer_id

)

SELECT

ac.customer_id,

ac.signup_date,

COALESCE(ro.order_count, 0) as recent_orders

FROM active_customers ac