Data Engineer Interview Questions — SQL

Three dialects, one idea.

-- PostgreSQL
INSERT INTO dim_charger (charger_id, operator, kw_rating, updated_at)
SELECT charger_id, operator, kw_rating, NOW() FROM stg_charger
ON CONFLICT (charger_id) DO UPDATE
  SET operator = EXCLUDED.operator,
      kw_rating = EXCLUDED.kw_rating,
      updated_at = EXCLUDED.updated_at;

-- MySQL
INSERT INTO dim_charger (charger_id, operator, kw_rating)
SELECT charger_id, operator, kw_rating FROM stg_charger
ON DUPLICATE KEY UPDATE operator = VALUES(operator), kw_rating = VALUES(kw_rating);

MERGE is the ANSI form, supported in SQL Server, Oracle and PostgreSQL 15 onwards, and it gives you a WHEN NOT MATCHED BY SOURCE branch that the ON CONFLICT syntax cannot express.

The failure people hit first is a duplicate key inside the source batch. PostgreSQL raises “ON CONFLICT DO UPDATE command cannot affect row a second time” and the whole statement rolls back. SQL Server’s MERGE throws a similar error about attempting to update the same row more than once. Neither will quietly pick a winner, which is correct behaviour and still surprising at 3 AM. Deduplicate the staging table first, keeping the latest row per key with ROW_NUMBER.

The second failure is silent and worse: overwriting good data with stale data. If your source is a queue that can deliver out of order, an older version of a row will happily overwrite a newer one. Guard the update with a condition:

ON CONFLICT (charger_id) DO UPDATE
  SET operator = EXCLUDED.operator, updated_at = EXCLUDED.updated_at
  WHERE dim_charger.updated_at < EXCLUDED.updated_at;

MySQL’s ON DUPLICATE KEY UPDATE has no WHERE, so the usual workaround is a conditional expression such as GREATEST or an IF() per column, which is uglier and does the same job.

Two operational notes. The match must be backed by a unique or primary key constraint, since these statements detect conflicts through the index, not through the column list you name. And MySQL’s version fires on any unique index, so a table with a second unique constraint can match on a key you never intended, updating the wrong row entirely.

Start with the query that breaks:

SELECT patient_id,
       DATEDIFF(discharge_date, admit_date) AS stay_days
FROM admissions
WHERE stay_days > 7;

Unknown column stay_days. The column is right there on line two, and the database still cannot see it. The reason is ordering.

The logical sequence is:

1. FROM / JOIN — assemble the working row set
2. WHERE — filter individual rows
3. GROUP BY — collapse rows into groups
4. HAVING — filter groups
5. SELECT — evaluate expressions and assign aliases
6. DISTINCT
7. ORDER BY — sort
8. LIMIT / OFFSET / TOP / FETCH

Aliases come into existence at step 5. WHERE ran back at step 2, three steps too early. ORDER BY sits at step 7, which is why ORDER BY stay_days works perfectly in the same query that rejects it in WHERE.

Two ways out: repeat the expression in WHERE, or push the calculation into a derived table or CTE.

SELECT patient_id, stay_days
FROM (
  SELECT patient_id,
         DATEDIFF(discharge_date, admit_date) AS stay_days
  FROM admissions
) a
WHERE stay_days > 7;

Vendors diverge on the middle of that list, and it catches people who switch databases. MySQL permits aliases in GROUP BY and HAVING. PostgreSQL permits them in GROUP BY and ORDER BY, but not in HAVING. SQL Server permits them only in ORDER BY. None of them permit an alias in WHERE. A query written and tested on MySQL can fail on its first run against SQL Server for exactly this reason.

The word “logical” is doing real work here. This is the order the language guarantees results are equivalent to, not a description of the execution plan. An optimiser is free to push a filter below a join, evaluate a scalar subquery once instead of per row, or skip a sort it can satisfy from an index. Interviewers ask this because the sequence quietly explains WHERE versus HAVING, alias scope, and why HAVING can reference aggregates.

NULL is not a value sitting in the cell. It is a marker saying the value is unknown. So settled_on = NULL is asking whether one unknown quantity equals another unknown quantity, and the honest answer is: unknown. WHERE keeps a row only when the condition is true, and unknown is not true.

SELECT claim_id FROM claims WHERE settled_on = NULL;   -- always empty
SELECT claim_id FROM claims WHERE settled_on IS NULL;  -- pending claims

The same logic makes NULL <> NULL empty too, so you cannot escape by flipping the operator.

Where this genuinely costs people money is NOT IN. Suppose you want policies that have never been claimed against:

SELECT policy_id
FROM policies
WHERE policy_id NOT IN (SELECT policy_id FROM claims);

If a single row in claims has a NULL policy_id, this returns nothing. The reason: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and that last term is unknown, so the whole expression can never be true. Nothing errors out. You just get an empty result and assume every policy has a claim. NOT EXISTS, or a LEFT JOIN with IS NULL, behaves correctly here.

One inconsistency to keep straight: NULL is not equal to itself for comparison, but GROUP BY and DISTINCT treat all NULLs as a single group, and ORDER BY sorts them together. Most engines also allow multiple NULLs in a UNIQUE column for the same reason.

When you do need NULL-safe equality, PostgreSQL and the standard offer IS DISTINCT FROM, and MySQL has the <=> operator.

The real payoff is conditional aggregation. Here are recharge volumes bucketed by ticket size, per telecom circle, in a single pass:

SELECT circle,
       COUNT(*) AS recharges,
       SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 END)               AS under_100,
       SUM(CASE WHEN amount >= 100 AND amount < 500 THEN 1 ELSE 0 END) AS mid,
       SUM(CASE WHEN amount >= 500 THEN 1 ELSE 0 END)              AS premium,
       SUM(CASE WHEN validity_days >= 84 THEN amount ELSE 0 END)   AS long_pack_revenue
FROM recharges
WHERE recharged_on >= '2026-07-01'
GROUP BY circle;

Three separate queries collapse into one, and the table is scanned once.

Now the trap, and it is a common one:

COUNT(CASE WHEN amount < 100 THEN 1 ELSE 0 END)   -- counts every row

COUNT ignores NULL, not zero. The ELSE branch hands it a 0, which is a perfectly good value, so every row gets counted and all three buckets come back identical to the row total. Either drop the ELSE so non-matching rows produce NULL, or keep the ELSE and use SUM. Pick one style and stay with it.

Second thing to watch: CASE stops at the first branch that evaluates to true. Write overlapping ranges in a careless order and rows land in the wrong bucket without any warning — a WHEN amount > 100 sitting above WHEN amount > 500 sends every premium recharge into the mid bucket.

Also worth knowing that simple CASE (CASE status WHEN 'ACTIVE' THEN ...) uses equality internally, so it can never match a NULL status. Searched CASE with an explicit WHEN status IS NULL branch is the way to handle those.

Pick the join by asking which rows you cannot afford to lose. Take flights (every scheduled departure for a day) and bookings.

• INNER JOIN keeps only flights that have at least one booking. Empty flights disappear from the result. Fine for revenue analysis, quietly wrong for a load-factor report, because the worst-performing flights are exactly the ones that vanish.
• LEFT JOIN keeps every flight, filling booking columns with NULL where there is no match. This is the one you want for any report where the left table defines the universe of things being measured.
• RIGHT JOIN is the same operation with the tables reversed. It is legal and it works, but most teams standardise on LEFT and reorder the tables instead, because a query mixing both directions is genuinely hard to read.
• FULL OUTER JOIN keeps unmatched rows from both sides at once. Its natural home is reconciliation: in one pass you see flights with no bookings and bookings pointing at a flight ID that no longer exists in the schedule. Data quality checks between two systems are where this earns its keep.

MySQL has no FULL OUTER JOIN. You emulate it:

SELECT f.flight_id, b.booking_id FROM flights f
LEFT JOIN bookings b ON b.flight_id = f.flight_id
UNION
SELECT f.flight_id, b.booking_id FROM flights f
RIGHT JOIN bookings b ON b.flight_id = f.flight_id;

UNION rather than UNION ALL, since the matched rows appear in both halves.

The related pattern interviewers usually probe next is the anti-join: a LEFT JOIN with WHERE b.booking_id IS NULL gives you flights nobody booked.

Here is the query, joining e-commerce orders to a sparse returns table:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE r.return_reason = 'DAMAGED';

The join does its job. Every order survives it, and orders that were never returned come out with return_reason set to NULL. Then WHERE runs, and NULL = 'DAMAGED' evaluates to unknown, so every one of those padded rows is discarded. What remains is precisely the inner join. You have written LEFT JOIN and received INNER JOIN semantics, with no warning of any kind.

The fix depends on what you meant.

If you wanted all orders, with the reason shown only when it was a damaged return, the condition restricts matching, so it belongs in ON:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r
  ON r.order_id = o.order_id
 AND r.return_reason = 'DAMAGED';

Every order stays. Orders with a non-damaged return simply fail the match and get NULL, same as orders with no return at all.

If you genuinely wanted only damaged returns, use an INNER JOIN and say so, so the next person reading the query is not misled.

There is one right-side condition that is safe in WHERE, and it is the exception that proves the rule: WHERE r.order_id IS NULL. That is true only for padded rows, so it deliberately keeps the non-matches and throws away the matches. It is the standard anti-join.

The reason this matters beyond the interview is how quietly it fails. A monthly report joining orders to a partially populated feedback table with WHERE f.rating >= 4 does not error. It returns a smaller, entirely plausible order count, and it can sit in a dashboard for two quarters before anyone reconciles it against the source system.

Working rule: conditions on the preserved table go in WHERE, conditions on the optional table go in ON. For an INNER JOIN the distinction makes no difference to the result, which is where the habit of putting everything in WHERE comes from.

The forms are cumulative, and each one exists to kill a specific way data goes wrong.

1NF. One value per cell, no repeating groups. A crops_grown column holding 'wheat,mustard' violates it, as does crop1, crop2, crop3. You cannot index it, constrain it, or query it without string surgery.

2NF. With a composite key of (farmer_id, season_id), every non-key column must depend on both parts. Storing farmer_village there depends on the farmer alone, so it repeats across every season that farmer appears in. Change the village once and you have to change it in eleven rows or the table contradicts itself.

3NF. No non-key column depending on another non-key column. If the table holds village_id and also district_name, the district is a fact about the village, not about the row. Move it to a village table.

The anomalies are the point, and naming them is what interviewers listen for. Update: correcting a district in one row and not the others leaves two answers to the same question. Insert: you cannot record a new village until some farmer is registered in it. Delete: removing the last farmer in a village erases the district mapping entirely.

Now the deliberate stop. Normalisation optimises for write integrity; analytics optimises for read cost. A fully normalised model turns a routine dashboard query into a nine-table join, and at scale that is the wrong trade.

Denormalise when the joins dominate your query cost and the denormalised attribute rarely changes. Dimension tables in a warehouse are intentionally not in 3NF, carrying village, district and state on one row, because the redundancy costs a little storage and saves two joins on every query.

The condition attached to that: a copied value can drift. Once district_name lives in two tables, something has to keep them in agreement, and “something” means a scheduled reconciliation or a rebuild from the source of truth, not good intentions. If you cannot say what that mechanism is, you are not denormalising, you are just introducing a bug with a longer fuse.

A gym chain has members, plus payments and class_bookings, both with many rows per member. This query looks reasonable:

SELECT m.member_id, SUM(p.amount) AS total_paid
FROM members m
JOIN payments p        ON p.member_id = m.member_id
JOIN class_bookings cb ON cb.member_id = m.member_id
GROUP BY m.member_id;

