Analytical SQL: execution order and fundamentals
SQL (Structured Query Language) is the standard language for querying relational databases. In a data analysis context, queries go far beyond a simple SELECT * FROM table: they combine aggregations, multiple joins, subqueries and analytical functions to answer precise business questions over millions of rows.
Basic aggregation functions — COUNT, SUM, AVG, MIN, MAX — combined with GROUP BY and HAVING form the backbone of analysis. But before moving to advanced features, understanding the logical execution order of SQL clauses is essential for writing correct queries and debugging them efficiently.
The logical SQL execution order
The order in which you write SQL clauses (SELECT, FROM, WHERE...) is different from the order in which the SQL engine logically executes them. Understanding this execution order explains why certain errors occur and how to avoid them. Logical execution order: (1) FROM and JOIN — determine the working row set. (2) WHERE — filter rows before any aggregation. (3) GROUP BY — group the rows. (4) HAVING — filter groups after aggregation. (5) SELECT — project columns and apply expressions. (6) DISTINCT — deduplicate. (7) ORDER BY — sort. (8) LIMIT/OFFSET — paginate.
This sequence explains why you cannot use a SELECT alias in WHERE (WHERE is evaluated before SELECT), why HAVING can use aggregate functions but WHERE cannot, and why window functions must live in SELECT or ORDER BY (they run after GROUP BY and HAVING). A classic mistake: WHERE calculated_alias = ... — the engine does not yet know this alias at the time WHERE is evaluated.
JOIN types: INNER, LEFT, SELF, LATERAL and CROSS
INNER JOIN returns only rows that have a match in both tables — unmatched rows are dropped from both sides. LEFT JOIN (or LEFT OUTER JOIN) returns every row from the left table, with NULL for right-side columns when there is no match — essential for finding 'orphan' records (customers with no orders, products with no sales).
A SELF JOIN joins a table with itself — useful for comparing rows within the same table (finding duplicates, comparing employees with their manager in the same table). A LATERAL JOIN (or CROSS JOIN LATERAL, called CROSS APPLY in SQL Server) lets each row of the left table reference a subquery or function that runs separately for that row — useful for TOP-N per group without a window function, or for exploding arrays. CROSS JOIN produces the cartesian product of two tables (every row of A times every row of B) — useful for generating combinations or a complete date/customer grid.
SQL in modern analytical warehouses
Modern analytical data warehouses — BigQuery, Snowflake, Amazon Redshift, Databricks SQL, ClickHouse and DuckDB — all use SQL as their primary interface, even though they store data in columnar format (Parquet, ORC) rather than row-based. SQL knowledge is therefore largely transferable from one tool to another. The differences lie in tool-specific extensions: QUALIFY in BigQuery and Snowflake, ARRAY_AGG and STRUCT in BigQuery, native PIVOT in Snowflake, and window frame syntax nuances.
DuckDB is an in-process OLAP SQL engine (like SQLite for OLAP) that lets you query Parquet, CSV and JSON files directly in SQL with no server involved. It is the ideal tool for local exploration of medium-sized datasets (tens of GB) and for lightweight transformation scripts. It supports every window function, recursive CTEs and native PIVOT.
Window functions: the complete guide
Window functions are the most powerful SQL feature for data analysis. They calculate a value for each row based on a related set of rows (the 'window'), without reducing the number of rows in the result — unlike GROUP BY, which collapses individual rows into an aggregate.
Full syntax: FUNCTION() OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN n PRECEDING AND m FOLLOWING). The OVER clause defines the calculation window: PARTITION BY splits data into independent groups (like GROUP BY but without aggregating), ORDER BY orders rows within each partition, and the frame clause (ROWS/RANGE BETWEEN) precisely delimits which rows are included in each row's calculation.
Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
ROW_NUMBER() assigns a unique, sequential number to every row in the partition, ignoring ties. RANK() assigns the same rank to ties but leaves numeric 'gaps' (1, 1, 3 — rank 2 does not exist). DENSE_RANK() assigns the same rank to ties without gaps (1, 1, 2). Typical use case: 'for each customer, return only their most recent order' — solution: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC), then filter WHERE rn = 1.
NTILE(n) splits the rows of a partition into n groups of roughly equal size and assigns each row its group number. NTILE(4) OVER (ORDER BY revenue DESC) segments customers into revenue quartiles (Q1 = top 25%). NTILE(100) gives you percentiles. Useful for segmentation and distribution analysis without manually computing quantiles.
LAG, LEAD, FIRST_VALUE and LAST_VALUE
LAG(column, n, default) returns the value from n rows before the current row within the window (default if absent). LEAD(column, n, default) returns the value n rows after. A classic analytical use case: calculating month-over-month growth without a self-join. SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prior_revenue, (revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) * 100 AS growth_pct.
FIRST_VALUE(column) OVER (...) returns the value of the first row of the window per the ORDER BY — useful for comparing each row to the first value of the series. LAST_VALUE(column) OVER (...) returns the value of the last row — but beware: by default, the frame clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means LAST_VALUE actually returns the current row's value, not the partition's last value. To fix it: LAST_VALUE(column) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).
Rolling aggregates and the frame clause
Aggregate functions become window functions once you add OVER. SUM(revenue) OVER (PARTITION BY year ORDER BY month) computes a monthly running total per year (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default frame when ORDER BY is present). AVG(score) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) computes a 7-day rolling average. COUNT(*) OVER () returns the total row count of the whole table — useful for computing proportions.
The distinction between ROWS and RANGE in the frame clause is subtle but important. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING literally includes the 1 row before and after by position. RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING includes rows whose ORDER BY value falls within [value-1, value+1] — which can include more rows when values tie. For rolling calculations by date or continuous value, ROWS is generally more predictable.
Window functions are defined in the SQL:2003 standard and supported by PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.25+, BigQuery, Snowflake, Redshift, DuckDB, ClickHouse and nearly every modern analytical warehouse. Syntax learned in PostgreSQL works in BigQuery with only minor variations.
SQL:2003 Standard — ISO/IEC 9075CTEs: Common Table Expressions for readable queries
A CTE (WITH name AS (...)) is a named subquery that can be referenced multiple times in the main query or in subsequent CTEs. It dramatically improves the readability and maintainability of complex analytical queries — it is the SQL equivalent of named functions in application code.
The key advantage over nested subqueries: the CTE is defined once at the top, referenced by name, and the reader follows the logic step by step. A complex analytical query can be broken down into 5-6 successive CTEs, each performing one precise, clearly named operation. CTEs can reference one another in order (CTE_B can use CTE_A if CTE_A is defined first), but not circularly, except with WITH RECURSIVE.
Materialized vs inlined CTEs
By default, most SQL engines (PostgreSQL, BigQuery, Snowflake) treat CTEs as optimization barriers, or materialize them — meaning they run the subquery once and temporarily store the result before using it. In PostgreSQL up to version 11, CTEs were always materialized (which could hurt performance if the planner would have preferred to push filters inward). Since PostgreSQL 12, you can force behavior with WITH cte AS MATERIALIZED (...) or WITH cte AS NOT MATERIALIZED (...).
In analytical warehouses like BigQuery or Snowflake, CTEs are generally inlined (the engine treats them as subqueries and optimizes the overall plan). The practical implication: do not rely on CTEs to force separate execution in analytical warehouses — use a temporary table or a materialized view if you need that.
Recursive CTEs: hierarchies, graphs and sequences
A recursive CTE (WITH RECURSIVE in PostgreSQL, WITH RECURSIVE in DuckDB, natively supported without the keyword in BigQuery and Snowflake) references itself to traverse hierarchical structures: org charts (all subordinates of a manager n levels deep), nested categories (a product category tree), dependency graphs (all downstream datasets affected by a change).
Structure: an anchor part (base case — the roots of the hierarchy) and a recursive part (WITH RECURSIVE cte AS (SELECT id, parent_id, 1 AS level FROM table WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, c.level + 1 FROM table t JOIN cte c ON t.parent_id = c.id)). The recursion stops once the recursive part stops producing rows. Watch out for cycles (non-acyclic graphs), which would create infinite recursion: use a visited array or a MAX DEPTH guard.
CTE = temporary within the query, not stored, scope limited to the current query. Subquery = embedded in WHERE, FROM or HAVING — less readable when complex, but sometimes optimized differently by the planner. View = stored in the database, accessible from multiple queries, no parameters. Materialized view = stored and pre-computed, refreshed periodically. Rule of thumb: CTE for the readability of a one-off complex query, materialized view for an expensive aggregation reused often.
QUALIFY, PIVOT, JSON functions and advanced analytics
Beyond standard window functions, modern analytical warehouses offer powerful SQL extensions that simplify queries that would otherwise be tedious to write. QUALIFY, PIVOT/UNPIVOT, JSON functions and conditional aggregations are all part of a senior Data Analyst's advanced toolkit.
QUALIFY: filtering on window functions without a subquery
QUALIFY is a clause available in BigQuery, Snowflake (and DuckDB) that filters rows after window functions are evaluated — the equivalent of HAVING for window functions. Without QUALIFY, filtering on a window function's result requires a subquery or a CTE. With QUALIFY: SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC) AS rn FROM orders QUALIFY rn = 1 — returns each customer's most recent order in a single query with no CTE.
QUALIFY runs after SELECT and window functions, but before ORDER BY — making it the most elegant tool for patterns like 'latest record per group', 'TOP-N per category', or 'filter on a percentile rank'. It is one of the most frequently asked questions about BigQuery/Snowflake vs PostgreSQL differences in Data Engineering interviews.
PIVOT, UNPIVOT and JSON functions
PIVOT (native in Snowflake, SQL Server and Oracle; via CASE WHEN in PostgreSQL and BigQuery) turns row values into columns — useful for turning a long table (one row per metric per date) into a wide table (one column per metric). Snowflake also supports UNPIVOT for the reverse operation (columns to rows).
JSON functions are essential for analyzing semi-structured columns. In BigQuery: JSON_EXTRACT_SCALAR, JSON_EXTRACT_ARRAY, TO_JSON_STRING. In Snowflake: the VARIANT column type with the :: notation (SELECT col:field::string AS field FROM table). In PostgreSQL: ->> to extract a JSON field as text, -> to extract it as JSON. ARRAY_AGG() aggregates multiple rows into a single array — useful for building per-user event histories in order.
Conditional aggregations and FILTER
SUM(CASE WHEN status = 'won' THEN amount ELSE 0 END) computes a conditional sum — useful for pivoting multiple metrics into a single query. The FILTER syntax (more readable, available in PostgreSQL and DuckDB) does the same thing: SUM(amount) FILTER (WHERE status = 'won'). COUNTIF(condition) in BigQuery is shorthand for COUNT(CASE WHEN condition THEN 1 END). These patterns avoid writing multiple subqueries to compute metrics on different subsets of the same table.
Window functions with UNBOUNDED PRECEDING (a running total from the start) are generally efficient because the engine optimizes them in a single pass. However, several window functions with different PARTITION BY or ORDER BY clauses in the same query can require multiple sorts of the table — expensive on tables with billions of rows. In that case, computing window functions in separate CTEs or intermediate tables can improve performance.
SQL optimization: EXPLAIN ANALYZE and columnar warehouses
A SQL query can return the same correct result in 50ms or 5 minutes depending on whether it uses indexes and the storage schema properly. Understanding the execution plan is the key skill for optimizing a slow query, whether you're on PostgreSQL, BigQuery or Snowflake.
EXPLAIN ANALYZE: reading the execution plan
EXPLAIN shows the planned execution plan without running the query. EXPLAIN ANALYZE runs the query and compares estimated costs to real ones. Critical elements to watch: Seq Scan (sequential read of the whole table — often to replace with an Index Scan), Hash Join vs Nested Loop (Hash Join is generally more efficient for large tables, Nested Loop for small ones), and estimated vs actual row counts (a large gap signals stale table statistics — refresh with ANALYZE).
In BigQuery, the equivalent is Job Information → Execution details, which shows the plan's stages, the volume of data scanned per stage, and timings. In Snowflake, the Query Profile shows a graph of the plan with time and bytes processed by each operator. The key metric in BigQuery is bytes scanned — since billing is based on the amount of data scanned, not compute time.
Optimization in columnar warehouses: partitioning and clustering
In columnar analytical warehouses (BigQuery, Snowflake, Redshift), the traditional B-tree index does not exist. Optimization relies on partitioning and clustering instead. In BigQuery: partitioning by DATE or TIMESTAMP eliminates irrelevant partitions before the scan (partition pruning). Clustering (on 1 to 4 columns) co-locates similar data within storage blocks — queries that filter on a clustered column scan fewer blocks.
In Snowflake: the clustering key (CLUSTER BY) plays the same role. 50-500 MB micro-partitions are automatically sorted according to the cluster key, letting the engine prune irrelevant micro-partitions. Search Optimization Service and materialized views help optimize frequent, expensive queries on low-cardinality columns.
Common SQL anti-patterns to avoid
SELECT * on a wide table in a columnar warehouse is particularly costly: the columnar format lets you read only the columns you need, but SELECT * forces every column to be read — which can multiply costs by 10x or 100x. Functions applied to partitioned columns inside WHERE (WHERE EXTRACT(YEAR FROM date) = 2026) prevent partition pruning — prefer WHERE date BETWEEN '2026-01-01' AND '2026-12-31'.
Correlated subqueries (executed once per row of the outer query) are often replaceable with a JOIN or a CTE with a grouped aggregation. Accidental CROSS JOINs (a missing ON condition in a JOIN) produce a cartesian product and blow up the volume processed. In BigQuery, an unfiltered CROSS JOIN can generate enormous costs on large tables — use CROSS JOIN UNNEST for arrays, or LATERAL for per-row subqueries.
Essential analytical SQL patterns
Certain SQL patterns come up over and over in a Data Analyst's daily work. Knowing them by heart considerably speeds up productivity and avoids reinventing solutions every time.
Cohort and retention analysis
Cohort analysis groups users by acquisition period and tracks their behavior over time — it is the most important retention pattern in product analytics. SQL structure: (1) a 'first_activity' CTE: SELECT user_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month FROM events GROUP BY user_id. (2) an 'activity' CTE: SELECT e.user_id, fa.cohort_month, DATEDIFF(month, fa.cohort_month, DATE_TRUNC('month', e.event_date)) AS months_since_first FROM events e JOIN first_activity fa ON e.user_id = fa.user_id. (3) SELECT cohort_month, months_since_first, COUNT(DISTINCT user_id) AS retained_users.
This pattern measures retention rate, churn and long-term value by acquisition cohort. Dividing retained_users by the cohort's month-0 count gives the retention rate as a percentage. Pivoting (QUALIFY + CASE WHEN, or native PIVOT) turns this long table into a readable cohort triangle.
Funnel analysis (conversion funnel)
Funnel analysis measures the completion rate of each step in a user journey (sign-up → activation → first purchase → retention). SQL pattern: COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) AS step1, COUNT(DISTINCT CASE WHEN event = 'activation' THEN user_id END) AS step2, COUNT(DISTINCT CASE WHEN event = 'first_purchase' THEN user_id END) AS step3. Conversion rate between steps = step2 / step1.
For an ordered funnel (the user must complete step N before step N+1 within a certain window), you use window functions: LAG to verify the previous step happened first, plus a filter on the date delta. Snowflake and BigQuery offer MATCH_RECOGNIZE or native functions for ordered funnels with time constraints.
Deduplication and getting the latest version of a record
Deduplication is one of the most common needs in Data Engineering: extracting a single row per entity (a customer's latest state, the most recent transaction per order). Standard pattern: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1.
With QUALIFY (BigQuery, Snowflake, DuckDB): SELECT * FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) = 1 — one query instead of a nested one. For deduplication in dbt, the 'incremental' materialization with a unique_key and the 'merge' strategy handles deduplication automatically on every incremental run.
dbt (data build tool) turns SQL into an industrialized transformation framework: models are .sql files versioned in Git, the dependency DAG is detected automatically, tests (unique, not_null, referential integrity, custom) run in CI/CD, and documentation (with lineage) is generated automatically. dbt has become the de facto standard for SQL transformation in modern data stacks.
Anchoring analytical SQL with spaced repetition
SQL is a practical skill acquired by repeating patterns. Passively reading a SQL guide is not enough: you need to write the queries, run into the errors (wrong execution order, LAST_VALUE with the wrong frame clause) and recall syntaxes from memory. Flashcards accelerate this anchoring by forcing active recall of syntaxes and distinctions.
The most useful cards for Data Analyst and Data Engineer interviews: the full syntax of a window function (OVER, PARTITION BY, ORDER BY, frame clause), RANK vs DENSE_RANK vs ROW_NUMBER (with the tie example), QUALIFY and its advantage over a CTE, ROWS vs RANGE in the frame clause, and the 3 columnar-optimization anti-patterns.
Interview priorities: (1) The OVER (PARTITION BY ... ORDER BY ...) syntax and what each clause does. (2) The difference between ROW_NUMBER / RANK / DENSE_RANK with ties. (3) LAG(col, 1) vs LEAD(col, 1) with a default value. (4) LAST_VALUE with the UNBOUNDED FOLLOWING frame. (5) QUALIFY vs CTE for filtering on a window function. (6) Partition pruning in BigQuery/Snowflake and why EXTRACT(YEAR FROM date) prevents it.
Explore the Data & AI cluster
Frequently asked questions about SQL and data analysis
What is the difference between GROUP BY and a window function?
GROUP BY reduces the number of rows in the result by aggregating: 100 orders become 12 rows (one per month). A window function (OVER) computes a value for each row while considering other rows in the window, without reducing the result: 100 orders remain 100 rows, each with its own running total. GROUP BY collapses individual rows; window functions keep them.
What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
All three rank rows within a window. ROW_NUMBER() assigns a unique, sequential number even for tied values (1, 2, 3, 4). RANK() assigns the same rank to ties but leaves gaps (1, 1, 3 — rank 2 is skipped). DENSE_RANK() assigns the same rank to ties without gaps (1, 1, 2). To extract a single row per group (latest purchase, first event), ROW_NUMBER is the right choice.
What are LAG and LEAD used for?
LAG(col, n, default) returns the value from n rows earlier in the window. LEAD(col, n, default) returns the value n rows later. Typical use: calculating month-over-month growth without a self-join — revenue - LAG(revenue, 1) OVER (ORDER BY month). The default parameter avoids NULLs for the first or last row of the series.
What is a CTE and why use one?
A CTE (Common Table Expression) is a named subquery defined with WITH name AS (...) before the main query. It improves readability by breaking a complex query into named, logical steps, can be referenced multiple times, and supports recursive CTEs (WITH RECURSIVE) for hierarchies. It's an alternative to unreadable nested subqueries.
What is QUALIFY in BigQuery and Snowflake?
QUALIFY is a clause that filters rows after window functions are evaluated — the equivalent of HAVING for window functions. Without QUALIFY, filtering on a window function's result requires wrapping it in a CTE. With QUALIFY: SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC) AS rn FROM orders QUALIFY rn = 1. Available in BigQuery, Snowflake and DuckDB.
How do you optimize a slow SQL query?
First reflex: EXPLAIN ANALYZE to see the execution plan and spot expensive Seq Scans. In columnar warehouses (BigQuery, Snowflake): check that WHERE columns match the partitioning/clustering columns — avoid functions on partitioned columns (EXTRACT, DATE_TRUNC) which prevent pruning. Replace SELECT * with only the columns you need. Use materialized views for frequent, expensive aggregations.
What is the difference between WHERE and HAVING?
WHERE filters rows BEFORE aggregation (GROUP BY) — it runs early in the logical order, so it's more efficient. HAVING filters groups AFTER aggregation. WHERE revenue > 100 keeps order rows above 100. HAVING SUM(revenue) > 1000 keeps only groups whose total exceeds 1000. You cannot use an aggregate function in WHERE, but you can in HAVING.
What is dbt and why use it with SQL?
dbt (data build tool) is a framework that turns analytical SQL into versionable, testable code. Each model is a .sql file within a dependency DAG. dbt adds: automatic tests (unique, not_null, referential integrity, custom), generated documentation and lineage, configurable materializations (view, table, incremental, materialized view), and Snapshots for SCD Type 2. It is the de facto standard tool for SQL transformation in modern data stacks.
What is the logical execution order of SQL clauses?
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This order explains why you can't use a SELECT alias in WHERE (WHERE is evaluated before SELECT), why HAVING can use aggregations but WHERE cannot, and why window functions (evaluated in SELECT) cannot be filtered with HAVING — you need a CTE or QUALIFY instead.
How do you write a deduplication pattern in SQL?
Standard pattern with a CTE: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1. With QUALIFY (BigQuery, Snowflake, DuckDB) in a single query: SELECT * FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) = 1. In dbt, the incremental materialization with a unique_key handles deduplication automatically.