SQL Practice

Window functions, deduplication, SCD Type 1 and 2, gaps and islands, top-N per group, incremental loads and query tuning, with worked SQL.

Same series as Parts 1–4. This one is hands-on: worked SQL problems with sample schemas, not just theory. Syntax is Spark SQL / Databricks SQL throughout (works on Delta tables) — flag it as such if the interviewer's environment is a different dialect (Postgres/Snowflake syntax is nearly identical for all of this except a few functions noted inline).

1. Window Functions

Theory recap before the practice: OVER (PARTITION BY ... ORDER BY ... [ROWS/RANGE ...]) computes a value per row without collapsing rows the way GROUP BY does — you keep every input row and add a computed column alongside it. PARTITION BY resets the window per group; ORDER BY inside the window determines row sequence for ranking/offset functions; the optional frame clause (ROWS BETWEEN ...) controls how many surrounding rows are included for aggregate window functions.

ROW_NUMBER()
RANK()
LAG()
LEAD()
SUM() OVER()

Sample table for these examples:

-- orders(order_id, customer_id, order_date, amount)

Q: Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() — give an example where they diverge.

A:

SELECT
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC)  AS rn,
  RANK()       OVER (ORDER BY amount DESC)  AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC)  AS drnk
FROM orders;

With amounts [100, 100, 90, 80]: ROW_NUMBER gives 1,2,3,4 (always unique, breaks ties arbitrarily by physical/implicit order — dangerous if you need determinism and haven't fully specified the ORDER BY). RANK gives 1,1,3,4 (ties share a rank, next rank skips). DENSE_RANK gives 1,1,2,3 (ties share a rank, no gap). The practical trap: using ROW_NUMBER for "top N per group" when there are ties and the business actually wants all tied rows included — that's a RANK (or DENSE_RANK) call, not ROW_NUMBER, and I always ask which behavior is wanted rather than assuming.

Q: LAG/LEAD — write a query to find each customer's order amount compared to their previous order.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS delta
FROM orders;

LAG(amount, 1) (default offset 1) pulls the value from the previous row within the partition; LEAD does the same for the following row. Both accept an optional default (LAG(amount, 1, 0)) for when there's no previous row — otherwise it's NULL, which I handle explicitly in the delta calc if a business rule needs "first order counts as +100% growth" or similar rather than a null gap.

Q: SUM() OVER() — write a running total of order amount per customer, and separately, a total across the whole table alongside each row.

-- running total per customer
SELECT
  customer_id, order_date, amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

-- grand total repeated on every row (no ORDER BY -> whole partition, not running)
SELECT
  customer_id, order_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_total
FROM orders;

Q: What's the difference between ROWS BETWEEN and RANGE BETWEEN in a window frame, and why does it matter?

A: ROWS counts physical rows (the Nth preceding/following row, by position). RANGE counts by value of the ORDER BY column — so RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW includes all rows within 7 days of the current row's date, however many physical rows that is, not exactly N rows. This matters for something like a trailing-7-day sum with irregular/missing dates — ROWS BETWEEN 6 PRECEDING would silently give you "the last 7 rows" which isn't the same as "the last 7 days" if there are gap days with no data. I use RANGE (or an explicit date-range self-join/aggregation) whenever the business definition is time-based rather than row-count-based.

2. Deduplication

ROW_NUMBER() OVER (
    PARTITION BY business_key
    ORDER BY updated_at DESC
)

Q: Write a query to keep only the latest record per business key from a table with duplicate change events.

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY business_key
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_events
)
SELECT * FROM ranked WHERE rn = 1;

Q: What if updated_at has ties (same timestamp for multiple events on the same key)? How do you make this deterministic?

A: Add a tiebreaker to the ORDER BY — a monotonically increasing ingestion sequence number, offset/LSN from the source CDC log, or _metadata.file_modification_time / an ingestion _load_id if that's all that's available: ORDER BY updated_at DESC, ingestion_seq DESC. Never leave a dedup ROW_NUMBER window with an ORDER BY that can tie on the data you have — "arbitrary which one survives" is a correctness bug that will bite you the first time it matters (e.g. keeping a stale record over a newer one due to a coincidental exact-timestamp tie), and it's also non-deterministic across re-runs, which breaks idempotency (Part 2's core theme).

Q: How would you deduplicate on multiple business key columns (composite key)?

ROW_NUMBER() OVER (
  PARTITION BY customer_id, order_id
  ORDER BY updated_at DESC, ingestion_seq DESC
) AS rn

