Advanced SQL Techniques Every Data Analyst Should Know

You can write a SELECT statement. You can JOIN tables and slap on a WHERE clause. But somewhere between "I know SQL" and "I really know SQL" lies a gap that separates analysts who get things done from analysts who get things done fast, elegantly, and correctly. This article covers the techniques that live in that gap. 1. Window Functions Most analysts discover GROUP BY early and lean on it forever. Window functions do something fundamentally different — they let you compute aggregates without collapsing rows . SELECT employee_id , department , salary , AVG ( salary ) OVER ( PARTITION BY department ) AS dept_avg , salary - AVG ( salary ) OVER ( PARTITION BY department ) AS diff_from_avg FROM employees ; You get one row per employee, but each row carries its department's average alongside it. No subquery. No self-join. No mess. Running totals and moving averages SELECT order_date , revenue , SUM ( revenue ) OVER ( ORDER BY order_date ) AS running_total , AVG ( revenue ) OVER ( ORDER BY o