Analyst SQL
Move from writing isolated queries to building reliable analysis that handles real business logic.
Reusable logic
CTEs: A common table expression names a query result for the rest of one statement. Use it to make the stages of your reasoning visible.
Subqueries: A subquery is a query nested inside another statement. IN is one way to use it, not the definition of a subquery.
Views: A view gives a reusable name to a stored SELECT definition. People can query it without rewriting the same reporting logic.
Analytical patterns
Window functions: A window calculation can summarize a related set of rows while keeping the original row-level detail.
Ranking and offsets: Window functions let you number positions, recognize ties, and compare a row with its neighbors.
Running and rolling metrics: A cumulative total starts at the beginning. A rolling metric uses a moving portion of the sequence. Define the window before naming the metric.
Robust transformations
Advanced CASE: Real business rules combine several fields and sometimes depend on missing values. Make the priority of the rules visible.
Date and string functions: Dates and labels often arrive in a form that is hard to group or compare. Transform them with a defined purpose and a known SQL dialect.
Type conversion and NULL handling: A calculation should distinguish missing information, zero values, invalid types, and undefined results.
Analyst discipline
Grain and fan-out checks: A query can run correctly and still give the wrong number. Row multiplication is one of the most common reasons.
Set operations: Set operations combine compatible query outputs vertically, rather than matching columns horizontally with a join.
Query validation: Before reporting a metric, prove that the query answers the intended question and survives the data’s awkward cases.