A member with 6 payments and 20 class bookings produces 6 × 20 = 120 rows, and SUM(p.amount) comes out 20 times too high. The nasty part is that the inflation factor differs per member, so total revenue is not off by a clean multiple you might notice. It is just wrong, in a way that looks like it could be right.

A join is not a lookup. It pairs every left row with every right row that satisfies the condition. One join to a many-side is often exactly what you want: joining orders to order_items should repeat the order row once per item. The damage starts when you hang two independent one-to-many branches off the same key, because they form a Cartesian product with each other inside every member.

Diagnose before you assume. Compare the row count to the base table, or check the join key directly:

SELECT member_id, COUNT(*) FROM payments GROUP BY member_id HAVING COUNT(*) > 1;

The reliable fix is to collapse each branch to one row per key before joining:

SELECT m.member_id, p.total_paid, cb.classes_booked
FROM members m
LEFT JOIN (SELECT member_id, SUM(amount) AS total_paid
           FROM payments GROUP BY member_id) p  ON p.member_id = m.member_id
LEFT JOIN (SELECT member_id, COUNT(*) AS classes_booked
           FROM class_bookings GROUP BY member_id) cb ON cb.member_id = m.member_id;

Resist the shortcut of SUM(DISTINCT p.amount). Two genuine payments of ₹2,499 in different months are indistinguishable after DISTINCT, so one of them silently disappears. It patches the number without fixing the cardinality, which is worse than leaving it visibly broken.

Worth doing in the interview itself: before writing anything, ask whether the relationship is one-to-one or one-to-many. Candidates who ask that get read as people who have debugged a wrong revenue figure at least once.

Same fact table, different treatment of the dimensions around it.

In a star, dim_property carries ward name, zone name, city and state as columns on the same row, repeated across every property. A tax collection query joins one dimension and filters:

SELECT d.zone_name, SUM(f.tax_collected)
FROM fact_property_tax f
JOIN dim_property d ON d.property_sk = f.property_sk
WHERE d.city = 'Bengaluru'
GROUP BY d.zone_name;

In a snowflake, that dimension splits into property, ward, zone and city tables, and the same query walks three more joins to reach the city filter.

Storage is rarely the deciding argument. Dimensions are small next to the fact table, and saving a few hundred megabytes on a dimension while the fact table holds hundreds of millions of rows is not a trade worth extra joins on every query. Where snowflaking genuinely pays: a dimension with very high cardinality and a wide hierarchy, or a hierarchy shared by several dimensions where you want one authoritative copy, or a source system that already maintains the levels separately and mirroring them avoids a reconciliation job.

Two practical points. Query engines built for this workload, including most columnar warehouses, optimise star joins specifically, and dimension tables that fit in memory get broadcast rather than shuffled. Adding levels defeats some of that. And BI tools model a star far more naturally, which matters because analysts, not engineers, write most of the queries that hit it.

The choice that matters more than either is the grain of the fact table. Decide what one row represents, state it explicitly, and keep every measure on that row additive at that grain. Get the grain wrong and no amount of dimension modelling rescues the design.

Facts are what happened. Dimensions are the context that describes it. For a cinema advertising business, fact_ad_play records each slot played, with numeric measures and foreign keys; dim_advertiser, dim_screen and dim_date supply the names, cities and categories you slice by.

The practical distinctions:

Grain is the first decision and the one everything else depends on. Write it as a sentence before you write any DDL: “one row per advertisement play, per screen, per show.” Not “the ad play table.” The sentence forces the level to be explicit, and once it exists, deciding whether a column belongs becomes mechanical. Does it vary at that level, or is it a fact about the advertiser? If the latter, it belongs in the dimension.

Where this goes wrong in production is mixed grain. Someone adds campaign_total_budget to a play-level fact table because a report needed it. The column is now repeated across every play in the campaign, and the first analyst who writes SUM(campaign_total_budget) gets a figure inflated by the number of plays. Nothing errors, and the number is large enough to look like a plausible annual budget.

That is the additivity question, and it is worth raising unprompted. Measures come in three kinds. Fully additive, like seconds played or amount billed, sum across every dimension. Semi-additive, like a stock level or an account balance, sum across some dimensions but not across time; you take a closing value or an average instead. Non-additive, like a fill rate or a percentage, cannot be summed at all and must be recomputed from its numerator and denominator.

A factless fact table is worth naming for the follow-up: a fact with no measures, recording that a relationship existed. Which ads were eligible for which screens, for instance, so you can find the ones that never played.

A ride-hailing app writes a fare record when a trip ends: one row, five milliseconds, thousands of times a minute. The analytics team asks for average fare by city by hour across eighteen months: one query, four hundred million rows, no writes at all. Optimising for one actively harms the other.

Row storage suits OLTP because fetching one trip means reading one contiguous record. Column storage suits OLAP because averaging fares reads only the fare and city columns and skips the other forty entirely, then compresses each column well since neighbouring values are similar.

The failure mode that makes this a real interview question rather than a textbook one: analysts pointed at the production database. A single unindexed aggregate scan holds read locks or a long-running snapshot, competes for the same buffer pool the application depends on, and in PostgreSQL prevents vacuum from cleaning up dead tuples for the duration. Checkout latency rises during the report, and the connection is not obvious to anyone watching the app.

A read replica removes most of the contention and is often the first step, but it is not a warehouse. It still carries the normalised schema, so the nine-table join remains, and it still holds only current state, so a customer’s old address is gone the moment they change it. History and modelling are what the warehouse adds.

Modern lines are blurrier than the table suggests, and it is worth saying so. HTAP systems and columnar extensions inside transactional engines exist, and for a small product a replica genuinely is enough. The scale at which separating them pays is a judgement, not a rule.

One query on a music streaming log makes all three visible:

SELECT COUNT(*)                 AS play_events,     -- 4,812,000
       COUNT(user_id)           AS identified_plays,-- 4,105,000
       COUNT(DISTINCT user_id)  AS listeners,       -- 318,400
       COUNT(DISTINCT track_id) AS tracks_played    -- 27,900
FROM plays
WHERE played_on >= '2026-08-01';

Four different numbers, four different questions. The gap between the first two is anonymous playback with no logged-in user. The gap between the second and third is simply that people listen more than once.

Where this turns into a bug is after a LEFT JOIN. Count the wrong side and you count the padding:

SELECT a.artist_id, COUNT(*) AS plays
FROM artists a
LEFT JOIN plays p ON p.artist_id = a.artist_id
GROUP BY a.artist_id;

An artist nobody streamed still contributes one NULL-padded row, so COUNT(*) reports 1 play instead of 0. COUNT(p.play_id) reports 0, because the padded row has NULL there. Any time a count follows an outer join, count a column from the optional table, never the star.

On performance, the folklore that COUNT(*) is slower than COUNT(id) is worth dropping. In PostgreSQL and MySQL/InnoDB, COUNT(*) is treated as a row count and the planner picks the cheapest index it can scan. COUNT(id) may be no faster and cannot use certain shortcuts if the column is nullable. What genuinely costs is COUNT(DISTINCT ...), which has to deduplicate through a hash or a sort, and gets expensive on high-cardinality columns. Approximate functions exist for that, such as APPROX_COUNT_DISTINCT in SQL Server and BigQuery, when an exact figure is not needed.

Multi-column distinct is a portability trap. MySQL accepts COUNT(DISTINCT user_id, track_id). PostgreSQL requires COUNT(DISTINCT (user_id, track_id)) with a row constructor, and SQL Server supports neither, so you concatenate or use a subquery.

There is no hierarchy in a GROUP BY list. The database builds a key out of every grouped column, joined together, and every distinct key becomes one output row. GROUP BY market, crop and GROUP BY crop, market produce the same set of rows with the same aggregates. Only the column order in a SELECT or an ORDER BY changes what you see.

For daily mandi arrivals:

SELECT market_name,
       crop,
       SUM(quantity_quintal)   AS arrivals,
       AVG(modal_price_per_qtl) AS avg_price
FROM mandi_arrivals
WHERE trade_date = '2026-08-19'
GROUP BY market_name, crop;

If Bengaluru’s Yeshwanthpur mandi traded onion, tomato and potato that day, that mandi contributes three rows. A mandi that traded only onion contributes one. There is no fixed rectangle, only the combinations that actually occur in the filtered data.

That last point is the one that trips reports. GROUP BY cannot invent a group for a pair with no rows behind it. A crop that arrived at no mandi on that date is absent from the output, not present with a zero. If the consumer of your report needs a fixed set of rows, you build the grid separately and join to it.

The counting rule is worth internalising because it explains a class of “my numbers changed” tickets. Adding a column to GROUP BY can only split existing groups, never merge them. Someone adds grade to the list for a bit more detail, the row count goes from 900 to 2,600, and the totals per market are now spread across three grade rows each. Nothing is wrong. The report just answers a finer question than the one the dashboard tile was built for.

Every non-aggregated column in the SELECT must appear in the GROUP BY. PostgreSQL and SQL Server enforce this. MySQL enforces it too under ONLY_FULL_GROUP_BY, which is on by default from 5.7 onwards, but a legacy server with that mode disabled will accept a stray column and hand back a value from an arbitrary row in the group. The one relaxation everyone allows: columns functionally dependent on the grouped primary key can be omitted in PostgreSQL and modern MySQL.

Both of these find pharmacy SKUs whose current stock is below the average stock level for their category. The nested version:

SELECT s.sku_id, s.category, s.on_hand
FROM stock s
WHERE s.on_hand < (SELECT AVG(on_hand) FROM stock x WHERE x.category = s.category);

The CTE version:

WITH category_avg AS (
  SELECT category, AVG(on_hand) AS avg_on_hand
  FROM stock
  GROUP BY category
)
SELECT s.sku_id, s.category, s.on_hand
FROM stock s
JOIN category_avg c ON c.category = s.category
WHERE s.on_hand < c.avg_on_hand;

The second one reads in the order you would explain it aloud. Compute the category averages, then compare each SKU against them. That matters more than it sounds, because the version you can debug in six months is the version that stays correct. You can also comment out the final SELECT and run the CTE alone, which you cannot do with a deeply nested inline subquery.

Reuse is where a CTE stops being a style preference. Reference category_avg twice, once for understocked SKUs and once for overstocked, and the subquery version means writing the same aggregation twice and hoping both copies get edited together.

On plans, be careful about the advice you repeat. PostgreSQL before version 12 always materialised CTEs, which made WITH an optimisation fence: predicates could not be pushed down into it, and a CTE inside a big query was sometimes a real performance problem. From 12 onwards a CTE used once and not recursive is inlined by default, and you can force either behaviour with MATERIALIZED or NOT MATERIALIZED. MySQL 8.0 also merges CTEs into the outer query where it can. So on current versions the two forms above typically produce the same plan. The correct answer in the room is that it depends on the engine and version, and that you would check EXPLAIN rather than assert it.

One capability a subquery simply does not have: recursion. Walking an unbounded hierarchy or generating a series needs WITH RECURSIVE, which is CTE-only.

Always run EXPLAIN ANALYZE rather than plain EXPLAIN. Plain EXPLAIN shows the optimiser’s guesses; ANALYZE actually executes and reports what happened, which is the only way to catch a bad estimate. On MySQL it is EXPLAIN ANALYZE from 8.0.18, and on SQL Server the equivalent is the actual execution plan rather than the estimated one.

Reading order is inside out and bottom up. The deepest indented node runs first and feeds its parent.

