Test SQL skills using advanced window functions such as LAG, LEAD, and DENSE_RANK.

LAG, LEAD, and DENSE_RANK are powerful window functions. LAG(column, n) returns the value n rows before the current row; LEAD does the opposite. DENSE_RANK assigns ranks with no gaps for ties. Example—period-over-period comparison: SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue, revenue - LAG(revenue, 1) OVER (ORDER BY date) AS growth FROM sales; For ranking within partitions: SELECT employee_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees; Use cases: YoY/MoM growth, identifying gaps in sequences, top-N per group. Best practice: always specify ORDER BY in window; use PARTITION BY to avoid cross-partition leaks; be mindful of NULL handling with LAG/LEAD. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.