PARTITION BY just takes a comma-separated list — no different mechanically, just make sure the composite key actually is the full business key (a partial key here silently under-deduplicates).

Q: ROW_NUMBER vs QUALIFY — which do you prefer and why?

A: Spark SQL (and Snowflake/Databricks SQL) supports QUALIFY, which lets you filter on a window function without the CTE wrapper:

SELECT *
FROM raw_events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY business_key ORDER BY updated_at DESC
) = 1;

Same result, less boilerplate — I use QUALIFY when the dialect supports it (came up in the MERGE dedup examples in Part 2 too) purely for readability; there's no performance difference since it compiles to the same plan as the CTE version.

3. Slowly Changing Dimensions

Q: Explain SCD Type 1 and write the implementation.

A: Type 1 = overwrite, no history kept — the dimension row is simply updated to the latest values. Simplest, but you lose the ability to answer "what was this customer's address as of last year."

MERGE INTO dim_customer AS target
USING staged_customer_changes AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *

Straightforward MERGE, same pattern as the CDC upsert in Part 2 — Type 1 is really just "current-state MERGE" with no extra machinery.

Q: Explain SCD Type 2 and write the implementation.

A: Type 2 = preserve full history — every change to a tracked attribute creates a new row, with the old row's validity window closed off. Typical columns: effective_date, end_date (or is_current boolean, often both), plus a surrogate key distinct from the business key (since the business key now maps to multiple rows over time).

-- Step 1: close out changed rows (expire the old current version)
MERGE INTO dim_customer AS target
USING staged_customer_changes AS source
ON target.customer_id = source.customer_id
   AND target.is_current = true
WHEN MATCHED AND (
     target.address <> source.address
  OR target.email   <> source.email
     -- compare every SCD2-tracked attribute
) THEN
  UPDATE SET
    target.end_date   = source.change_date,
    target.is_current  = false

-- Step 2: insert new current rows for changed + brand-new customers
INSERT INTO dim_customer
  (customer_sk, customer_id, address, email, effective_date, end_date, is_current)
SELECT
  <generate new surrogate key>,
  source.customer_id, source.address, source.email,
  source.change_date, NULL, true
FROM staged_customer_changes AS source
LEFT JOIN dim_customer AS target
  ON  target.customer_id = source.customer_id
  AND target.is_current = true
WHERE target.customer_id IS NULL     -- brand new customer
   OR target.address <> source.address
   OR target.email   <> source.email;  -- changed customer (row just expired above)

I do this as two statements (expire, then insert) because a single MERGE statement can't both update an existing row and insert a brand-new versioned row keyed differently within one WHEN MATCHED/WHEN NOT MATCHED — some warehouses/newer Spark versions support more elaborate multi-clause MERGE that can approximate this, but the two-step pattern is the classic, portable, easiest-to-reason-about implementation, and I'd say so explicitly rather than trying to force it into one clever statement.

Q: How do you query "what was true as of a specific date" against an SCD2 table?

SELECT *
FROM dim_customer
WHERE customer_id = 123
  AND effective_date <= DATE'2026-03-15'
  AND (end_date > DATE'2026-03-15' OR end_date IS NULL);

Q: SCD1 vs SCD2 — how do you decide which attributes get which treatment on the same dimension?

A: It's usually mixed within one table — a type_1_attrs/type_2_attrs split. Attributes where history genuinely matters for reporting (address, at a company that reports "which region did this sale belong to at the time" — SCD2), vs attributes where only the current value is ever meaningful and re-stating history would be actively wrong (a corrected typo in a customer's name — SCD1, you don't want ten historical rows for a spelling fix). I ask the business/analytics stakeholders which attributes need "as-of" reporting rather than defaulting every column to Type 2, since Type 2 has real costs: row explosion, more complex joins (must join on the as-of date, not just the key), and slower dimension scans as history accumulates.

Q: How would you handle a late-arriving change in an SCD2 pipeline — a correction that should have applied to a past effective_date, not now?

A: This breaks the simple "expire current, insert new current" pattern because the change isn't at the boundary — it needs to be inserted into the historical timeline, splitting an existing row's validity window. I'd: find the row(s) whose [effective_date, end_date) window contains the late change's actual effective date, split that row into two (before/after the correction point) preserving its original attribute values, then insert the corrected row for the affected window. This is rare enough in most pipelines that I handle it as an explicit backfill/correction script rather than baking the complexity into the routine incremental MERGE — over-engineering the standard path for a rare case isn't worth it.

4. Gaps and Islands