What to check, in this order.

Estimate versus actual. A node reading rows=400 ... actual rows=912340 is the single most useful signal in the plan. The optimiser chose its join strategy based on 400 rows and got two thousand times that, so a nested loop that would have been fine became a disaster. Causes are stale statistics, correlated predicates the planner treats as independent, or a function wrapped around a column that defeats estimation. ANALYZE on the table is the first thing to try.

Where the time went. Nodes report their own timing plus children’s. A node at 40 milliseconds under a parent at 12 seconds means the parent is the problem. Look for the largest jump.

Access method. A sequential scan on a large table with a selective filter suggests a missing or unusable index. A sequential scan on a small table, or one returning most rows, is correct and faster than an index scan would be. Do not treat it as a fault by reflex.

Join method. Nested loop for a small outer side against an indexed inner side. Hash join for two large unsorted inputs. Merge join for pre-sorted inputs. A nested loop with a large outer row count is the classic symptom of the estimate problem above.

Spills. Sort Method: external merge Disk: 148MB means work_mem was too small and the sort went to disk. Raising it for that session can be a bigger win than any index.

Cost units deserve a caution: they are relative, on an arbitrary scale, and comparing them across machines or configurations tells you nothing.

The reason plans change without the query changing is data volume and statistics. A predicate that was highly selective when the table held two lakh rows may not be at two crore, and the planner correctly switches strategy. Queries do not degrade gradually; they fall off a cliff at the point the plan flips.

For the positive check, pick whichever reads better:

-- accounts that received a transaction flagged for review
SELECT a.account_no FROM accounts a
WHERE a.account_no IN (SELECT account_no FROM flagged_txns);

SELECT a.account_no FROM accounts a
WHERE EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Same result, and on PostgreSQL and MySQL 8.0 usually the same plan, because both get rewritten into a semi-join. The advice that “EXISTS is faster than IN” comes from older optimisers, particularly MySQL 5.x, where a correlated IN subquery could be re-executed per outer row. Repeating it as a general truth about current engines is how candidates get caught out.

Negate the check and the two stop being interchangeable:

SELECT a.account_no FROM accounts a
WHERE a.account_no NOT IN (SELECT account_no FROM flagged_txns);

If flagged_txns.account_no is nullable and even one row is NULL, this returns zero accounts. NOT IN expands into a chain of inequality comparisons joined by AND, and a comparison against NULL is never true, so the chain can never succeed. The query does not fail. It reports that every account has a flagged transaction, which in a compliance report is a conclusion someone might act on.

NOT EXISTS has no such behaviour. It asks whether a matching row exists, gets a straight yes or no, and is the form to write by default:

SELECT a.account_no FROM accounts a
WHERE NOT EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Two smaller points worth having ready. IN with a literal list is a different thing from IN with a subquery, and very long literal lists, in the tens of thousands, degrade badly and are better handled by a temporary table or a join. And the SELECT 1 inside EXISTS is convention rather than optimisation. The engine never evaluates the select list, so SELECT * performs identically, but SELECT 1 signals to the reader that no column is being returned.

Start by narrowing the problem rather than by tuning. A query that was fast and is now slow has a cause, and finding it beats optimising blindly.

Establish the facts. Slow for everyone or one user? Always or at 9 AM? Did data volume grow, did a plan flip, or did someone change the query? Check whether it is even the query: lock waits and connection pool exhaustion both present as a slow query and neither is fixed by an index.

Read the plan. Get EXPLAIN ANALYZE output and find the node with the biggest estimate-versus-actual gap and the largest time contribution. That points at where to work, and skipping it means guessing.

Reduce rows before anything else. The cheapest work is work not done. Filter earlier, aggregate before joining rather than after, and remove columns nobody uses, since a wide select can prevent an index-only scan.

Make predicates usable. This is the most common real fix. A column wrapped in a function is invisible to a plain index:

WHERE DATE(dispatched_at) = '2026-08-19'          -- no index use
WHERE dispatched_at >= '2026-08-19'
  AND dispatched_at <  '2026-08-20'               -- range scan, index used

Leading wildcards in LIKE, implicit type casts between a varchar column and an integer literal, and OR across different columns all defeat indexes the same way.

Then consider indexes, and only for predicates that are actually selective. An index on a column with three distinct values across two crore rows will not be used, and you will have paid the write cost for nothing.

Then schema. Partitioning by the filtered date, a summary table for a repeatedly computed aggregate, or denormalising a hot join. These are the right answer when the query is already minimal and the data volume is simply too large for the shape of the question.

Two measurement disciplines that separate this from folklore. Change one thing at a time, or you will not know which change helped. And run each test twice, discarding the first, because the second run reads from a warm buffer cache and a comparison of cold against warm is meaningless.

Know when to stop. If the query now scans exactly the rows it needs and still takes four minutes, the shape of the data is the problem and further query tuning is wasted effort. Precompute it.

Selectivity decides it. An index on status where 94% of rows are 'ACTIVE' will be ignored for the common value, because fetching nine lakh scattered rows through an index costs more than reading the table in order. The same index is genuinely useful for status = 'DISPUTED' if that is 0.2% of the table, and PostgreSQL’s per-value statistics will use it for one value and not the other in the same query shape.

The write cost is the half people forget. Every insert into a table with six indexes writes seven structures, not one. Updates to an indexed column mean deleting and reinserting the index entry. On a bulk load, indexes frequently dominate the total time, which is why dropping and rebuilding them around a large load is standard practice.

-- find unused indexes on PostgreSQL
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Zero scans since the last statistics reset means the index is pure cost, unless it backs a constraint.

Three things worth having ready.

A partial index targets the rows you actually query: CREATE INDEX ON gst_invoices (filed_on) WHERE status = 'DISPUTED' is a fraction of the size and cheaper to maintain than a full index. PostgreSQL and SQL Server support these; MySQL does not.

A covering index includes every column the query needs, so the engine answers from the index alone and never touches the table. Real win on a hot query, real cost in width and write time.

Foreign key columns deserve an index in most engines even though the constraint does not create one automatically. Without it, deleting a parent row scans the child table.

The one that catches people: creating an index and seeing no improvement, because the statistics have not been updated, or because the predicate wraps the column in a function, or because the table is small enough that the planner is right to scan it.

GROUP BY answers “what is the total per category” and hands back one row per category. The moment you also want the individual rows, you are asking a different question and GROUP BY is the wrong tool.

For a supermarket’s daily till log:

SELECT bill_id,
       aisle,
       line_total,
       SUM(line_total) OVER (PARTITION BY aisle)                       AS aisle_total,
       ROUND(100.0 * line_total / SUM(line_total) OVER (PARTITION BY aisle), 2) AS pct_of_aisle
FROM bill_lines
WHERE bill_date = '2026-08-19';

Every line item survives, and each one now carries its aisle total and its share of that total. Doing this with GROUP BY needs an aggregate in a subquery joined back to the detail, which is three times the text for the same result.

The clause-order rule catches nearly everyone once. Window functions are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY. Two consequences follow. First, the window only ever sees rows that survived WHERE, so filtering to one aisle makes the “total” the total of that aisle alone. Second, you cannot filter on a window result in WHERE:

WHERE SUM(line_total) OVER (PARTITION BY aisle) > 50000   -- error, every engine

The value does not exist yet at that point. Wrap the query in a CTE or derived table and filter on the outer level. That single restriction is behind most “top N per group” solutions you will see.

Windows and GROUP BY are not mutually exclusive. A window function can operate on the output of a GROUP BY, since it runs later:

SELECT aisle, SUM(line_total) AS aisle_total,
       SUM(SUM(line_total)) OVER () AS store_total
FROM bill_lines GROUP BY aisle;

The nested SUM looks wrong on first reading and is correct. The inner one aggregates the group, the outer one is a window over the grouped rows.

OVER () with nothing inside treats the entire result set as one partition, which is the neat way to put a grand total on every row for percentage calculations.

Take five cricket batters ordered by runs scored in a tournament, with two of them level on 412:

SELECT player, runs,
       ROW_NUMBER() OVER (ORDER BY runs DESC) AS rn,
       RANK()       OVER (ORDER BY runs DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY runs DESC) AS dense
FROM tournament_stats;

Read the Bose row. RANK says 4th because two players occupy second place and third is consumed. DENSE_RANK says 3rd because it counts distinct scores, not positions. Neither is more correct; they answer different questions. A leaderboard shown to the public almost always wants RANK, because “joint second, then fourth” is how sport reports it. DENSE_RANK is what you want for “the third highest distinct score”, which is a different claim entirely.

The trap with ROW_NUMBER is that it fabricates an order where none exists. Naik and Rathore are genuinely level, and ROW_NUMBER hands one of them 2 and the other 3. Which one is not defined, and it can change between runs, after an index rebuild, or when the query goes parallel. If you use ROW_NUMBER to pick “the top scorer” and there is a tie, you have written a query that returns different answers on different days.

The fix is to make the ordering deterministic by adding a tiebreaker that is unique:

ROW_NUMBER() OVER (ORDER BY runs DESC, player_id)

Now it is arbitrary but stable, which is what you want for deduplication and pagination.

ROW_NUMBER is still the right choice for exactly those jobs, where you need one row per group and do not care which. Deduplication, “latest record per key”, and stable paging all want distinct numbers and no gaps.

ORDER BY inside OVER is required for RANK and DENSE_RANK, since there is nothing to rank without it. ROW_NUMBER technically permits its absence in some engines, but the result is then non-deterministic and there is no good reason to write it.

Before writing anything, ask the interviewer one question: if two events are priced identically at the top, is the next price down the second highest or the third? People who start typing immediately give the wrong answer half the time, and the asking is a large part of what is being marked.

Prices for concert tickets: 4500, 4500, 3200, 2800.

-- third highest DISTINCT price -> 2800
SELECT price FROM (
  SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS dr
  FROM ticket_prices
) t WHERE dr = 3;

-- third row by price -> 3200
SELECT price FROM ticket_prices ORDER BY price DESC LIMIT 1 OFFSET 2;

Two defensible answers, four hundred rupees apart. “Third highest price” in ordinary speech nearly always means the third distinct price, so DENSE_RANK is the safer default, but say why you chose it.

The window function has to be filtered in an outer query. WHERE DENSE_RANK() OVER (...) = 3 is rejected everywhere, because windows are evaluated after WHERE.

Two edge cases worth raising unprompted. If the table holds only two distinct prices, both queries return zero rows rather than NULL, so calling code that expects a scalar needs to handle an empty result. And NULL prices sort first under ORDER BY price DESC in PostgreSQL and last in MySQL, which shifts every position by one on a nullable column. WHERE price IS NOT NULL removes the ambiguity and costs nothing.

The pre-window classic still comes up on older MySQL:

SELECT MAX(price) FROM ticket_prices
WHERE price < (SELECT MAX(price) FROM ticket_prices
               WHERE price < (SELECT MAX(price) FROM ticket_prices));

It works, it naturally handles ties as one position, and it does not generalise past about N=3 without becoming unreadable. Worth knowing it exists; not worth writing when window functions are available.

LIMIT ... OFFSET is MySQL and PostgreSQL syntax. SQL Server needs OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY, and it requires an ORDER BY.

Run both over a set of sensor readings from a manufacturing line and count the output.

-- 12 machines in, 12 rows out
SELECT machine_id, AVG(vibration_mm_s) AS avg_vibration
FROM sensor_readings GROUP BY machine_id;

