What SQL do data engineers need to know for interviews?

In this article

1. Before you start
2. Why interviewers ask this
3. SQL for data engineers from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

Pipeline work leans on a narrow part of SQL, and the SQL interview questions guide covers the rest.

Why interviewers ask this

A wrong ordering of clauses or an unhandled NULL does not raise an error in a pipeline; it silently changes a table that reports and models trust. For that reason, questions in data engineering rounds tend to probe behavior: what a query returns on tied values, on missing values and on duplicate keys.

A weak answer lists syntax: "partition by groups rows and merge upserts." A strong answer shows what each construct does to the rows, names the case where the textbook version breaks, and says how to defend against it, for example by adding a tie-breaker to an ordering or deduplicating a source before a merge. A Staff-level answer adds the pipeline view: which step is safe to rerun, and which step keeps history and which overwrites it.

SQL for data engineers from first principles

Window frame

A window frame is the range of rows inside a partition that a window function reads for the current row; when order by appears inside over, the default frame runs from the start of the partition through the current row and its tied peers, and without order by it is the whole partition.

Upsert

An upsert is a write that updates a target row when its key already exists and inserts a new row when it does not; in SQL it is usually written with merge, and it only behaves predictably when each key appears once in the source.

In plain words

Picture a class register. Grouping by class gives one summary line per class, such as the average mark, and the student lines disappear. A window function keeps every student line and adds a column that shows the class average beside each name. The analogy breaks in one place: a real window can also be sorted and cut to a range of rows, and that range is the frame.

In one precise sentence, grouping reduces rows to one per group, while a window function adds a computed column to rows that stay. Put another way, grouping answers "what is the total for each class?" and a window function answers "what is each student's mark next to the class total?" Both read the same rows, but only the second leaves every row on the page.

Step 1: Filter rows first, then group, then filter groups

A query is written in one order and evaluated in another. The select documentation lists the clauses as select, from, where, group by, having, window, then order by and limit, in that written order. In practice the rows come first, then groups, then output.

where filters individual rows before group by, while having filters the group rows that group by created, as the PostgreSQL 9.5 query reference puts it. Aggregates are computed before any expression in the having clause or the select list is evaluated. A query filters rows with where before grouping and filters groups with having afterward, so a condition on a total belongs in having and a condition on a raw column belongs in where. For ordering output rows, see how to sort rows ascending and descending. You now know the first rule: every later step reads the rows that this one leaves.

Step 2: A window function keeps every row, and its frame decides running totals

A window function keeps every row, unlike a grouped aggregate. The PostgreSQL tutorial says window functions do not cause rows to become grouped into a single output row. partition by inside over divides the rows into groups that share the same values, according to the current tutorial, and the function sees only its own group.

An ordinary aggregate such as sum becomes a window function only when an over clause follows it. Add order by inside over and the default frame runs from the start of the partition through the current row, plus any following rows that tie with it on the ordering. Without order by the frame is all rows in the partition.

That tie rule is the source of the common error. A running total over a column with duplicate values gives both tied rows the same total, because each row's frame includes its peers. Add a unique tie-breaker column to the ordering, or ask for a frame counted in rows, when each row should climb on its own. The next section's numbers show the jump.

1 · Filter and group2 · Window3 · Rank4 · Dedupe, then mergeReplay

Step-by-step description

1. Rows are filtered before grouping; groups after.
2. A window function keeps every row.
3. Ties get the same rank; rank leaves gaps.
4. One row per key goes into the merge.

• WHERE filters rows before grouping and HAVING filters groups afterward.
• A window function keeps every row, unlike a grouped aggregate.
• Rank leaves gaps after ties and dense rank does not.
• Dedupe the source to one row per key before the merge.

Step 3: Ranking functions treat ties differently, so filter after ranking

row_number numbers the rows of a partition from 1, with no repeats. rank gives tied rows the same number and leaves gaps; dense_rank gives tied rows the same number with no gaps. These definitions come from the function reference. Tied rows get row_number values in an unspecified order, so add a tie-breaker column when the choice matters.

Window functions are allowed only in the select list and the order by clause, because they logically run after the other clauses. A condition on a window result therefore cannot sit in where. The documentation suggests a sub-select: compute the rank in an inner query and filter it in the outer one. A common table expression (CTE) is a named subquery introduced with with that the main query reads as if it were a table, and it is the usual way to write this top-N-per-group pattern.

The query below keeps the two best-paid salary levels in each department. The inner query, named ranked, adds a dense_rank column called pos for each department. The outer query then filters on pos, which is allowed because pos is now an ordinary column of ranked.

