Advanced SQL: Window Functions, CTEs, and Query Optimization

Window Functions

-- ROW_NUMBER: Assign a unique sequential integer per partition
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK: Same rank for ties, leaves gaps
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK: Same rank for ties, no gaps
SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Common Table Expressions (CTEs)

WITH department_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary
FROM employees e
JOIN department_avg da ON e.department = da.department
WHERE e.salary > da.avg_salary;

Query Optimization Tips

• Use EXPLAIN (or EXPLAIN ANALYZE) to view query plans.
• Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses.
• Avoid SELECT *; fetch only the columns you need.
• Use covering indexes to avoid table lookups entirely.