-- 4.6 million readings in, 4.6 million rows out
SELECT reading_id, machine_id, vibration_mm_s,
       AVG(vibration_mm_s) OVER (PARTITION BY machine_id) AS machine_avg
FROM sensor_readings;

Identical arithmetic. The first throws away every reading once it has the average; the second keeps all of them and hangs the average off each one, which is what you need to flag readings running above their own machine’s baseline.

Three practical differences follow from that.

Filtering works in opposite directions. HAVING filters GROUP BY output in the same query. A partitioned result cannot be filtered in place at all, because window functions are computed after WHERE and HAVING, so anything comparing a row to its window average needs a CTE and an outer WHERE.

The SELECT list has no restrictions under PARTITION BY. With GROUP BY, every non-aggregated column must be grouped or aggregated. With a window, the row is still there, so you can select any column you like alongside the windowed value.

Ordering is available inside a window and not in a group. PARTITION BY machine_id ORDER BY recorded_at gives each partition an internal sequence, which is what makes LAG, running totals and ranking possible. GROUP BY has no concept of order within a group.

They compose, and the combination is genuinely useful:

SELECT shift_id, machine_id,
       AVG(vibration_mm_s) AS shift_avg,
       AVG(AVG(vibration_mm_s)) OVER (PARTITION BY machine_id) AS machine_avg_of_shifts
FROM sensor_readings
GROUP BY shift_id, machine_id;

GROUP BY runs first and produces one row per shift per machine. The window then runs over those grouped rows. The nested aggregate reads oddly and is correct for exactly that reason.

On cost, PARTITION BY is not free. The engine sorts or hashes by the partition key, so on a large table it can be heavier than the equivalent GROUP BY, which at least gets to shrink its output. An index matching the partition and order keys is what keeps it cheap.

Take a chit fund system moving a member’s contribution between two ledgers.

Atomicity. Debit one account, credit another. If the second statement fails, the first must be undone. The unit is the transaction, not the statement, and a crash midway leaves nothing half-applied.

BEGIN;
UPDATE member_ledger SET balance = balance - 25000 WHERE member_id = 4102;
UPDATE fund_pool    SET balance = balance + 25000 WHERE fund_id = 7;
COMMIT;

Consistency. Constraints, foreign keys and triggers hold before and after the transaction. This is the letter people explain least well, because it is not something the database invents; it is the guarantee that your declared rules are never violated by a committed transaction. If you never declared a constraint that balances cannot go negative, consistency will not save you from one.

Isolation. Another transaction reading the ledger mid-transfer should not see money that has left one account and not yet arrived in the other. How strictly this holds depends on the isolation level, which is where the practical complexity lives.

Durability. Once COMMIT returns, the change survives a power cut. Engines achieve this by writing to a log and flushing it before acknowledging. The caveat worth mentioning: durability is only as good as the flush settings. MySQL’s innodb_flush_log_at_trx_commit = 2 and PostgreSQL’s synchronous_commit = off both trade a window of recent transactions for a large throughput gain, and both are deliberately chosen in real systems. Someone somewhere decided your data is durable within one second rather than absolutely.

Isolation is the letter relaxed most often. Full serialisability costs concurrency, so most databases default to something weaker and accept specific anomalies in exchange for throughput.

Distributed systems are where the whole model bends. Cross-shard atomicity requires two-phase commit or a consensus protocol, both expensive, which is why many distributed stores offer atomicity within a partition only and eventual consistency across them. Knowing which guarantee you have given up, and what breaks because of it, is the actual engineering question underneath the acronym.