Sample table: login_events(user_id, login_date) — one row per day a user logged in, want to find consecutive-day streaks (islands) and the missing gaps.

Q: Find consecutive-day login streaks per user (the "islands" part).

A: The classic trick: login_date - ROW_NUMBER() OVER (...) is constant within a consecutive run of dates, because both the date and the row number increment by 1 together for consecutive days — subtracting them cancels the increment out and leaves a stable "group id" per island.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM login_events
),
grouped AS (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, rn) AS island_id   -- constant within a consecutive run
  FROM numbered
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_length
FROM grouped
GROUP BY user_id, island_id
ORDER BY user_id, streak_start;

Q: Find the gaps instead — periods where a user did NOT log in, between their first and last login.

A: Compare each row's date to the previous row's date via LAG; a gap exists wherever the difference is more than 1 day.

WITH ordered AS (
  SELECT
    user_id,
    login_date,
    LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
  FROM login_events
)
SELECT
  user_id,
  prev_login AS gap_start_exclusive,
  login_date AS gap_end_exclusive,
  DATEDIFF(login_date, prev_login) - 1 AS days_missed
FROM ordered
WHERE DATEDIFF(login_date, prev_login) > 1;

Q: Why does the date - row_number() trick work — explain it like you'd explain it on a whiteboard.

A: Line up two sequences that both increase by exactly 1 for consecutive rows: the calendar date, and the row number. If dates are consecutive (no gap), date and row_number increase in lockstep, so date - row_number is the same value for every row in that run — it's like subtracting two parallel staircases, the "step difference" stays flat as long as both staircases climb at the same rate. The moment there's a gap in dates, the date jumps ahead of the row number (row number only increments by 1 regardless of how big the date gap was), so the subtraction result jumps too — that jump is exactly what separates one island from the next when you GROUP BY the subtraction result.

5. Top-N Per Group

Q: Find the top 3 highest-value orders per customer.

SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
WHERE rn <= 3;

Or with QUALIFY:

SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

Q: Why is this generally better than a correlated subquery with LIMIT per group?

A: ROW_NUMBER + filter is a single pass with one shuffle (partition by customer_id, sort within partition) — Catalyst can plan this efficiently as one window operation. A correlated subquery (SELECT * FROM orders o1 WHERE (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o1.customer_id AND o2.amount > o1.amount) < 3) is effectively a self-join per row and scales far worse — Spark doesn't have a native "LIMIT per group" execution primitive the way some row-store databases do, so the window-function approach is both idiomatic and the performant choice here, not just a style preference.

6. Running Totals

Already shown in the window functions section (SUM() OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), but two follow-ups that come up:

Q: Running total that resets per calendar month, per customer.

