SQL Mastery for Data Engineers & Analytics: Window Frames (ROWS vs RANGE), QUALIFY, Recursive CTEs & SARGable Predicates
Go beyond basic SELECT GROUP BY queries: master deterministic analytical frames, Gaps and Islands, Snowflake/BigQuery/Databricks QUALIFY filters, and index-friendly SARGable filtering.
Key Takeaways
- Always distinguish ROWS BETWEEN (exact physical row offsets) from RANGE BETWEEN (logical peer-value bounds with ties).
- Use QUALIFY in modern warehouses (Snowflake, BigQuery, Databricks, DuckDB) to filter window functions without boilerplate wrapper CTEs.
- Keep filter columns unwrapped on the left side of comparisons (SARGable predicates) so partition pruning and B-tree indexes actually skip data.
1. Why Analytical SQL Decides Data, BI & ML Feature Interviews
Regardless of whether your team builds ETL pipelines in PySpark, warehouse transformations in dbt on Snowflake, or analytics tables in BigQuery, ANSI SQL remains the shared dialect across engineering, product analytics, and machine learning feature stores. Nearly every screening loop begins with a 20–30 minute live SQL exercise designed to expose subtle logic bugs around NULL semantics, fan-out joins, window frame ties, and edge-case calendar gaps.
Candidates rarely lose points on simple INNER JOIN or COUNT(*) queries. They stumble when asked to deduplicate slowly changing dimensions without dropping ties, compute 7-day rolling revenue on sparse event tables that have missing calendar days, build hierarchical org charts cleanly, or identify why a query that returns in 200ms in unit tests melts down on 2 billion production rows.
2. ROWS vs. RANGE Window Frames: Stopping Subtle Tie Bugs
The single highest-leverage SQL distinction for interviews is how window frame clauses behave when timestamps or sort keys tie. When you omit an explicit frame inside SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date), ANSI SQL specifies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as the default — NOT ROWS BETWEEN.
Why does that matter? If a customer makes two $50 purchases on the same txn_date ("2026-11-01"), RANGE treats both rows as equal peers and returns $100 as the running total on BOTH the first and second row. Even worse, RANGE BETWEEN INTERVAL '6 DAYS' PRECEDING AND CURRENT ROW correctly calculates true 7-calendar-day moving sums even when dates are missing, whereas ROWS BETWEEN 6 PRECEDING AND CURRENT ROW strictly counts the last 6 physical rows regardless of whether three weeks elapsed between purchases. Knowing exactly when to choose ROWS vs RANGE immediately signals senior analytical judgment.
-- 1. Tie-safe deduplication using QUALIFY (Databricks, Snowflake, BigQuery, DuckDB)
SELECT
order_id,
customer_id,
status,
net_amount,
updated_at
FROM raw_orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingestion_seq DESC
) = 1;
-- 2. Compare physical 3-row moving avg vs true 7-calendar-day running sum
SELECT
account_id,
txn_date,
amount,
ROUND(AVG(amount) OVER (
PARTITION BY account_id
ORDER BY txn_date, txn_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS last_3_txn_avg,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY txn_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS true_7day_rolling_spend
FROM ledger_entries;3. Gaps & Islands + Sessionization Without Procedural Loops
Tracking consecutive active streaks, uninterrupted uptime windows, or user clickstream sessions are classic "Gaps & Islands" tasks. Trying to solve streaks with self-joins explodes into quadratic complexity. Instead, combine two clean relational tricks.
For discrete sequence islands (such as consecutive active days per subscriber): assign ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) and subtract that sequence integer from active_date (DATEADD(day, -rn, active_date) or active_date - rn). Every contiguous run of calendar days collapses onto the exact same invariant anchor date, allowing a single fast GROUP BY (user_id, island_anchor) to compute streak start, streak end, and streak duration. For event sessionization (grouping clicks separated by 30+ minutes of inactivity), use LAG(event_ts) to flag boundary starts (CASE WHEN diff > 30 minutes THEN 1 ELSE 0 END), then compute a cumulative SUM() of boundary flags to yield each user's unique session_id in a single linear scan.
4. Recursive CTEs & SARGable Query Optimization
Recursive Common Table Expressions (WITH RECURSIVE) unlock two everyday production needs that regular joins cannot express cleanly: flattening arbitrary-depth org hierarchies or category trees, and generating continuous calendar spine tables so left-joined daily dashboards never silently drop zero-activity days.
Finally, always keep predicates SARGable (Search ARGument-able). Writing WHERE YEAR(created_at) = 2026 or WHERE COALESCE(status, 'OPEN') = 'OPEN' wraps column values in functions, forcing query engines to disable B-tree index lookups and Parquet/Snowflake min-max micro-partition pruning. Rewrite them as range comparisons (WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01') and explicit boolean clauses (WHERE status = 'OPEN' OR status IS NULL). Notice a single rewrite often cuts bytes scanned by 95% with zero schema changes.
- Never join two 1-to-many tables together without pre-aggregating at least one side to target grain first (avoids silent revenue double-counting).
- Prefer NOT EXISTS (or LEFT ANTI JOIN) over NOT IN whenever the subquery column could ever contain NULL (since x NOT IN (1, NULL) returns empty unknown sets).
- Use UNION ALL instead of UNION by default unless deduplication across combined branches is deliberately required.
- Validate SARGability on every partition column and clustering key (avoid wrapping partitioned columns in CAST, SUBSTRING, or LOWER in WHERE clauses).
5. Handling Fan-Out Traps, NULL Keys & Skewed SQL Joins Safely
Two quiet bugs destroy executive metrics faster than syntax errors: fan-out multiplication during joins and skewed NULL keys during high-volume shuffles. Fan-out happens whenever an engineer joins fact_orders directly to order_payments (where split credit-card + gift-card transactions create 2 payment rows per order) AND joins to shipments simultaneously — duplicating order line revenue cleanly without raising any runtime error. Protect metric integrity by aggregating each child table down to the parent grain inside its own isolated CTE before joining, or counting COUNT(DISTINCT order_id) explicitly during reconciliation checks.
Similarly, millions of guest-checkout rows often share customer_id IS NULL or dummy sentinel values such as "UNKNOWN" or "-1". Equi-joining two billion-row tables directly on raw nullable keys routes every unmatched or sentinel row into a single hash bucket on one unlucky worker node, stalling the entire warehouse query. Either isolate NULL/sentinel records into separate deterministic fallback branches via UNION ALL or filter them cleanly prior to the inner shuffle.
Practice What You Just Read Inside DataArena Labs
Frequently Asked Questions
Why does NOT IN return zero rows when a subquery contains even one NULL?
In SQL three-valued logic (TRUE, FALSE, UNKNOWN), x NOT IN (1, NULL) expands to (x != 1) AND (x != NULL). Because any comparison to NULL yields UNKNOWN, AND-ing TRUE with UNKNOWN produces UNKNOWN, causing WHERE to filter out every single row. Use NOT EXISTS or filter WHERE col IS NOT NULL inside the subquery.
What is the logical execution order of a SQL SELECT statement?
SQL executes logically as: FROM / JOIN -> WHERE -> GROUP BY -> HAVING -> Window Functions (OVER) -> QUALIFY -> SELECT / Expressions -> DISTINCT -> ORDER BY -> LIMIT. This explains why WHERE cannot reference window aliases while QUALIFY can.