WITH monthly AS (
  SELECT DATE_TRUNC('month', enrolled_on) AS month,
         SUM(premium) AS premium
  FROM mutual_fund_sips
  GROUP BY 1
)
SELECT month,
       premium,
       LAG(premium) OVER (ORDER BY month) AS prev_month,
       ROUND(100.0 * (premium - LAG(premium) OVER (ORDER BY month))
             / NULLIF(LAG(premium) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly;

The ORDER BY inside OVER is what defines “previous”. It has nothing to do with the ORDER BY of the outer query, and getting them out of sync is a common source of results that look shuffled.

Three things to have ready when this is asked.

The first row returns NULL, always, because there is no earlier row to reach. That is usually correct and should be presented as “no comparison available” rather than patched to zero, which would render as a 100% jump on the chart. If a default genuinely makes sense, LAG takes a third argument: LAG(premium, 1, 0).

NULLIF on the denominator is not decoration. One month with zero collection and the percentage calculation aborts the whole query in PostgreSQL. NULLIF turns the zero into NULL and the expression yields NULL for that row while everything else still computes.

The failure that actually reaches production is the missing month. LAG steps back one row, not one month. If April had no SIP enrolments, April has no row, and May’s LAG reaches all the way back to March while the column header still says “previous month”. The comparison is silently wrong and nothing errors. The fix is a date spine: generate every month, LEFT JOIN the actuals onto it, and let LAG operate over a complete series.

Partitioning applies the same way. PARTITION BY scheme_code ORDER BY month restarts the comparison for each fund, and the first month of every scheme correctly returns NULL rather than borrowing the last month of the previous one.

For a year-on-year comparison, LAG with an offset of 12 works only when every month is present, which is the same reason the spine matters. A self join on a date expression is more robust when the series is sparse.

Start by settling what “duplicate” means. Two grievance tickets from the same citizen about the same complaint type on the same day are probably a double submission. Two tickets a month apart are not. That definition is a business decision and it goes in the PARTITION BY, so ask for it rather than assuming the whole row must match.

Finding them:

SELECT citizen_id, complaint_type, filed_on, COUNT(*) AS copies
FROM grievance_tickets
GROUP BY citizen_id, complaint_type, filed_on
HAVING COUNT(*) > 1;

That gives the duplicated keys and how many copies each has. To see the actual rows, including their IDs, ROW_NUMBER is more useful:

WITH marked AS (
  SELECT ticket_id, citizen_id, complaint_type, filed_on,
         ROW_NUMBER() OVER (PARTITION BY citizen_id, complaint_type, filed_on
                            ORDER BY ticket_id) AS rn
  FROM grievance_tickets
)
SELECT * FROM marked WHERE rn > 1;

Every row with rn > 1 is a surplus copy. Run this SELECT and eyeball the results before you delete anything. Deletes on a production table are not a thing to discover a bug in afterwards.

The delete, in PostgreSQL:

DELETE FROM grievance_tickets
WHERE ticket_id IN (SELECT ticket_id FROM marked WHERE rn > 1);

MySQL 8.0 will not let you delete from a table you are also selecting from in a subquery. The self join form works there:

DELETE t FROM grievance_tickets t
JOIN grievance_tickets keep
  ON keep.citizen_id = t.citizen_id
 AND keep.complaint_type = t.complaint_type
 AND keep.filed_on = t.filed_on
 AND keep.ticket_id < t.ticket_id;

The ORDER BY ticket_id decides which copy survives, and it is a real choice, not boilerplate. Ordering by ID keeps the earliest. ORDER BY last_updated_at DESC keeps the most recently edited, which is usually what you want when the copies are not byte-identical and one of them has been worked on.

Two things that separate a careful answer here. If the table has no unique identifier at all, ROW_NUMBER has nothing to target for deletion, and the standard route is to build a deduplicated copy and swap the tables. And once the data is clean, add a unique constraint on those columns, or the same import will recreate the duplicates next week and you will be running this again.

Both stack one result set on top of another. Only one of them does extra work.

SELECT reg_no, marks FROM exam_results_2025
UNION ALL
SELECT reg_no, marks FROM exam_results_2026;

UNION ALL passes rows straight through. Swap in UNION and the engine must compare every row against every other row to eliminate exact duplicates, which means sorting or hashing the whole combined set. On two ten-million-row result sets that is a substantial amount of work, sometimes spilling to disk, to remove duplicates that in this case cannot exist — a 2025 result and a 2026 result differ in at least the year.

So the rule: use UNION ALL unless you specifically need deduplication and cannot get it more cheaply upstream.

Beyond speed, UNION can also quietly delete correct data. Combine two fee payment feeds where a student legitimately paid ₹5,000 twice on the same date, and if every selected column matches, UNION collapses those into one row. Your total is now ₹5,000 short, with no error and no obvious symptom. UNION ALL keeps both.

The mechanical requirements apply to both. Each branch needs the same number of columns, in the same order, with compatible types. Column names come from the first branch and the rest are ignored, so aliasing the second branch does nothing. A type mismatch is where behaviour diverges by engine: PostgreSQL is strict and will refuse to union text with integer, while MySQL applies its usual coercion and may return something surprising rather than an error.

ORDER BY belongs at the end, applied once to the entire combined result, not to the individual branches. Some engines reject an ORDER BY inside a branch outright; others accept it and ignore it, which is worse because it looks like it worked.

One case where UNION is the right call: merging customer contact lists from two acquired businesses, where the same person genuinely appears in both and you want a single row per person. Deduplication is the actual requirement there, not an accident.

Type 1 overwrites and loses history. Type 2 adds a row and keeps it, which is what you want whenever anyone will ever ask “what did this look like at the time”.

CREATE TABLE dim_outlet (
  outlet_sk     BIGINT PRIMARY KEY,      -- surrogate, meaningless, stable
  outlet_id     VARCHAR(20) NOT NULL,    -- natural key from the source
  manager_name  VARCHAR(100),
  format        VARCHAR(30),
  valid_from    DATE NOT NULL,
  valid_to      DATE NOT NULL DEFAULT DATE '9999-12-31',
  is_current    BOOLEAN NOT NULL DEFAULT TRUE
);

The load is two statements inside one transaction. Close the outgoing version, then insert the incoming one:

UPDATE dim_outlet d
SET valid_to = CURRENT_DATE - 1, is_current = FALSE
FROM stg_outlet s
WHERE s.outlet_id = d.outlet_id
  AND d.is_current
  AND (d.manager_name, d.format) IS DISTINCT FROM (s.manager_name, s.format);

INSERT INTO dim_outlet (outlet_sk, outlet_id, manager_name, format, valid_from)
SELECT nextval('outlet_sk_seq'), s.outlet_id, s.manager_name, s.format, CURRENT_DATE
FROM stg_outlet s
JOIN dim_outlet d ON d.outlet_id = s.outlet_id AND d.is_current
WHERE (d.manager_name, d.format) IS DISTINCT FROM (s.manager_name, s.format);

IS DISTINCT FROM is doing real work there. Plain <> returns unknown when either side is NULL, so a manager going from NULL to a name would not be detected as a change, and the version would never be created. That is a genuine production bug and it is invisible until someone audits.

Two design decisions to be able to defend. The surrogate key exists because the natural key is no longer unique once you have multiple versions; the fact table stores outlet_sk, captured at load time, which is what pins each transaction to the attributes in force that day. And the end date is a real date far in the future rather than NULL, so range predicates work without a special case. Using 9999-12-31 versus NULL is a genuine team convention split, so say which you use and why.

Querying comes in two flavours. Current state is WHERE is_current. Point-in-time is a range join:

SELECT f.sale_date, d.manager_name, f.amount
FROM fact_sales f
JOIN dim_outlet d ON d.outlet_id = f.outlet_id
                 AND f.sale_date BETWEEN d.valid_from AND d.valid_to;

Watch the boundaries. If valid_to on the closed row equals valid_from on the new one, BETWEEN matches both and every fact on that date doubles. Close the old row at the day before, or switch to a half-open interval with >= valid_from AND < valid_to, which is the more robust convention when changes can happen intraday.

Same-day double changes are the edge case interviewers reach for. With day-grain dating you get a zero-length version that either duplicates or vanishes, so either move to timestamp grain or collapse to the last state of the day.

The shape is simple. Getting it correct at the boundary is not.

-- 1. read the watermark
SELECT last_loaded_at FROM etl_control WHERE table_name = 'meter_readings';

-- 2. pull the delta, with deliberate overlap
SELECT * FROM source.meter_readings
WHERE updated_at >= :last_loaded_at - INTERVAL '15 minutes'
  AND updated_at <  :batch_start;

-- 3. merge into target, then advance the watermark
UPDATE etl_control SET last_loaded_at = :batch_start WHERE table_name = 'meter_readings';

Four things decide whether this loses data.

Open upper bound. Reading everything up to NOW() while the source is still writing means a row committed a millisecond later carries a timestamp inside your window but was never visible to your query. It is skipped permanently. Freeze :batch_start once at the beginning and use a strict < upper bound.

Ties at the boundary. Twelve readings share the exact second at which the previous batch stopped. > last_loaded_at drops them; >= reprocesses some. Reprocessing is the safe error, which is why the window overlaps and the merge must be idempotent. Never rely on a watermark alone for correctness.

Commit order versus timestamp order. A long transaction can stamp updated_at when it starts and commit after your batch has moved past that point. The row is then permanently below the watermark and invisible. This is the failure that survives testing and shows up as a mysterious missing meter three months later. Logical replication or CDC solves it properly; the overlap window only reduces the odds.

Source discipline. The whole approach assumes every update touches updated_at. A bulk correction run in the source database with a direct UPDATE that skips the trigger produces changes your pipeline will never see. Ask whether the column is maintained by a trigger or by application code, because application code is where it gets forgotten.

Keep the watermark in a control table rather than computing MAX(updated_at) from the target. Deriving it from the target means a partial load leaves the watermark somewhere undefined, and a truncate-and-reload silently resets your entire pipeline history.

Hard deletes are the standing limitation. A deleted row has no updated timestamp, so a watermark load can never detect it. Either the source soft-deletes with a flag, or you run a periodic full key reconciliation, or you move to CDC.

Two analysts pull the average credit score of loan applicants and get different numbers. One wrote AVG(credit_score). The other wrote SUM(credit_score) / COUNT(*). Both queries are valid, and one of them is answering a different question than the analyst thinks.

SELECT COUNT(*)            AS applications,   -- 10000
       COUNT(credit_score) AS scored,         -- 8200
       SUM(credit_score)   AS total_score,
       AVG(credit_score)   AS mean_score
FROM loan_applications;

So AVG(credit_score) divides by 8,200. The manual version divides by 10,000 and produces a mean roughly 18% lower, because 1,800 thin-file applicants with no bureau history were silently treated as zeros.

Which is correct depends on what NULL means in that column. For a mean credit score, an unknown score should be excluded, and AVG is right. For “average disbursed amount per application” where a NULL means nothing was disbursed, zero is the true value and AVG(COALESCE(disbursed_amount, 0)) is what you want. Decide that before you write the query, not after someone questions the number.

The failure that reaches production most often is the empty set. Filter to a branch that made no disbursals last month and COUNT(*) returns 0, but SUM(disbursed_amount) and AVG(disbursed_amount) both return NULL, not ₹0. The dashboard tile renders blank, and any downstream arithmetic on that NULL propagates NULL through the rest of the calculation. COALESCE(SUM(disbursed_amount), 0) is cheap insurance on every reporting query.

COUNT(DISTINCT credit_score) also skips NULL. And COUNT(1) is identical to COUNT(*), not a NULL question at all, though interviewers sometimes hide it inside this one.

Retries are not exceptional. Schedulers retry, operators rerun a failed day, someone backfills a week. A pipeline that only produces correct output when every task runs exactly once is a pipeline that is wrong on a regular basis.

The pattern that covers most batch transforms is delete-write on a partition key:

BEGIN;
DELETE FROM fact_toll_transits WHERE transit_date = DATE '2026-08-19';
INSERT INTO fact_toll_transits (transit_date, plaza_id, vehicle_class, transits, amount)
SELECT transit_date, plaza_id, vehicle_class, COUNT(*), SUM(toll_amount)
FROM stg_toll_events
WHERE transit_date = DATE '2026-08-19'
GROUP BY 1, 2, 3;
COMMIT;

Run it fifty times, get the same table. The transaction is what makes it safe rather than merely repeatable, because a crash between the delete and the insert would otherwise leave a day of data missing. On a partitioned table, swapping or truncating the partition is cheaper than a DELETE and achieves the same thing.

Where the target is keyed rather than partitioned, an upsert on the business key gives the same property, with the update branch overwriting whatever the previous run wrote.

Three things quietly break idempotency even when the write pattern looks right.

NOW() or CURRENT_DATE inside the transform. The second run computes a different window than the first, so “rerun yesterday’s job” reruns today’s. Pass the logical date in as a parameter and never read the wall clock inside the SQL.

Sequences and auto-increment surrogate keys. Rerunning generates fresh IDs for the same logical rows, so anything downstream that captured the old ones now points at nothing. Derive the key deterministically from the business columns, or accept that the surrogate is only stable if the row is never rewritten.

Reading from a source that keeps moving. If the transform selects “everything not yet processed” from a live table, the second run sees a different set. Bound the read by the logical date, not by a state flag.

Concurrency is the case people forget. Two retries of the same partition running together can interleave the delete and insert and produce a partially doubled day. A unique constraint on the partition grain, or an advisory lock, or scheduler-level guarantees of single execution, all work. Assuming it cannot happen does not.

On a straightforward deduplication, these two are interchangeable:

SELECT DISTINCT vehicle_id FROM trips WHERE trip_date >= '2026-08-01';

SELECT vehicle_id FROM trips WHERE trip_date >= '2026-08-01' GROUP BY vehicle_id;

Same rows, same order guarantees (none, in both cases), and on MySQL and PostgreSQL you will typically see the same plan in EXPLAIN. Anyone who tells you one is universally faster is repeating something they read. Check the plan on your own data.

The genuine split is intent. GROUP BY is the right tool the moment you want something about each group: trips per vehicle, total distance, last trip date. DISTINCT is the right tool when you only want the unique values and nothing else, and it signals that intent to whoever reads the query next.

The gotcha that actually costs people: DISTINCT applies to the entire select list, not to the column it happens to sit next to.

SELECT DISTINCT vehicle_id, driver_id FROM trips;

That returns distinct vehicle-driver pairs. A truck driven by four different drivers appears four times, and someone who expected one row per truck now has an inflated fleet count. If you need one row per vehicle plus a representative driver, you need an aggregate or a window function, not DISTINCT.

Related, and worth flagging in a code review: DISTINCT bolted on to a query because the join was returning duplicates. It hides a fan-out rather than fixing it, and it can also delete rows you wanted, if two legitimately different records look identical once the extra columns are dropped from the select list.

A public library wants members who have never borrowed a book. Three ways to ask for it:

-- 1. LEFT JOIN + IS NULL
SELECT m.member_id
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
WHERE l.member_id IS NULL;

-- 2. NOT EXISTS
SELECT m.member_id
FROM members m
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.member_id);

-- 3. NOT IN
SELECT m.member_id
FROM members m
WHERE m.member_id NOT IN (SELECT member_id FROM loans);

The first two always agree. The third agrees only while loans.member_id contains no NULLs. Add one row with a NULL member ID, from a bulk import or a nullable legacy column, and version 3 returns an empty set. Not an error, not a warning, just zero members. Because of how NULL comparisons resolve, the expression can never be true once a NULL is in the list.

Ship NOT EXISTS. It is immune to that, it stops scanning the moment it finds the first match, and it states the intent in the query text rather than encoding it as a side effect of a NULL check.

The LEFT JOIN form has a detail that catches people in review: the IS NULL test must be on a column that is never NULL in a matched row. Test the join key or the right table’s primary key. If you write WHERE l.returned_on IS NULL instead, you have quietly changed the question to “members with an outstanding loan”, which is a different report entirely.

On scale, the LEFT JOIN form materialises every matching pair before the filter throws them away. A member with 400 loans generates 400 rows so that 399 of them can be discarded. NOT EXISTS never builds those rows. On small tables the optimiser often converts one form into the other and the plans converge, but on a wide loans table the difference is measurable, and it goes the same way on both MySQL and PostgreSQL.

Nothing special happens in the engine. The table appears twice in the FROM clause, and the aliases are what make it readable:

SELECT e.staff_id,
       e.full_name  AS employee,
       m.full_name  AS reports_to
FROM store_staff e
LEFT JOIN store_staff m ON m.staff_id = e.manager_id;

e is the employee side, m is the manager side. Same physical table, two independent row sources.

The choice of LEFT over INNER is the whole interview question. A retail chain’s regional head has manager_id set to NULL. With an INNER JOIN that row fails to match and disappears, so your org chart is missing exactly the person at the top, and the row count is one short in a way nobody notices. LEFT JOIN keeps them with reports_to as NULL, and COALESCE(m.full_name, 'Regional Head') presents it cleanly.

Ask the interviewer one thing before writing: is manager_id guaranteed to point at a live row? Real HR tables accumulate orphans when a manager leaves and their record is soft-deleted or archived. Those employees also come back with a NULL manager under a LEFT JOIN, and they mean something completely different from the person at the top. Distinguishing the two needs an explicit check on the manager row’s status, not just a NULL test.

Self joins are not only for hierarchies. Comparing a row to its neighbour uses the same shape, such as finding two stock transfers between the same pair of stores on the same day:

SELECT a.transfer_id, b.transfer_id
FROM transfers a
JOIN transfers b
  ON b.from_store = a.from_store
 AND b.transfer_date = a.transfer_date
 AND b.transfer_id > a.transfer_id;

That > on the key is doing real work. Without it every row pairs with itself, and every genuine pair appears twice in reversed order. On a large table, forgetting it turns a modest result into something the query planner spends a very long time producing.

One honest caveat for the follow-up: a single self join gives you one level. Arbitrary depth needs a recursive CTE, available in MySQL 8.0 and PostgreSQL, not in MySQL 5.7.

Booking data only contains nights that were actually booked. A hotel with no suite bookings on 14 August has no row for suites on 14 August, so a GROUP BY over that table produces an occupancy chart with holes in it. Every gap is a night the revenue team most wants to see.

The fix is to generate the complete grid first, then attach the facts to it:

SELECT d.stay_date,
       rt.room_type,
       COALESCE(COUNT(b.booking_id), 0) AS rooms_sold
FROM calendar d
CROSS JOIN room_types rt
LEFT JOIN bookings b
       ON b.stay_date = d.stay_date
      AND b.room_type = rt.room_type
WHERE d.stay_date BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY d.stay_date, rt.room_type;

31 dates crossed with 5 room types gives 155 rows, guaranteed, whether or not anything was sold. The LEFT JOIN is not optional here. Swap it for an INNER JOIN and you have thrown away the empty combinations you just built the spine to expose.

Category completion is the same pattern. Every sales region crossed with every product line, so a region that sold nothing this quarter shows a zero rather than being absent from the comparison.

Two things to keep in view. First, the output size is the product of the inputs, so it grows fast: dates by room type by rate plan by channel reaches six figures before you have joined anything. Restrict the date range inside the spine rather than after the cross, so the smaller set is what gets multiplied.

Second, the accident. An old-style comma join with a missing predicate is a CROSS JOIN wearing a disguise:

SELECT * FROM bookings b, room_types rt;   -- no WHERE, no ON

Nobody writes that on purpose in a two-table query. It happens in a five-table FROM clause where one join condition was dropped during an edit. The query does not fail. It just runs for a long time and returns an implausible number of rows. Writing CROSS JOIN explicitly when you mean it, and using ANSI JOIN syntax everywhere else, makes the accidental version visible on sight.

Keep both. The argument is only about which one the fact table stores.

CREATE TABLE dim_seller (
  seller_sk    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  seller_code  VARCHAR(24) NOT NULL,     -- natural key from the marketplace
  legal_name   VARCHAR(200),
  gstin        VARCHAR(15),
  CONSTRAINT uq_seller_code UNIQUE (seller_code)
);

Four reasons the surrogate wins as the primary key.

Natural keys mutate. A marketplace restructures its seller codes during a platform migration and every fact row referencing the old value is now orphaned. With a surrogate, you update one column in one dimension row.

They get reused. A retired code reissued to a different seller eighteen months later silently merges two businesses’ history into one entity, and nothing errors. This is the failure worth describing in an interview, because it is invisible until someone questions a suspiciously large seller.

They are wide and composite. A three-column natural key propagates into every fact table and every index built on it, costing storage on hundreds of millions of rows and making every join more expensive than an 8-byte integer comparison.

They are not universal. Merge a second marketplace and the two code spaces collide. A surrogate namespace absorbs both.

Type 2 dimensions make the surrogate mandatory rather than preferable, since the natural key stops being unique once you keep multiple versions of a seller.

The unique constraint on the natural key is not optional. Without it, a repeated load creates a second surrogate for the same seller, facts split across both, and the two halves of one seller’s revenue never reconcile. That constraint is also what your upsert matches on.

On sequence versus UUID: sequences are compact, ordered, and index well, which matters for clustered storage. UUIDs are generatable without coordination, so they suit distributed writes and multi-system merges, at the cost of size and, for random UUIDs, index fragmentation. Neither is a default; say which problem you are solving.

Three options along one axis: how much precomputation you are willing to trade for staleness.

A view is a named query. Referencing it inlines the definition, so the ninety seconds is paid on every dashboard load. Views are excellent for encapsulating join logic and enforcing consistent business definitions, and they do nothing at all for performance.

A materialised view stores the result set on disk:

CREATE MATERIALIZED VIEW mv_hostel_occupancy AS
SELECT hostel_id, stay_date, COUNT(*) AS beds_filled, SUM(tariff) AS revenue
FROM bed_nights
GROUP BY hostel_id, stay_date;

CREATE UNIQUE INDEX ON mv_hostel_occupancy (hostel_id, stay_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hostel_occupancy;

Now the dashboard reads precomputed rows. The unique index earns its place twice: it speeds lookups, and PostgreSQL requires one before REFRESH ... CONCURRENTLY will work. Without CONCURRENTLY, the refresh takes an exclusive lock and every dashboard query blocks for its duration, which turns a nightly optimisation into a nightly outage.

Support varies enough to matter. PostgreSQL and Oracle have materialised views with manual or scheduled refresh. MySQL has none, so the equivalent is a summary table plus a scheduled job. SQL Server’s indexed views auto-maintain but impose real constraints on the query and slow down writes to the base tables.

Go to a physical table when the refresh is more than “rerun the query”. Incremental loading of only changed partitions, retaining history the base tables no longer hold, or applying corrections that the source will never reflect all require a table with its own load logic. A materialised view is by definition derivable from its query; anything that is not, is a table.

The question to ask before choosing is how stale the number is allowed to be. A finance close needs live figures. An occupancy dashboard is fine at an hour old, and often fine at a day old, and nobody will tell you which unless you ask. Whatever you pick, expose the refresh timestamp on the dashboard. A materialised view whose refresh job silently failed on Tuesday looks exactly like real data on Friday.

Everything the WHERE clause let through collapses into one group. One group, one row:

SELECT COUNT(*)          AS bills_issued,
       SUM(units_billed) AS total_units,
       MAX(amount_due)   AS highest_bill
FROM electricity_bills
WHERE billing_month = '2026-07';

One row back, always. Not one row per consumer, not one per division.

The interesting case is when the filter matches nothing. Point that query at a division with no billing run and you still get one row, containing 0 for the count and NULL for the sum and the max. This catches people writing validation logic. A check like IF (SELECT SUM(units_billed) ...) > 0 does not go to the false branch on empty data. It goes to unknown, which is not true, and depending on the surrounding code that can look like a pass. Wrap it in COALESCE and compare a real number.

The other half of the question is what happens when you add a plain column:

SELECT consumer_id, MAX(amount_due) FROM electricity_bills;

There is one group covering millions of rows, and one consumer_id has to be chosen out of all of them. PostgreSQL and SQL Server refuse to guess and raise an error. MySQL with ONLY_FULL_GROUP_BY disabled runs it and returns some consumer_id, not necessarily the one holding the maximum bill. That is the actual danger: the output looks exactly like the answer to “who had the highest bill”, it is formatted like the answer, and it is frequently not the answer. Getting the row that holds the maximum needs a window function, an ORDER BY with LIMIT, or a subquery on the max value.

Because there is a group, HAVING is legal here too. SELECT SUM(units_billed) FROM electricity_bills HAVING SUM(units_billed) > 100000 gives you either one row or an empty result, which is a compact way to write a threshold check.

Referencing a CTE four times does not guarantee it is computed once. In PostgreSQL 12 and later, a non-recursive CTE used more than once is materialised rather than inlined, so it is computed once; used a single time it is inlined by default. MySQL 8.0 materialises CTEs into internal temporary tables in many cases. SQL Server inlines them unconditionally, so a CTE referenced four times becomes four evaluations of the underlying query. That last one surprises people moving between engines.

A temp table removes the ambiguity:

CREATE TEMP TABLE tmp_active_devices AS
SELECT device_id, MAX(heartbeat_at) AS last_seen, COUNT(*) AS beats
FROM device_heartbeats
WHERE heartbeat_at >= CURRENT_DATE - 7
GROUP BY device_id;

CREATE INDEX ON tmp_active_devices (device_id);
ANALYZE tmp_active_devices;

Three properties a CTE cannot give you. It is computed exactly once, no matter how many statements reference it. You can index it, which matters when the next step joins it to something large. And after ANALYZE it carries statistics, so the optimiser plans downstream joins on real row counts instead of a guess. Bad cardinality estimates on a large intermediate are a frequent cause of a nested loop where a hash join was needed, and that is a hundredfold difference, not a marginal one.

The costs are real too. A temp table means writing to disk, extra statements to manage, and in PostgreSQL, heavy temp table churn generates catalog bloat that eventually needs attention. It also breaks the transform into pieces, which some teams find clearer and others find harder to follow.

Where the intermediate is small, use the CTE and keep the query readable. Where it is large, reused, and joined onward, the temp table usually wins.

Decide by measuring rather than by rule. Run both, compare EXPLAIN ANALYZE on the whole transform, and look specifically at whether the CTE version repeats a scan and whether the estimated rows match the actual. Anyone who answers this question with a blanket preference is guessing.

The shape of what comes back decides where it is legal.

Scalar. One row, one column. It behaves like a value, so it can appear in SELECT, WHERE, or an expression:

SELECT listing_id,
       asking_price,
       asking_price - (SELECT AVG(asking_price) FROM listings) AS diff_from_city_avg
FROM listings
WHERE locality = 'Whitefield';

The failure mode is sharp. If a scalar subquery returns more than one row at runtime, the query aborts with a cardinality error. It is a data-dependent bug: correct in staging where each locality has one benchmark row, dead in production the day a second benchmark is inserted. Anything with a GROUP BY or a non-unique filter inside a scalar position deserves a second look. Returning zero rows is gentler, giving you NULL rather than an error, which then quietly poisons the arithmetic around it.

Row. One row, several columns, compared against a row constructor:

SELECT * FROM listings
WHERE (locality, bhk) = (SELECT locality, bhk FROM listings WHERE listing_id = 4471);

MySQL and PostgreSQL support this. SQL Server does not, so there you write out the columns separately, which is why row subqueries rarely turn up in portable code.

Table. Many rows, one or more columns. It goes in FROM as a derived table, or feeds IN and EXISTS:

SELECT l.locality, l.median_price
FROM (
  SELECT locality, AVG(asking_price) AS median_price, COUNT(*) AS listings
  FROM listings GROUP BY locality
) l
WHERE l.listings >= 20;

A derived table in FROM must be given an alias in MySQL and PostgreSQL, and forgetting it is the most common syntax error people hit with this form.

Cutting across all three is correlation. An uncorrelated subquery does not reference the outer query and can be evaluated once. A correlated one references an outer column and is logically evaluated per outer row, though optimisers frequently rewrite it into a join. Correlated scalar subqueries in a SELECT list over a large result set are the classic slow query in a review, and turning one into a join against a pre-aggregated derived table is usually the fix.

The dependency is the whole definition. Remove the reference to the outer table and the subquery becomes independent, computable once, and cheap.

-- uncorrelated: evaluated once
SELECT crate_id FROM shipments
WHERE weight_kg > (SELECT AVG(weight_kg) FROM shipments);

-- correlated: depends on s.route_id, evaluated per outer row
SELECT s.crate_id, s.weight_kg FROM shipments s
WHERE s.weight_kg > (SELECT AVG(x.weight_kg) FROM shipments x
                     WHERE x.route_id = s.route_id);

The second one compares each crate against the average for its own route, which the first cannot express.

Now the cost. “Runs once per outer row” is the semantics, not a promise about execution. PostgreSQL and MySQL 8.0 will often transform a correlated subquery into a semi-join or a hash join and the plan comes out fine. What you should be able to say in the room is when the transform fails. Put a correlated aggregate in the SELECT list over a large result set and it frequently does not:

SELECT s.crate_id,
       (SELECT COUNT(*) FROM scan_events e WHERE e.crate_id = s.crate_id) AS scans
FROM shipments s;

With 400,000 shipments and no index on scan_events.crate_id, that is 400,000 full scans of the events table. In EXPLAIN it shows up as a subplan with a high loop count, and the query that finished in eight seconds during testing on a month of data takes forty minutes against two years of it.

The rewrite is to aggregate once and join:

SELECT s.crate_id, COALESCE(e.scans, 0) AS scans
FROM shipments s
LEFT JOIN (SELECT crate_id, COUNT(*) AS scans FROM scan_events GROUP BY crate_id) e
       ON e.crate_id = s.crate_id;

One pass over the events table instead of 400,000. Note the COALESCE: the correlated version returns 0 for a crate with no scans, while the join returns NULL, and losing that difference silently changes the report.

An index on the correlated column is the other lever, and often the faster fix in production. Before reaching for either, check whether a window function expresses the intent directly, because for per-group comparisons it usually does and it reads better.

The index is sorted by city first, then service_type within each city, then raised_at within each pair. That ordering is the entire explanation. A phone directory sorted by surname then first name lets you find every Rao, and every Rao whose first name is Anita, but gives you nothing if all you know is Anita.

Against (city, service_type, raised_at) on a civic complaints table:

The fifth row is the subtle one. The gap at service_type means every raised_at value inside Pune is scattered rather than contiguous, so the engine can seek to Pune and must then filter the rest. It works and it is far less efficient than the interviewer’s phrasing implies.

The design rule that follows: equality predicates first, then the range column, then anything used only for ordering or covering. Reversing that puts a range in the middle and everything after it becomes a filter rather than a seek. Order the equality columns among themselves by selectivity where the workload allows, though matching the most common query shape matters more than selectivity in practice.

MySQL and SQL Server both call this the leftmost prefix rule and behave as described. PostgreSQL is more forgiving: it can perform a full index scan using a non-leading column as a filter, which is sometimes faster than a table scan since the index is narrower, and its bitmap index scans can also combine several single-column indexes. Do not lean on that. A properly ordered composite still beats it.

One practical consequence: an index on (city, service_type) is redundant if (city, service_type, raised_at) already exists, since the shorter one is a prefix of the longer. Dropping it removes write cost with no loss.

The clustered index is not a copy of the data. It is the data, held in key order. That is why a table gets exactly one, and why the choice of clustering key shapes everything else.

In SQL Server, a table with a clustered index is called a clustered table and one without is a heap. In MySQL’s InnoDB every table is clustered, on the primary key if you declared one and on a hidden internal key otherwise. PostgreSQL is the outlier: it has no clustered index in this sense. The CLUSTER command physically reorders a table once against an index, and normal write activity immediately begins undoing that ordering, so it is a one-off maintenance operation rather than a permanent property.

Take a scooter rental table clustered on (station_id, rented_at). Fetching every rental from station 12 last week reads a contiguous run of pages. The same query against a heap with a non-clustered index reads the index, then jumps to a scattered set of pages, one lookup per row. At a few hundred rows the difference is small; at two lakh it is the whole query.

Two consequences worth knowing in an interview.

Non-clustered indexes in InnoDB and SQL Server store the clustering key as the row pointer, not a physical address. So a wide clustering key, such as a three-column composite of varchars, is duplicated into every secondary index on the table. Six secondary indexes means six copies. A narrow integer or bigint key keeps them small.

Insert order matters enormously. A monotonically increasing key appends at the end of the structure, filling pages neatly. A random key such as a version 4 UUID inserts into the middle constantly, splitting pages, fragmenting the table and inflating the working set. On a high-insert table this is a measurable throughput difference, and it is the reason ordered UUID variants exist.

Choose the clustering key by how the table is read in ranges, not by what looks like a natural identifier. Time-series data clustered on a timestamp, or on a tenant plus timestamp, tends to beat clustering on a surrogate ID that nobody filters by.

Partitioning splits one logical table into physical pieces the engine can skip entirely. It is not an index; it removes data from consideration before any index is consulted.

CREATE TABLE fact_seat_bookings (
  booking_id   BIGINT,
  booked_on    DATE NOT NULL,
  operator_id  INT,
  fare         NUMERIC(10,2)
) PARTITION BY RANGE (booked_on);

CREATE TABLE fact_seat_bookings_2026_08 PARTITION OF fact_seat_bookings
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Range partitioning on a date is the common case because analytical queries almost always carry a date filter. List partitioning suits a small fixed set such as region. Hash partitioning spreads writes evenly when there is no natural range key, and it gives you no pruning for range queries, only for equality on the hash column.

Verify pruning rather than assuming it. In PostgreSQL, EXPLAIN shows which partitions are scanned; a query with a one-month filter that lists forty partitions is not pruning. MySQL has EXPLAIN PARTITIONS, and its output names the partitions touched.

The predicate has to be usable. This is where it silently fails:

WHERE EXTRACT(YEAR FROM booked_on) = 2026     -- no pruning, scans everything
WHERE booked_on >= '2026-01-01'
  AND booked_on <  '2027-01-01'               -- prunes

Same logic, completely different plan. Wrapping the partition key in a function, comparing it against a column from another table, or joining on it without a literal bound all defeat pruning. A parameterised query can also lose it if the planner cannot see the value at plan time, though PostgreSQL 11 onwards handles runtime pruning for many of these cases.

Partition size is a real decision. Too coarse and you scan more than you need. Too fine and the planner spends its time on thousands of partitions, and each one carries its own indexes and catalog overhead. Monthly partitions for a table growing a few crore rows a month is a reasonable starting shape; daily when queries are always single-day.

The strongest argument for partitioning often has nothing to do with queries. Dropping a partition is a metadata operation that completes instantly, where DELETE FROM ... WHERE booked_on < '2023-01-01' on three billion rows runs for hours, bloats the table, and stalls replication. If you have a retention policy, partition by the retention column.

Cumulative donations for a crowdfunding campaign:

SELECT donation_date,
       amount,
       SUM(amount) OVER (ORDER BY donation_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM donations
WHERE campaign_id = 88;

Drop the ROWS clause and the query still runs. That is the part worth understanding, because the default is not what most people picture.

Adding ORDER BY to a window without specifying a frame applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not positions, so every row sharing the current row’s ORDER BY value is treated as part of the current row. Three donations on 12 August all get the same running total, the total including all three. It looks like the running total froze for three rows and then jumped.

ROWS counts physical rows instead, so each of those three donations accumulates one at a time.

Neither is wrong. A daily cumulative chart genuinely wants the RANGE behaviour. A donor-by-donor ledger wants ROWS. Choose deliberately and write the frame down, so the next reader does not have to know the default to understand the query.

Two more points. If you aggregate to one row per date first, ties disappear and the distinction stops mattering, which is the cleanest way to sidestep it for reporting.

And an ORDER BY that is not unique makes ROWS non-deterministic between rows with the same key, just as it does for ROW_NUMBER. Add the donation ID as a tiebreaker if reproducibility matters.

The frame is also how you get moving windows. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a trailing seven-row sum, and with a complete date spine that is a trailing seven-day sum. PARTITION BY campaign_id restarts the accumulation for each campaign.

Window functions including frames arrived in MySQL 8.0. On 5.7 a running total needs a correlated subquery or a session variable, and neither is pleasant.

Three anomalies, defined against a warehouse stock ledger.

Dirty read. You read a quantity another transaction has written but not committed. It rolls back and you acted on a number that never existed.

Non-repeatable read. You read the stock for SKU 991 twice in one transaction and get different values, because someone committed a change in between.

Phantom read. You count rows matching a condition twice and get different counts, because someone inserted a row that matches.

The table is the standard. What engines actually do differs enough to be worth stating explicitly, and interviewers who know this are listening for it.

PostgreSQL has no true READ UNCOMMITTED; requesting it gives you READ COMMITTED. Its default is READ COMMITTED, and each statement sees a fresh snapshot taken at statement start.

PostgreSQL’s REPEATABLE READ uses a snapshot taken at transaction start and, as a result of that snapshot mechanism, does prevent phantoms, exceeding what the standard requires. Its SERIALIZABLE adds serializable snapshot isolation, which detects dangerous dependency cycles and aborts one transaction with a serialisation failure.

MySQL’s InnoDB defaults to REPEATABLE READ, and uses next-key locking on locking reads to block phantoms, while plain non-locking SELECTs read from a consistent snapshot. The upshot is that a plain SELECT and a SELECT ... FOR UPDATE inside the same transaction can see different data, which surprises people.

The anomaly the table does not list is write skew: two transactions each read overlapping data, each decides its write is safe, and together they violate an invariant neither could see being broken. Two dispatchers each confirming the last unit of stock is the classic shape. Snapshot isolation permits it. Only SERIALIZABLE, or an explicit lock, prevents it.

Running at SERIALIZABLE means your application must expect transactions to be aborted and must retry them. Code that treats a serialisation failure as a fatal error will fail intermittently under load, and that is the practical cost of the strongest level.

Autocommit is the default in most clients, so each statement is its own transaction unless you open one explicitly. Forgetting that is how a multi-statement load leaves half its work applied after a failure.

BEGIN;
INSERT INTO seed_dispatch (lot_id, dealer_id, bags) VALUES (5501, 88, 400);
SAVEPOINT after_first_lot;
INSERT INTO seed_dispatch (lot_id, dealer_id, bags) VALUES (5502, 91, -12);
ROLLBACK TO SAVEPOINT after_first_lot;
INSERT INTO seed_dispatch (lot_id, dealer_id, bags) VALUES (5502, 91, 120);
COMMIT;

The bad row is discarded, the good ones survive, and one COMMIT at the end makes the whole set permanent. Releasing a savepoint does not commit anything; it only discards the marker.

Savepoints earn their place in a loop processing a batch where individual records can fail independently and you want to keep the rest. They are how most ORMs implement nested transactions, since databases do not truly nest them.

Three things that trip people up.

A dropped connection with an open transaction rolls back. The database has no way to know your intent, so the safe default is to discard. Any client-side retry must reissue the whole transaction, which is another reason idempotency matters.

DDL behaviour splits by engine. PostgreSQL is transactional for DDL, so a CREATE TABLE inside a transaction rolls back cleanly, which makes schema migrations genuinely atomic. MySQL commits implicitly before and after most DDL, so a failed migration leaves a partially altered schema behind, and Oracle behaves similarly.

In PostgreSQL, an error inside a transaction aborts it entirely. Every subsequent statement returns “current transaction is aborted” until you ROLLBACK or roll back to a savepoint. MySQL does not do this; a statement can fail and the transaction continues, which means silently incomplete work if nobody is checking.

The operational hazard is the long-open transaction. A session that ran BEGIN, executed one statement, and went idle holds its snapshot and any locks it took. In PostgreSQL that blocks vacuum from reclaiming dead tuples across the whole database, and tables bloat until someone notices. Set idle_in_transaction_session_timeout and keep transactions short.

The useful convention is that a check returns the offending rows. Zero rows means pass, and any output is both the alert and the debugging material.

-- uniqueness on the declared grain
SELECT policy_no, effective_date, COUNT(*)
FROM fact_crop_cover
GROUP BY policy_no, effective_date
HAVING COUNT(*) > 1;

-- referential integrity against the dimension
SELECT DISTINCT f.district_sk
FROM fact_crop_cover f
LEFT JOIN dim_district d ON d.district_sk = f.district_sk
WHERE d.district_sk IS NULL;

-- accepted values
SELECT DISTINCT cover_status FROM fact_crop_cover
WHERE cover_status NOT IN ('ACTIVE','LAPSED','CLAIMED','CANCELLED');

-- range sanity
SELECT policy_no, sum_insured FROM fact_crop_cover
WHERE sum_insured <= 0 OR sum_insured > 10000000;

Six categories cover most of what goes wrong: uniqueness on the grain, not-null on required columns, referential integrity to dimensions, accepted values on categoricals, numeric and date ranges, and volume reasonableness compared with recent loads. The last one catches the failure the others miss entirely, which is a load that ran, passed every row-level check, and produced four thousand rows where yesterday produced nine lakh.

SELECT COUNT(*) AS today,
       (SELECT AVG(row_count) FROM etl_load_log
        WHERE table_name = 'fact_crop_cover'
          AND loaded_on >= CURRENT_DATE - 14) AS recent_avg
FROM fact_crop_cover WHERE loaded_on = CURRENT_DATE;

Severity has to be decided per check, not globally. A duplicate on the primary grain should stop the pipeline, because publishing it corrupts every downstream aggregate. A handful of rows with an unexpected category is usually a warning that gets logged and investigated. Treating everything as fatal leads to someone disabling the checks during an incident, and they rarely get re-enabled.

Placement matters as much as content. Run the checks against the staging table before the swap into the published table. Checks that run after publication tell you that bad data reached the dashboard, which is a monitoring system, not a quality gate.

One thing candidates rarely mention and interviewers appreciate: test the tests. Insert a deliberately bad row into a copy and confirm the check catches it. A check with a typo in the WHERE clause returns zero rows forever and looks exactly like a passing check.

A query-based incremental load asks the source what changed and depends on the source telling the truth. CDC reads the write-ahead log directly, which is the record the database keeps for its own recovery, so it sees every committed change including the ones nobody stamped a timestamp on.

What arrives is a stream of events, roughly:

Landing it usually happens in two layers. Raw events append into a log table exactly as received, immutable, partitioned by ingestion date. A second transform reduces that log to current state, taking the last event per primary key by log position and applying deletes:

WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY payload_id ORDER BY lsn DESC) AS rn
  FROM raw_cdc_payments
  WHERE ingested_on = DATE '2026-08-19'
)
SELECT payload_id, amount, status
FROM latest
WHERE rn = 1 AND op <> 'd';

Order by the log position, not by source_ts. Two changes inside the same millisecond, or a clock adjustment on the source, will scramble a timestamp ordering and leave you with an older version as the winner.

Three things worth being able to say.

Deletes are the reason to reach for CDC at all. Everything else is achievable with a watermark; hard deletes are not.

The initial snapshot is a separate problem. CDC only knows about changes after it was switched on, so a backfill of existing rows has to be reconciled with the stream carefully, otherwise a row modified during the snapshot lands twice in the wrong order. Debezium and similar tools handle this explicitly, and it is worth naming that you know it needs handling.

Delivery is at-least-once. Connector restarts replay from the last committed offset, so duplicate events are normal and the downstream merge has to tolerate them.

Schema evolution is the operational headache. A new column in the source appears mid-stream, and a pipeline with a fixed target schema either drops it or fails. Landing raw payloads as JSON and projecting columns in the transform keeps that from being an outage.

The first decision is which date the fact belongs to. If you partition by the date the row was loaded, a shipment that happened on the 15th and arrived on the 19th lands in the 19th, and your daily volumes are permanently wrong in both directions. Partition by event date, and late data means rewriting an old partition rather than corrupting a new one.

Rewriting is only safe if the transform is idempotent, which is the connection interviewers are usually probing. Given a delete-and-reinsert-by-partition transform, handling lateness is a loop:

SELECT DISTINCT shipment_date
FROM stg_shipment_events
WHERE ingested_on = DATE '2026-08-19'
  AND shipment_date < DATE '2026-08-19';

That gives the list of partitions the new batch touches. Rerun the transform for each of them and the published tables converge.

Then set a restatement window, and set it from evidence:

SELECT DATE_PART('day', ingested_at - event_at) AS lag_days, COUNT(*)
FROM stg_shipment_events
WHERE ingested_at >= CURRENT_DATE - 90
GROUP BY 1 ORDER BY 1;

If 99.7% of rows arrive within three days, a rolling seven-day reprocess covers you cheaply. Reprocessing all history nightly is correct and does not scale; reprocessing only today is cheap and wrong. The distribution tells you where to sit between them.

Two consequences that need to be agreed with the business rather than solved in SQL. Published numbers will change after publication, so a figure quoted on Tuesday may differ on Friday. Either mark recent days as provisional, or freeze them at a cut-off and route anything later into an adjustment record. Both are defensible; silently changing history is not.

The other case is a fact arriving before its dimension row, common when an order lands ahead of the customer record. Dropping it loses data and failing the load stops the pipeline. The standard answer is an inferred member: insert a placeholder dimension row keyed on the natural key with unknown attributes, let the fact join to it, and let the real dimension load update the attributes later. The fact keeps its surrogate key throughout.

At-least-once delivery means retries are normal, not exceptional, and a consumer restart can replay a whole offset range. Design for repeats rather than treating each one as an incident.

Everything depends on having a stable identifier assigned at the source, before the message ever hits the queue:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at) AS rn
  FROM raw_wallet_events
  WHERE ingested_on BETWEEN DATE '2026-08-16' AND DATE '2026-08-19'
)
SELECT * FROM ranked WHERE rn = 1;

