SQL for Analytics
Analytical SQL is not CRUD. A transactional database is tuned for writing individual rows; the analyst almost always does the opposite — reads millions of rows, aggregates, ranks, and computes quantities across whole datasets. Three skills grow out of that, and they are the three most frequently tested in interviews. All three live in this topic.
The first is window functions: a way to compute something over a group of rows without collapsing them into one (a running total, a moving average, a rank within a category, the difference from yesterday). The second is analytics patterns: recurring query shapes an analyst writes again and again (gaps-and-islands, sessionization, funnels, retention, cohorts, deduplication). The third is performance: how to make all of that run over a 300M-row table by reading the EXPLAIN plan instead of guessing.
SQL keywords (OVER, PARTITION BY, GROUP BY, EXPLAIN) are written in uppercase English throughout — that is the convention in both locales.
Topic map
- Window functions —
OVER,PARTITION BY,ORDER BY;ROW_NUMBERvsRANKvsDENSE_RANK;LAG/LEAD; running totals and moving averages;ROWSvsRANGEframes; top-N per group and how a window differs fromGROUP BY. - Analytics patterns — gaps-and-islands, sessionization, funnels, retention and cohorts, deduplication, self-joins, pivot/unpivot, date-series generation, and the shape of a query over an event log.
- SQL performance — indexes (when they help and when they hurt), reading an
EXPLAINplan, avoiding full scans, join strategies, whySELECT *is a smell, partitioning, CTEs vs subqueries vs temp tables, and how cardinality drives the optimizer.
Common traps
| Mistake | Consequence |
|---|---|
Thinking PARTITION BY collapses rows like GROUP BY | Expecting one row per group where a window keeps every row |
Filtering a window function directly in WHERE | Syntax error; filtering on a window result needs a wrapping subquery/CTE |
Not distinguishing ROWS from RANGE in a frame | A running total "jumps" on tied dates or sums the wrong rows |
Writing DATE(created_at) = '...' over an index | A function on the column makes the predicate non-sargable; the index is skipped |
SELECT * in a columnar store | Every column is read from disk instead of the few you need — costly, though a single-column scan is cheap |
Papering over join duplicates with SELECT DISTINCT | The real fan-out cause is hidden, and the sort/dedup is expensive |
Interview relevance
SQL is asked as a test of your mental model, not your syntax recall. A candidate who says "a window function computes over a set of rows but returns each row, so it can't go in WHERE — you need an outer query" immediately gets ahead of one reciting a function list. The tasks almost always reduce to one of this topic's patterns: compute retention, split a stream into sessions, take top-N per category, find the longest streak.
Typical checks:
- The difference between
PARTITION BYandGROUP BYand why a window keeps rows. - How
ROW_NUMBER,RANK, andDENSE_RANKbehave on ties. - How to build a running total and a moving average, and where
ROWSdiverges fromRANGE. - How to read an
EXPLAINplan, make a predicate sargable, and choose a broadcast vs shuffle join.
Common wrong answer: "a window function is the same as GROUP BY, just written differently". In reality GROUP BY collapses a group into one row, while a window computes a value for each row and keeps them all — which is exactly why it is how ranks, differences, and running totals are computed.