with ranked as (
  select dept, name, salary,
         dense_rank() over (partition by dept order by salary desc) as pos
  from employees
)
select dept, name, salary
from ranked
where pos <= 2;

Step 4: Deduplicate the source, then merge on a key

An incremental load is a pipeline run that processes only new or changed source rows instead of rebuilding the whole table. A merge inserts source rows whose key is absent from the target and updates the rows whose key exists. Snowflake describes merging a change log into a target in exactly this way, and the dbt merge strategy works the same way on a unique_key.

The danger is a source with two rows for one key, and what happens depends on whether that key already exists in the target. When the duplicate key matches one target row, Snowflake documents the result as nondeterministic: the ERROR_ON_NONDETERMINISTIC_MERGE session parameter controls it, and the merge reference says the default makes the merge return an error, while FALSE picks one of the duplicates, not a defined one.

When the duplicate key is new, both source rows fall into when not matched and both are inserted, so the target silently holds the key twice. That second case raises no error and is a common pipeline bug. Dedupe the source to one row per key before the merge, for example by keeping row_number() over (partition by key order by updated_at desc) equal to 1. No single page states this recipe; it combines the window pattern of Step 3 with the merge rule above.

The statement below does both steps. The CTE named deduped keeps only the newest source row per id. The merge then updates target rows whose id matches and inserts the rest. It is written for PostgreSQL 15 and later; some engines want the deduplicating query inside the using clause instead.

with ranked as (
  select id, name, updated_at,
         row_number() over (partition by id order by updated_at desc) as rn
  from source_rows
),
deduped as (
  select id, name, updated_at from ranked where rn = 1
)
merge into target t
using deduped d on t.id = d.id
when matched then
  update set name = d.name, updated_at = d.updated_at
when not matched then
  insert (id, name, updated_at) values (d.id, d.name, d.updated_at);

Worked example

Take one illustrative department of four employees, Asha at 5,000, Ben at 4,200, Cai at 4,200 and Dev at 3,900.¹ Two orderings are used. rank, dense_rank and the default-frame running total use over (order by salary desc), so Ben and Cai are peers. row_number and the rows-frame running total use over (order by salary desc, name), which breaks the tie by name, together with rows between unbounded preceding and current row. A rows frame without a tie-breaker would split the 4,200 between Ben and Cai in an unspecified order. Three ranking functions and two running totals are shown in one table.

row_number never repeats a number. rank gives tied rows the same number and skips the next one, and dense_rank gives tied rows the same number and skips none.¹

The query below produces every column of the table from a table named employees with columns name and salary. The first window uses a tie-breaker, so row_number is stable. The next two use the salary alone, so tied rows are peers. The last two columns differ only in their window: the first has no frame clause and so gets the default frame, and the second asks for a frame counted in rows.

select name, salary,
       row_number() over (order by salary desc, name) as rn,
       rank()       over (order by salary desc) as rnk,
       dense_rank() over (order by salary desc) as drnk,
       sum(salary)  over (order by salary desc) as running_default,
       sum(salary)  over (order by salary desc, name
                          rows between unbounded preceding and current row) as running_rows
from employees;

Text description of the diagram

• Row number never repeats a number.
• Rank gives tied rows the same number and skips the next one.
• Dense rank gives tied rows the same number and skips none.

With the default frame, tied rows share the same running total of 13,400. A rows frame gives 9,200 and then 13,400. The default frame adds all tied rows at once, so a running total jumps instead of climbing row by row.¹ The same default frame explains a last_value surprise: over the default frame it returns 5,000, 4,200, 4,200 and 3,900 for the four rows, not 3,900 every time, because the frame ends at the current row and its peers.¹

Text description of the diagram

• With the default frame, tied rows share the same running total of 13,400.
• A rows frame gives 9,200 and then 13,400.

Counting and merging follow the same habit of checking the rows.

The first three counting rows follow the PostgreSQL aggregate documentation: count(*) counts input rows, and count of a column counts only non-null values.¹

Trade-offs and edge cases

Null handling is where most silent errors start. Most aggregate functions ignore null inputs, and except for count they return null, not zero, when no rows are selected, as the aggregate documentation explains; coalesce substitutes zero when needed. A conditional total built with sum and case therefore returns null for a group with no matching rows; see how a conditional total works and the guide to conditional expressions for the pattern, and conditions with several branches for longer ones.