If the producer does not emit an ID, a deterministic hash over the immutable business columns is the fallback, and it is genuinely worse. Two legitimate events can be identical: the same wallet, the same ₹500 top-up, the same second, from a customer who tapped twice. Hashing collapses them and you have deleted real money. Say that out loud, because it distinguishes a considered answer from a recited one.

The window is the second design decision. Deduplicating across all history requires scanning all history on every load, which does not hold up. A bounded window is what makes it cheap, and it means a duplicate arriving outside that window gets through. Size the window from the observed distribution of retry delays, and if exactness matters more than cost, back it with a unique constraint on event_id in the target so a late duplicate fails to insert instead of doubling a balance.

Where to dedupe is a real trade-off. Deduplicating on write keeps the served tables clean and makes every reader cheap, but pushes cost and latency into the load. Deduplicating at read time via a view keeps the raw log untouched, which is valuable for auditing, and makes every query pay. For a wallet ledger where correctness of the served table is the point, write-time wins.

One detail that catches people: when the two copies differ, ordering by ingested_at alone is arbitrary if both landed in the same batch. Add a tiebreaker such as the source offset, so reruns pick the same winner. Non-deterministic dedup produces a pipeline that disagrees with itself between runs, which is far harder to debug than a duplicate.

Check the version before answering, because this is one of the areas where the honest answer changed.

