SQL Interview Questions and Answers for Data Engineers

All three functions number the rows inside a window. They differ only in how they treat ties.

• ROW_NUMBER() gives a unique sequence (1,2,3,4). It breaks ties in any order.
• RANK() gives tied rows the same number, then skips numbers (1,2,2,4).
• DENSE_RANK() gives tied rows the same number with no gaps (1,2,2,3).

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

Use ROW_NUMBER to remove duplicates. Use DENSE_RANK for "Nth highest" questions. Use RANK when the gaps after ties matter.

What they are checking: Do you know how each function treats ties, and can you pick the right one?

The safest way is DENSE_RANK. It handles ties and works for any Nth value.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

Other ways to do it:

-- Offset approach
SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1;

-- Subquery approach
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Note one difference. The window version returns no row (not NULL) when there is no second value. The MAX version returns NULL. Say which result the caller wants.

What they are checking: Can you handle ties, and do you know more than one way?

GROUP BY collapses rows into one row per group. A window function calculates a value across related rows but keeps every input row.

-- One row per department
SELECT dept, AVG(salary) FROM employees GROUP BY dept;

-- Every employee row, plus the department average beside it
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

• PARTITION BY sets the group. ORDER BY sets the order inside the group. A frame (ROWS BETWEEN ...) sets which rows are used.
• Window functions run after WHERE, GROUP BY and HAVING. So you cannot filter on them in WHERE. Put the query in a CTE or subquery instead.

What they are checking: Do you know that a window keeps rows and GROUP BY collapses them, and do you know the run order?

Number the duplicates with ROW_NUMBER over the columns that make a row a duplicate. Then delete every row after the first.

DELETE FROM customers
WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

ctid is the physical row id in PostgreSQL. It works even when the table has no primary key. In other databases, use the primary key or another unique id.

For a very large table, copying is often faster. Build a clean copy with SELECT DISTINCT ON (email) ... or the ROW_NUMBER filter, then swap it in. After that, add a unique constraint so the problem cannot come back.

What they are checking: Can you remove duplicates safely and stop them coming back?

Group by the columns that define a duplicate. Keep only the groups with more than one row.

SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

To see the full duplicate rows, join back or use a window count:

SELECT *
FROM (
  SELECT c.*, COUNT(*) OVER (PARTITION BY email) AS n
  FROM customers c
) t
WHERE n > 1;

Be clear about what a duplicate means. It could be the same email, or the same email and name. Also think about NULLs. For a unique constraint, NULLs are not equal to each other. But GROUP BY puts all NULLs in one group.

What they are checking: Can you find duplicates and say exactly what a duplicate is?

LAG(col, n, default) returns the value from n rows before the current row, in the window order. LEAD returns the value from n rows after. If there is no default, a missing row gives NULL.

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS pct_change
FROM monthly_revenue;

Notes:

• Use PARTITION BY to compare inside a group, for example per product.
• NULLIF(..., 0) avoids divide-by-zero errors.
• LAG replaces a self join for "previous row" logic. It is usually faster and easier to read.

What they are checking: Can you compare neighbouring rows without a self join?

Rank the rows inside each group with a window function. Then filter in an outer query.