Dialects differ. The SQL standard defines a respect nulls or ignore nulls option for lead, lag, first_value, last_value and nth_value, but PostgreSQL does not implement it, so a query that depends on it does not port. Say which engine you are assuming.

Merge strategy is a history decision. By default, with a unique_key, dbt overwrites matched rows with new values, which mirrors overwrite logic that does not track history. A table that must show old values needs a snapshot, not a merge. The setting incremental_predicates limits the scan of the existing table; the dbt documentation's example restricts it to the last 7 days, per the dbt docs, which is only safe if changed rows always fall inside that window.

Which steps are safe to rerun, and what lag adds

A step is safe to rerun when running it twice leaves the same table as running it once. A merge on a key, fed a source deduplicated to one row per key, has that property: the second run updates the same rows to the same values and inserts nothing new. An append-only insert does not: a rerun loads the same source rows again, so the target holds duplicates. Interviewers listen for which of your steps you can retry after a failure.

The other window functions worth knowing are lag and lead, which read a row before or after the current one in the partition. Day-over-day change is amount - lag(amount) over (partition by account order by day); the first row of each partition has no earlier row, so the result is null there. For more window patterns see window functions interview questions, visualised, and for the cost of these queries see query optimisation questions.

Last, joins can multiply rows before any window or merge sees them, so check row counts after each join in a pipeline.

How to answer it in an interview

The spoken structure: name the four areas, give one failure for each, then end on the merge rule.

Common mistakes

• "A window function is just a group by." (wrong) A grouped query collapses rows and a window function does not. (right) "A window function keeps every row and adds a computed column."
• "I filter on the rank in the where clause." (wrong) Window functions run after where, so it is not allowed. (right) "I compute the rank in a subquery or CTE and filter in the outer query."
• "count(*) and count(column) are the same." (wrong) They differ whenever the column has missing values. (right) "count(*) counts rows and count(column) counts non-null values."
• "The sum of nothing is 0." (wrong) It returns null. (right) "I use coalesce to turn the null into 0."
• "A merge removes duplicates." (wrong) With duplicate source keys it errors, updates from an undefined row, or, when the key is new, inserts it twice. (right) "I deduplicate the source first."

Follow-up questions

What is the difference between rank, dense_rank and row_number in SQL?

row_number gives every row a unique number, rank gives ties the same number and skips the next, and dense_rank gives ties the same number without gaps. For salaries 5,000, 4,200, 4,200 and 3,900 they give 1, 2, 2, 4 for rank and 1, 2, 2, 3 for dense_rank.

Can you use a window function in a where clause?

A window function cannot appear in a where clause because window functions are permitted only in the select list and the order by clause, as they run after the other clauses. Compute the window value in a subquery or CTE and filter in the outer query.

What is the difference between where and having?

where filters rows before grouping, and having filters groups after aggregation. Use having for a condition on an aggregate such as a total.

What is the difference between count(*) and count(column)?

count(*) returns the number of input rows, and count(column) returns the number of rows where that column is not null. count(distinct column) counts distinct non-null values.

How do you remove duplicates before a merge?

Duplicates are removed before a merge by ranking the source rows per key with row_number() over (partition by key order by updated_at desc) and keeping rank 1 in an outer query. The target then receives one row per key, so the result is defined.

Does the dbt merge strategy keep history?

The dbt merge strategy does not keep history: with a unique_key, dbt by default overwrites matched rows with new values. Use snapshots when you need to keep the old versions. For more SQL questions of this kind, see the SQL interview questions guide.

Sources

1. PostgreSQL documentation: 3.5 Window Functions (tutorial) — PostgreSQL Global Development Group, 26 Oct 2026
2. PostgreSQL 13 documentation: 3.5 Window Functions — PostgreSQL Global Development Group, 26 Oct 2026
3. PostgreSQL documentation: 9.22 Window Functions (function reference) — PostgreSQL Global Development Group, 26 Oct 2026
4. PostgreSQL documentation: SELECT — PostgreSQL Global Development Group, 26 Oct 2026
5. PostgreSQL 9.5 documentation: SELECT (WHERE and HAVING) — PostgreSQL Global Development Group, 26 Oct 2026
6. PostgreSQL 15 documentation: Aggregate Functions — PostgreSQL Global Development Group, 26 Oct 2026
7. PostgreSQL 9.6 documentation: Aggregate Functions — PostgreSQL Global Development Group, 26 Oct 2026
8. dbt documentation: About incremental strategy — dbt Labs, 26 Oct 2026
9. Snowflake documentation: MERGE — Snowflake Inc., 26 Oct 2026