Window Functions vs Subqueries: A Face Off

Welcome to **Day 74**. We've learned both Subqueries and Window Functions. They often overlap, but which one is better?

The Battle: Comparing to Average

The Subquery Way

SELECT name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

**Pros**: Works in almost every SQL version since the 1970s. Very simple to read.

The Window Function Way

WITH ranked AS (

SELECT name, salary, AVG(salary) OVER() as avg_salary

FROM employees

)

SELECT * FROM ranked WHERE salary > avg_salary;

**Pros**: Allows you to easily add more averages (e.g., avg by department) without adding more nested queries.

The Verdict

Use **Subqueries** for simple "Single Value" comparisons (like the example above).

Use **Window Functions** for "Row-Level Details" where you need to see the average *and* the individual data side-by-side.

Performance Note

Always check your query plan! Sometimes the database optimizer handles a Join + Group By faster than a Window function, even if the Window function looks "cleaner."

Your Task for Today

Refactor a query that uses a subquery for an average into a window function. Which one do you prefer?

*Day 75: Project—User Behavioral Analysis Dashboard.*