ALTER TABLE library_holds ADD COLUMN pickup_branch INT is metadata-only on PostgreSQL 11 and later and on MySQL 8.0 with ALGORITHM=INSTANT. It does not rewrite the table and finishes in milliseconds regardless of size. On PostgreSQL 10 and earlier, adding a column with a default rewrote the entire table, which is where the folklore about avoiding defaults comes from. Adding a nullable column with no default was always cheap.

The remaining risk on a modern version is the lock. ALTER TABLE needs a brief ACCESS EXCLUSIVE lock, and if a long-running query or an idle transaction holds the table, your ALTER queues behind it and every subsequent query queues behind the ALTER. A metadata-only change turns into a five-minute outage without ever touching a row. Set lock_timeout to a couple of seconds and retry, so a blocked migration fails fast instead of blocking the application.

The backfill is the expensive part and belongs in its own step, in batches:

UPDATE library_holds
SET pickup_branch = 4
WHERE pickup_branch IS NULL
  AND hold_id BETWEEN 12000000 AND 12100000;

Loop over ranges with a pause between batches. One giant UPDATE holds a transaction open for hours, bloats the table with dead tuples in PostgreSQL, and can stall replication badly enough to affect read replicas.

NOT NULL from the start is the case to flag. On PostgreSQL, adding NOT NULL with a default is still instant on 11+, but converting an existing nullable column to NOT NULL requires a full scan to validate. The safe sequence is: add nullable, backfill in batches, add a CHECK constraint as NOT VALID, validate it separately, then promote.