SELECT *
FROM (
  SELECT category, product, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

Pick the ranking function with care. ROW_NUMBER returns exactly 3 rows per group. RANK or DENSE_RANK may return more when there are ties. Say which result the business wants.

PostgreSQL also supports CROSS JOIN LATERAL with ORDER BY ... LIMIT 3. This can be faster when there is an index on (category, sales) and there are few categories.

What they are checking: Can you solve the classic top-N-per-group problem and talk about ties?

• INNER JOIN: only rows that match in both tables. For example, orders with their customers.
• LEFT JOIN: every row from the left table, with NULLs where the right table has no match. For example, all customers and their orders, including customers with no orders.
• RIGHT JOIN: the mirror of LEFT. Most people swap the tables and use a LEFT JOIN instead.
• FULL OUTER JOIN: all rows from both sides, with NULLs where there is no match. Use it to compare two systems and find records missing from either.

SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

A common bug: filtering the right table in WHERE (WHERE o.status = 'paid') turns a LEFT JOIN into an inner join. Put that condition in the ON clause instead.

What they are checking: Do you know how each join works, and the WHERE versus ON trap?

Use SUM as a window function with ORDER BY. This gives a running total.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

• Add PARTITION BY customer_id to restart the total for each customer.
• State the frame clearly. The default frame with ORDER BY is RANGE ... CURRENT ROW. It treats rows with the same order value as peers and gives them the same running total. ROWS counts physical rows.
• Add a tiebreaker column to the ORDER BY so the result is the same every time.

What they are checking: Do you know window frames and the RANGE versus ROWS trap?

A common table expression (CTE) is a named temporary result. You define it with WITH. It exists only for that one statement.

WHERE filters single rows before grouping. HAVING filters groups after the aggregation.

Use an anti-join. NOT EXISTS is the safest form and is usually the fastest.

Both stack the results of two queries. The queries need the same number of columns and compatible types.

• UNION removes duplicate rows. This needs a sort or hash step.
• UNION ALL keeps every row and is faster.

NULL means unknown. So any comparison with it gives unknown, not true or false.

• DELETE: removes rows. It supports WHERE, is logged row by row, fires row triggers, and can be rolled back. Vacuum frees the space later.
• TRUNCATE: removes all rows fast by dropping the data files. It has no WHERE clause. It resets identity counters with RESTART IDENTITY. In PostgreSQL it can be rolled back. It takes an exclusive lock.
• DROP: removes the whole table, including its structure, indexes and constraints.

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. Comparing anything with NULL gives UNKNOWN. So the whole condition is never true, and the query returns no rows. This is three-valued logic.

Use a window aggregate without ORDER BY for the total. Use NTILE for buckets.

Join the table to itself with two aliases. Use a LEFT JOIN so employees without a manager still show up.

CASE WHEN adds conditional logic inside a query. COALESCE returns the first argument that is not NULL.

• COUNT(*) counts all rows, including rows with NULLs.
• COUNT(col) counts rows where col is not NULL.
• COUNT(DISTINCT col) counts distinct values that are not NULL.

A recursive CTE has two parts joined by UNION ALL. The anchor query gives the starting rows. The recursive part refers to the CTE itself. It repeats until the recursive part returns no new rows.

A Type 2 dimension stores one row per version, with a validity range. Filter on the range with a half-open interval so exactly one row matches.

EXPLAIN shows the planned tree. EXPLAIN (ANALYZE, BUFFERS) runs the query and adds real timings, row counts and block reads. Read from the innermost, most indented node outward.

Common reasons:

Use INSERT ... ON CONFLICT. It is atomic (all or nothing) and safe when many writers run at once. It needs a unique index or constraint on the conflict column.

Use conditional aggregation. It is portable and fast. PostgreSQL has no PIVOT keyword.

A B-tree composite index is sorted by the first column. Inside that, it is sorted by the second column, and so on. The index helps only when the query uses a leftmost prefix (the first columns of the index).

Partitioning splits one logical table into physical child tables by a key. Queries and maintenance then touch only the pieces they need.

Both define which rows around the current row go into the window function.

If there is exactly one row per day, use a ROWS frame:

Use the ordered-set aggregates percentile_cont and percentile_disc. The first interpolates, so it can give a value between two rows. The second returns a real value from the data.

Both test whether a value is in a subquery result.

• IN compares a value to the list of values from the subquery.
• EXISTS returns true as soon as the correlated subquery finds one row. It does not care about the values.

• B-tree (default): equality, ranges and sorting. It is right for most columns.
• Hash: equality only. It is rarely better than B-tree.
• GIN: many values per row, such as jsonb, arrays and full-text search.
• GiST: geometric data, ranges, nearest-neighbour search and full-text search.
• BRIN: a tiny index for huge...

• Nested loop: for each outer row, look up the matching inner rows. It is best when the outer side is small and the inner side has an index. The cost grows with the outer rows times the lookups.
• Hash join: build a hash table on the smaller input, then probe it with the other input. It is good for large unsorted...

A view stores only a query. Each read runs the query again on current data. A materialized view stores the result on disk. You refresh it when you ask.

A correlated subquery refers to the outer query. So it logically runs once for each outer row.

PostgreSQL uses MVCC (it keeps old row versions). After an UPDATE or DELETE, the old versions stay behind. These dead tuples take space until VACUUM frees it.

Work from evidence, not guesses.

1. Capture the exact query and its parameters. Run EXPLAIN (ANALYZE, BUFFERS). Compare it with an old plan if you saved one.
2. Compare estimates with actual numbers. If they are far apart, run ANALYZE on the tables. Data growth or a bulk load may have made the statistics old.
3. Look for a changed plan. A seq scan may have replaced an index scan. A bad join order may have replaced a good one.
4. Check table and index bloat (wasted dead space). Check that autovacuum keeps up.
5. Check for locks and contention in pg_stat_activity. Look for other heavy jobs running at the same time.
6. Check data growth. The table may have grown to a size where the plan changes. A new uneven value may have appeared.
7. Check recent changes: a dropped index, a schema migration, a new parameter value, or a version upgrade.

Fix the root cause. It may be statistics, an index, a query rewrite, or partitioning. Then add monitoring with pg_stat_statements so you see the problem early next time.

What they are checking: Do you have a clear, step by step way to debug slow queries?

Islands are runs of consecutive values. Gaps are the breaks between them. The standard trick is to subtract a ROW_NUMBER from the value. Inside one island, the difference stays the same.

Do two steps in one transaction. First close the changed current rows. Then insert the new versions.

Mark the start of a new session when the gap from the previous event is more than 30 minutes. Then take a running sum of the marks. That sum is the session id.

PostgreSQL has a short option called DISTINCT ON. It keeps the first row of each group, based on the ORDER BY.

Avoid one giant transaction. It holds locks, makes the WAL (write-ahead log) very large, and can run for hours.

Options:

• Do the work in batches of 10,000 to 100,000 rows. Commit after each batch and sleep briefly between batches:

Give each user a cohort (their signup month). Work out the months since signup for each activity. Then count distinct active users for each cohort and month offset.

Generate the calendar with generate_series. Then LEFT JOIN the data to it.

Start with EXPLAIN ANALYZE. Then reduce the work.