Advanced SQL for Data Analyticss
Advanced SQL covers the constructs used for multi-step analysis: common table expressions, subqueries and window functions, plus the basics of making queries run fast. Window functions such as ROW_NUMBER and LAG let an analyst rank and compare rows without collapsing them. It follows querying on the Data Analytics path.
Stage: SQL · Part of the Data Analytics roadmap · Last reviewed 19 November 2026
What to learn in Advanced SQL
- Break a long query into readable steps using CTEs
- Rank rows within a group using ROW_NUMBER, RANK or DENSE_RANK
- Compute a running total or a month-over-month change with window functions
- Use a correlated subquery and recognise when a join is simpler
- Read an EXPLAIN output to see whether an index is used
Advanced SQL interview questions
How do you get the latest order per customer using a window function?
Number each customer's orders with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in a CTE or subquery, then keep rows where the number is 1. This returns one row per customer, and a tiebreaker column makes ties deterministic.
What is the difference between RANK and DENSE_RANK?
Both give equal values the same rank. RANK then skips numbers after a tie, giving 1, 1, 3, while DENSE_RANK does not skip, giving 1, 1, 2. ROW_NUMBER never repeats. The choice depends on whether gaps in the sequence are acceptable for a top-N list.
Where Advanced SQL fits in the Data Analytics roadmap
Advanced SQL belongs to the SQL stage (beginner level), which takes about 8 hours. The analyst's main tool.
Other units in this stage
Back to the full Data Analytics roadmap