Analytics SQL for SQLs
Analytics SQL covers features for complex analysis in a single query. Common table expressions, written with WITH, name intermediate results and enable recursion for hierarchies. Window functions such as ROW_NUMBER, RANK, LAG and running SUM OVER compute values across related rows without collapsing them, which suits rankings, trends and period comparisons.
Stage: Advanced Querying · Part of the SQL roadmap · Last reviewed 19 November 2026
What to learn in Analytics SQL
- Break a long query into readable steps with WITH clauses
- Walk an employee hierarchy using a recursive CTE
- Rank rows within each group using ROW_NUMBER and PARTITION BY
- Compute a running total and a moving average with window frames
- Compare a value to the previous period using LAG
Analytics SQL interview questions
How do window functions differ from GROUP BY?
GROUP BY collapses rows into one row per group. A window function calculates over a set of related rows but keeps every original row, so you can show each order next to its customer's total or rank. The OVER clause defines the partition, order and frame.
What is the difference between RANK and DENSE_RANK?
Both give tied rows the same rank. RANK then skips numbers after a tie, so two rows at rank one are followed by rank three. DENSE_RANK does not skip, so the next rank is two. ROW_NUMBER ignores ties and assigns unique numbers.
Where Analytics SQL fits in the SQL roadmap
Analytics SQL belongs to the Advanced Querying stage (beginner level), which takes about 18 hours. Express complex logic.