Sequencing with the application is the part that gets skipped. Deploy the column first, then code that writes it, then the backfill, then code that reads it. Each step is independently reversible. Deploying the column and the dependent code together means a rollback of one leaves the other broken.

For MySQL where an instant algorithm is not available, pt-online-schema-change or gh-ost build a shadow table and swap it, which is standard practice and worth naming.

The two solve different shapes of problem, and choosing wrongly wastes weeks.

An index is for selectivity. Find the fourteen crop insurance policies belonging to one farmer out of nine crore. The index navigates to those rows directly.

Partitioning is for elimination. Aggregate every policy issued in the current kharif season out of nine crore spanning eleven years. No index helps much here, because the query legitimately needs most of the rows in that range, and an index scan fetching forty percent of a table is slower than reading it sequentially. Partitioning removes the other ten years from consideration entirely.

They compose. A partitioned table still carries indexes, created per partition, and the ideal plan prunes to one partition and then uses that partition’s index. Local indexes are also cheaper to rebuild, since you work on one month rather than eleven years.

Where partitioning makes things worse, and this does happen: when the workload does not filter on the partition key. A query filtering only on farmer_id against a date-partitioned table must visit every partition and use every partition’s index, which is more work than one index on one table. Partition count multiplies planning time too, and a table split into two thousand partitions can spend more time planning than executing on a short query.

The honest answer to most interview versions of this question is that indexing comes first. Partition when the table is genuinely large, when queries have a consistent range predicate, or when you need to drop data in bulk. Partitioning a fifty lakh row table because it sounds like an optimisation adds operational complexity and buys nothing.

In a row store, the whole record sits together, so reading one column means reading all forty. Columnar flips it: each column is stored separately, and a query touching four columns reads four of them. On a table of a hundred columns and eight hundred crore rows, that is not an optimisation, it is the difference between minutes and hours.

Three habits follow directly.

Name your columns. SELECT * on a columnar table reads every column file and discards most of them, which is exactly the pattern the format exists to avoid. On a row store the same query is barely more expensive than selecting three columns. This is the single most common carry-over mistake from OLTP work.

Filter on columns that are sorted or clustered. Columnar formats store min and max statistics per block, so a predicate on a well-ordered column lets the engine skip whole blocks without reading them. Filter on a column with values scattered randomly and no block can be skipped. Ordering data on load by the column you filter on is often the highest-value tuning available.

Avoid row-at-a-time modification. Updating one telemetry record means rewriting the affected blocks across every column. Columnar engines handle this with delete markers and background compaction, but the pattern remains expensive, and a pipeline issuing thousands of single-row updates will crawl. Batch the writes and prefer append or partition-rewrite over targeted updates.

Compression is a consequence worth knowing. Neighbouring values in one column are similar in type and often in value, so run-length and dictionary encoding work far better than they can on interleaved rows. Less data on disk means less to read, which compounds the scan advantage.

Traditional B-tree indexes are usually absent or of limited use here. What the engine offers instead is clustering, sort keys, zone maps and partitioning. Answering “I would add an index” to a performance question on Redshift, BigQuery or a Parquet-backed lake signals that the storage model has not landed.

The classic shape, in a cooperative dairy’s settlement job:

Neither can release what it holds until it gets what it wants. The engine’s deadlock detector finds the cycle, picks a victim, and rolls it back with an error. The other proceeds.

The single most effective fix is ordering. If every transaction touches routes in ascending route ID, a cycle cannot form, because whoever holds the lower ID always proceeds first. Applied consistently across the codebase this eliminates the whole class of problem. Adding ORDER BY to the driving SELECT of an update loop is often the entire change.

Four other levers, roughly in order of how often they help.

Keep transactions short. Locks are held until commit, so a transaction that updates a row and then makes an API call before committing holds that lock for the duration of the network round trip. Do the external work outside the transaction.

Index the columns in your WHERE clauses. Without an index, InnoDB may lock far more rows than the ones you meant to update, and two statements that were logically disjoint start conflicting. This is why two transactions can deadlock on a single table while apparently touching different rows.

Take the lock you will eventually need up front, with SELECT ... FOR UPDATE, rather than reading first and upgrading later. Lock upgrades from shared to exclusive are a common cycle source.

Batch smaller. A statement updating four lakh rows holds a great many locks for a long time.

Retry regardless. Deadlocks are a normal outcome under concurrency, not a bug to be eliminated absolutely. Catch the specific error code, wait a short randomised interval, and retry the whole transaction. Code that treats it as fatal will page someone at 2 AM for a condition the database handled correctly.

A deadlock is not the same as a lock wait timeout. A deadlock is a detected cycle and the engine acts immediately; a timeout is one transaction giving up after waiting on a lock nobody is going to release soon. They present similarly in logs and have different causes.

For diagnosis, SHOW ENGINE INNODB STATUS in MySQL prints the most recent deadlock with both transactions’ statements. PostgreSQL logs the full detail including the queries involved, and setting log_lock_waits catches the near misses before they become cycles.