SELECT
  customer_id, order_date, amount,
  SUM(amount) OVER (
    PARTITION BY customer_id, DATE_TRUNC('month', order_date)
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS mtd_total
FROM orders;

Adding the truncated month into PARTITION BY resets the window at each month boundary — no special "reset" logic needed, it's just a finer-grained partition.

Q: Moving average over the trailing 7 rows.

SELECT
  customer_id, order_date, amount,
  AVG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS trailing_7_avg
FROM orders;

7. Incremental Loads

Q: Write an incremental load pattern using a high-watermark column.

-- assumes a control table tracking last successful watermark per pipeline
MERGE INTO silver.orders AS target
USING (
  SELECT * FROM bronze.orders
  WHERE updated_at > (SELECT last_watermark FROM control.watermarks WHERE pipeline_name = 'orders')
) AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source.updated_at > target.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- advance watermark only after the merge succeeds
UPDATE control.watermarks
SET last_watermark = (SELECT MAX(updated_at) FROM bronze.orders WHERE updated_at > last_watermark)
WHERE pipeline_name = 'orders';

Q: What's the risk of using updated_at as the watermark if the source system doesn't guarantee strictly increasing writes (e.g. clock skew, or a batch of records written slightly out of order)?

A: You can miss records that land with a timestamp behind the watermark you already advanced past — a record with updated_at = 10:00:03 arriving in storage after you've already advanced the watermark to 10:00:05 (because processing/network delay, not because the event itself was late) gets silently skipped by the > last_watermark filter forever. Mitigations: use a small overlap window (updated_at > last_watermark - INTERVAL 15 MINUTES, combined with the MERGE's natural idempotency so reprocessing already-applied rows is harmless — this is exactly why Part 2's idempotent-MERGE design matters here too), or better, use a source-provided monotonic sequence/offset (Kafka offset, CDC LSN) instead of a wall-clock timestamp wherever the source can provide one, since those are guaranteed strictly increasing by construction.

Q: How do you handle a full-refresh requirement alongside an incremental pipeline, without maintaining two separate codebases?

A: Parameterize: a full_refresh boolean flag that, when true, ignores the watermark filter entirely (WHERE 1=1 instead of WHERE updated_at > watermark) and runs the exact same MERGE logic against the full source — same code path as Part 1's backfill discussion. The only difference is what rows land in the source CTE; the MERGE/idempotency logic downstream doesn't need to know or care whether it's an incremental trickle or a full historical reprocess.

8. Query Optimization — "Why is this query slow?"

execution plan
indexes
statistics
joins
cardinality
filter pushdown
partition pruning
data volume

This overlaps with Part 4's Spark debugging sequence but is the SQL-specific framing — good to have both ready since an interviewer might ask either "why is this Spark job slow" or "why is this query slow" and expect a slightly different vocabulary in the answer even though the underlying causes are largely the same.

Execution plan: EXPLAIN (or EXPLAIN FORMATTED / EXPLAIN COST in Databricks SQL) first, always — confirms what's actually happening rather than what I assume is happening. Look for full table scans where a filter should have pruned partitions/files, join order and join strategy (is it broadcasting the small side, or did stats mislead it into a sort-merge on a size-asymmetric join), and whether predicates got pushed down to the scan or are being applied late after an expensive join/shuffle.

Indexes: less central in Spark/Delta than in a traditional RDBMS (no B-tree secondary indexes) — the equivalent concepts are Z-Ordering/liquid clustering (Part 2) for data skipping, and Delta's file-level min/max stats. If this is a question about a traditional OLTP/warehouse system instead (Postgres, SQL Server), I'd check for a missing index on a join/filter column causing a sequential scan, or an index that exists but isn't being used because of a function wrapped around the column in the WHERE clause (WHERE UPPER(col) = 'X' can't use a plain index on col) — a classic "the index exists but the query as written can't use it" trap.

Statistics: the optimizer's join-order and join-strategy decisions (broadcast vs sort-merge, join reordering) are only as good as the table statistics it has. Stale or missing stats (ANALYZE TABLE ... COMPUTE STATISTICS in Spark SQL) lead to bad cardinality estimates, which lead to bad plans — this is a common, boring, and very real root cause: "the query used to be fast, then the table 10x'd in size and nobody re-ran ANALYZE" is a story I'd expect an interviewer to recognize.

Joins: covered in depth in Part 4 — right strategy for the size asymmetry, watch for accidental cross joins (missing/wrong join condition silently producing a cartesian product), and join order for multi-way joins (filter/reduce cardinality as early as possible before joining, not after).

Cardinality: is the optimizer's row-count estimate at each plan step close to reality? A huge gap between estimated and actual rows (visible in EXPLAIN COST / the Spark UI's SQL tab, which on Databricks shows actual runtime row counts per operator) tells you the optimizer made a bad plan decision somewhere upstream because it under- or over-estimated how much data a step would produce — this is usually a stats problem or a very selective filter the optimizer couldn't estimate well (e.g. a filter on a derived/computed expression instead of a raw column).

Filter pushdown: are filters happening as early as possible — ideally at the scan itself (file-skipping via Delta stats/Z-order, partition pruning) — rather than after an expensive join or shuffle has already processed rows that were going to be filtered out anyway? Check the plan for PushedFilters in the scan node; if a filter you wrote isn't showing up there, something (a UDF wrapping the column, a non-deterministic expression, an OR condition spanning columns in different files) is blocking pushdown.

Partition pruning: for a partitioned table, does the plan show only the relevant partitions being scanned, or the whole table? A filter on a partition column that's wrapped in a function (WHERE YEAR(order_date) = 2026 instead of WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01') can silently defeat pruning because the optimizer can't always push a transformed predicate down to partition-level filtering — write the filter in the partition column's native form when possible.

Data volume: sometimes the honest answer is "the query is slow because it's touching a genuinely large amount of data and there's no clever trick around that" — at which point the conversation shifts to whether the question being asked actually needs that much data (can it be pre-aggregated into a smaller Gold table instead of scanning raw Silver every time?), rather than continuing to look for a magic optimization that isn't there.

Closing line: "I'd run EXPLAIN before touching anything else — most 'slow query' problems turn out to be a plan-level issue (bad join strategy from stale stats, a filter that didn't push down, accidentally losing partition pruning) rather than something that needs a schema redesign, and EXPLAIN is what tells you which kind of problem you actually have."

Quick-fire recap