# Machine Learning Engineer Interview Questions — SQL Source: Codeayan (https://codeayan.com) Canonical: https://codeayan.com/get-hired/machine-learning-engineer/sql Questions: 38 Last updated: 2026-08-20 Licence: free to read. Please cite Codeayan when quoting. --- ## 1. What's the difference between WHERE and HAVING, and how do you decide which one a condition belongs in? *Easy · Very Common* **Short answer.** WHERE runs before rows are grouped, so it filters raw rows and cannot see aggregates. HAVING runs after GROUP BY has built the groups, so it filters those groups and can reference COUNT, SUM or AVG. Put a condition in WHERE whenever you can, so fewer rows reach the grouping step. Both clauses filter. The difference is what they are looking at, and when they get to look. Take an `orders` table from a food delivery app. You want restaurants that received more than 50 delivered orders in July. ``` SELECT restaurant_id, COUNT(*) AS delivered_orders FROM orders WHERE order_status = 'DELIVERED' AND placed_at >= '2026-07-01' AND placed_at < '2026-08-01' GROUP BY restaurant_id HAVING COUNT(*) > 50; ``` The status and date conditions describe a single order, so they belong in WHERE. Every row WHERE discards is a row the grouping step never has to process. The count condition cannot sit there at all: at the moment WHERE runs, no groups exist yet, so `COUNT(*)` has nothing to count. The mistake interviewers watch for is the reverse move — dragging a row-level condition into HAVING because it appears to work. ``` GROUP BY restaurant_id HAVING COUNT(*) > 50 AND order_status = 'DELIVERED' ``` PostgreSQL and SQL Server reject this outright, since `order_status` is neither grouped nor aggregated. MySQL running without `ONLY_FULL_GROUP_BY` will execute it and take the status from an arbitrary row in each group. No error, wrong numbers, and a report that nobody questions. One detail worth carrying into the room: HAVING is legal without GROUP BY. The whole result set becomes a single group, so `SELECT SUM(order_value) FROM orders HAVING SUM(order_value) > 500000` returns either one row or none at all. **Likely follow-ups** - If I move the date filter from WHERE into HAVING, does the answer change or just the speed? - Can you filter on an aggregate without writing GROUP BY at all? - Where does a window function get evaluated relative to these two? --- ## 2. Walk me through the order a SQL query is actually evaluated in. And why can't I use a column alias I defined in SELECT inside my WHERE clause? *Medium · Very Common* **Short answer.** SQL evaluates FROM and JOIN first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. A SELECT alias fails in WHERE because WHERE runs before SELECT, so the name does not exist yet. ORDER BY accepts the alias because it runs last. 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: - **FROM / JOIN** — assemble the working row set - **WHERE** — filter individual rows - **GROUP BY** — collapse rows into groups - **HAVING** — filter groups - **SELECT** — evaluate expressions and assign aliases - **DISTINCT** - **ORDER BY** — sort - **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. **Likely follow-ups** - If ORDER BY runs before LIMIT, what does a LIMIT with no ORDER BY actually give you? - Where would a window function fit into that sequence? - Does the database physically execute in this order, or is this just semantics? --- ## 3. Why does `WHERE settled_on = NULL` return zero rows when the column obviously contains NULLs? How should you test for one? *Easy · Very Common* **Short answer.** NULL means unknown, so any comparison with it evaluates to unknown rather than true or false, and WHERE keeps only rows that are true. Use IS NULL and IS NOT NULL instead. Watch out for NOT IN with a NULL in the list, which returns nothing at all. 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. **Likely follow-ups** - What happens to a NOT IN subquery when one of the returned values is NULL? - Two rows both have NULL in the same column — does GROUP BY put them together? - How would you compare two nullable columns and treat NULL as equal to NULL? --- ## 4. How do you write conditional logic with CASE WHEN? Show me how you'd use it inside an aggregate to get several counts from one pass over the table. *Easy · Very Common* **Short answer.** CASE WHEN evaluates conditions in order and returns the first match, otherwise the ELSE value or NULL. Wrapping it inside SUM or COUNT gives conditional aggregation: one scan produces several segment totals as separate columns, instead of running one query per segment or pivoting afterwards. 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. **Likely follow-ups** - What does COUNT(CASE WHEN ... THEN 1 ELSE 0 END) return, and is that what you intended? - If two of your CASE conditions overlap, which one wins? - How would you get these same buckets as rows instead of columns? --- ## 5. Explain INNER, LEFT, RIGHT and FULL OUTER JOIN. When have you actually needed a FULL OUTER? *Easy · Very Common* **Short answer.** INNER keeps only rows that match on both sides. LEFT keeps every left row, padding the right side with NULLs when nothing matches. RIGHT does the mirror image. FULL OUTER keeps unmatched rows from both sides. Choose by which side you cannot afford to lose. 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. **Likely follow-ups** - How would you write a FULL OUTER JOIN on a database that doesn't support one? - Which join would you use to find flights that received no bookings at all? - If I swap the two tables around, is a RIGHT JOIN the same as a LEFT JOIN? --- ## 6. I wrote a LEFT JOIN, added a condition on the right-hand table in WHERE, and half my rows disappeared. What happened? *Medium · Very Common* **Short answer.** Filtering the right table in WHERE removes the NULL-padded rows the LEFT JOIN created, leaving only matched rows, which is an INNER JOIN with extra steps. Move the condition into the ON clause if it should restrict what matches, or test IS NULL if you want unmatched rows. 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. **Likely follow-ups** - So is there any condition on the right table that is safe to put in WHERE? - Does moving a condition from WHERE to ON change anything for an INNER JOIN? - How would you keep every order but show the reason only when the return was marked damaged? --- ## 7. You join two tables and get back more rows than the left table started with. Why does that happen, and how do you fix it? *Medium · Very Common* **Short answer.** A join matches each left row against every matching right row, so a one-to-many relationship multiplies rows. Two such joins multiply against each other. Sums and counts inflate silently. Fix it by aggregating the many side in a subquery first, or by joining on a key that is genuinely unique. 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. **Likely follow-ups** - Would SUM(DISTINCT amount) fix the inflated total? - How would you check, before writing the join, whether the relationship is one-to-one? - Is there a case where a join multiplying rows is exactly what you want? --- ## 8. COUNT(*), COUNT(column), COUNT(DISTINCT column) — what does each one give you, and when do they disagree? *Easy · Very Common* **Short answer.** COUNT(*) counts rows. COUNT(col) counts rows where that column is not NULL. COUNT(DISTINCT col) counts unique non-NULL values. On a table with no NULLs the first two agree, which is why the difference only surfaces after a LEFT JOIN or on a sparse column. COUNT(1) is the same as COUNT(*), not a NULL question. 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. **Likely follow-ups** - Which of those three would tell me how many listeners a track had, as opposed to how many plays? - Is COUNT(*) slower than COUNT(id) on a large table? - What does COUNT(DISTINCT a, b) mean, and does your database support it? --- ## 9. If I write GROUP BY market, crop, what exactly is a group? Is it crops within markets, or something else? *Easy · Very Common* **Short answer.** A group is one row per unique combination of all the grouped columns, not a nesting of one inside another. GROUP BY market, crop produces a row for every pair that exists in the data. Adding a column always increases the group count, or leaves it unchanged, never reduces it. 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. **Likely follow-ups** - Does swapping the two columns in the GROUP BY change the result? - I want the per-market total on the same rows as the per-crop detail — how do you get both? - A market traded nothing this week; does it appear in your output? --- ## 10. When do you reach for a CTE instead of a subquery? Does the choice affect the execution plan? *Medium · Very Common* **Short answer.** A CTE names a query block up front so it reads top to bottom and can be referenced more than once; a subquery nests inline. Modern PostgreSQL and MySQL usually inline a CTE and produce the same plan, so treat the choice as readability first, not performance. 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. **Likely follow-ups** - If a CTE is referenced three times, is the underlying query run three times? - What can a recursive CTE do that a subquery cannot? - Would you use a CTE inside a view that gets queried hundreds of times a day? --- ## 11. IN or EXISTS for a membership check? And is there a case where they give different answers? *Medium · Very Common* **Short answer.** IN compares a value against a list; EXISTS checks whether a correlated subquery returns any row at all. Optimisers usually treat them alike for positive checks. The difference that matters is NOT IN: one NULL in the subquery result makes it return nothing, while NOT EXISTS stays correct. 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. **Likely follow-ups** - How would you rewrite a broken NOT IN without changing what the query means? - If the subquery returns ten million rows, does that change your choice? - What does IN do when the subquery returns no rows at all? --- ## 12. I want each transaction row alongside the total for its category. GROUP BY collapses my rows. What do I do? *Medium · Very Common* **Short answer.** GROUP BY collapses each group to one row. A window function computes across a set of rows while leaving every row intact, so SUM(...) OVER (PARTITION BY category) attaches the category total to each transaction. Same arithmetic, different output shape. 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. **Likely follow-ups** - Can you put a window function in the WHERE clause? - If I add a WHERE to that query, does the window see the filtered rows or all of them? - What does a window function with an empty OVER() give you? --- ## 13. ROW_NUMBER, RANK and DENSE_RANK — what's the difference, and which do you pick when there are ties? *Easy · Very Common* **Short answer.** ROW_NUMBER always gives distinct sequential numbers, breaking ties arbitrarily. RANK gives tied rows the same number then skips ahead, so 1,2,2,4. DENSE_RANK gives the same number without skipping, so 1,2,2,3. Pick by whether ties should share a position and whether the next position should jump. Take five cricket batters ordered by runs scored in a tournament, with two of them level on 412: Player Runs ROW_NUMBER RANK DENSE_RANK Iyer 508 1 1 1 Naik 412 2 2 2 Rathore 412 3 2 2 Bose 389 4 4 3 Kamat 350 5 5 4 ``` 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. **Likely follow-ups** - Two rows tie and you used ROW_NUMBER — which one gets 1? - Is ORDER BY inside OVER optional for these? - How would you rank within each region rather than across the whole table? --- ## 14. Find the third highest ticket price in the table. Now tell me what your query does if two tickets share the top price. *Medium · Very Common* **Short answer.** Ask first whether ties count as one position or several. DENSE_RANK = 3 gives the third distinct value. ROW_NUMBER or OFFSET 2 gives the third row, which is a different answer when the top price is duplicated. Both return nothing if fewer than three qualifying values exist. 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. **Likely follow-ups** - What should the query return if there are only two distinct prices? - Would your answer change if I asked for the third highest per venue? - Can you do it without any window function at all? --- ## 15. PARTITION BY and GROUP BY both split data into groups. What's the actual difference? *Medium · Very Common* **Short answer.** GROUP BY splits rows into groups and returns one row per group, discarding the detail. PARTITION BY splits rows into windows for a calculation but returns every input row. Row count is the giveaway: GROUP BY reduces it, PARTITION BY never does. 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. **Likely follow-ups** - Can you use both in the same query, and what would that mean? - If I leave PARTITION BY out entirely, what is the partition? - Which one can I filter on directly, and which needs an outer query? --- ## 16. How would you show each month's premium collected next to the previous month's, with the percentage change? *Medium · Very Common* **Short answer.** LAG pulls a value from an earlier row in the same window, LEAD from a later one, both governed by the ORDER BY inside OVER. The first row of each partition has no previous row, so LAG returns NULL there unless you supply a default. ``` 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. **Likely follow-ups** - A month with no policies sold is missing from the table — what does your LAG return for the month after it? - How do you avoid a divide-by-zero on the percentage? - What would you use to compare against the same month last year instead? --- ## 17. UNION or UNION ALL? One of them is the default people reach for, and it's the wrong default. Why? *Easy · Very Common* **Short answer.** UNION removes duplicate rows across the combined result, which means a sort or hash over everything. UNION ALL concatenates and does no deduplication work. UNION is the more expensive one, and people default to it while usually meaning UNION ALL. 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. **Likely follow-ups** - If I know the two sets can't overlap, is there any reason left to use UNION? - Where does an ORDER BY go when you're combining three queries? - What happens if the two branches have the same number of columns but different types? --- ## 18. Extract me a labelled training set for a churn model. Walk me through the time boundaries you set and why. *Hard · Common* **Short answer.** Fix an observation date, compute every feature strictly before it, and define the label from a window strictly after it. Rows whose label window has not fully elapsed must be excluded, or recent periods get labelled negative simply because the outcome has not happened yet. Three timestamps define the extraction, and naming them before writing SQL is most of the work. The observation date is the moment the model would make a prediction. Everything before it is feature territory. The label window is a fixed span after it, and the label is whatever happened inside that span. For a fitness-app subscription churn model with a 30-day horizon: ``` WITH cfg AS (SELECT DATE '2026-05-01' AS as_of, 30 AS horizon_days), population AS ( SELECT s.subscriber_id FROM app_subscriptions s, cfg WHERE s.started_on cfg.as_of) ), labels AS ( SELECT p.subscriber_id, CASE WHEN s.cancelled_on IS NOT NULL AND s.cancelled_on > cfg.as_of AND s.cancelled_on **Likely follow-ups** - A subscriber joined four days before your observation date — do you keep that row? - How would you produce a hundred observation dates instead of one? - What would you store so someone can regenerate this exact table next year? --- ## 19. This training query joins the customer table to get a status column and validation AUC jumped to 0.97. What happened? *Hard · Common* **Short answer.** The joined column reflects state after the outcome occurred. Dimension tables hold current values, so joining one to a historical label attaches tomorrow's information to yesterday's row. Suspiciously high validation performance is the symptom, and a feature that could not have been known at prediction time is the cause. ``` SELECT l.merchant_id, l.label_date, l.defaulted_on_settlement, m.account_status, m.risk_tier FROM settlement_labels l JOIN merchant_master m ON m.merchant_id = l.merchant_id; ``` `merchant_master` holds current state. For a label dated nine months ago, `account_status` is what the status is today, and a merchant who defaulted was moved to `SUSPENDED` shortly afterwards. The model learns that suspended merchants defaulted, which is true, useless, and unavailable at prediction time. AUC of 0.97 on a risk problem is not a triumph; it is a symptom. Three shapes this takes, and all three come from the same root. Current-state dimension joined to a historical row, as above. The fix is a point-in-time join against a history table carrying validity dates. A field populated by the process that resolves the outcome. `settlement_hold_reason`, `recovery_agent_assigned`, `days_past_due_final`. These are filled in after the fact, and they will not exist at scoring time. A timestamp that encodes the outcome. `last_updated_at` on the merchant record often sits within hours of the default, so even without reading the status the model can infer it from a date column that looks harmless. The audit that catches these systematically is not statistical, it is procedural. For each feature, ask one question: at the moment the prediction would be made, was this value already written and would it have had this value? Anything you cannot answer with a confident yes goes on a list. Then check the columns your gradient-boosted model ranks highest, since leakage nearly always dominates importance. An automated check worth building: for each candidate feature, compute the correlation between the feature and the label, and separately check whether the feature’s source row was written after the label date. A field whose write timestamp postdates the label is leaked by construction. If dropping the leaked column takes AUC from 0.97 to 0.61, that is your real model. Painful and correct. A 0.61 model deployed beats a 0.97 model that collapses on the first day in production and destroys the team’s credibility with the risk function. **Likely follow-ups** - How would you audit twenty features for this rather than finding it by accident? - The column is genuinely available at prediction time but only for some customers — is that safe? - What would you do if removing the leaked feature drops AUC to 0.61? --- ## 20. Your rolling feature uses SUM OVER with an ORDER BY on the timestamp. Show me how that leaks. *Hard · Common* **Short answer.** Two ways: an unbounded frame that includes following rows, and a frame ending at CURRENT ROW when the label is derived from that same row. Both let the feature see the event it is meant to predict. Bound the frame explicitly and end it strictly before the observation timestamp. The obvious leak is a frame that extends forward: ``` AVG(fare_paid) OVER (PARTITION BY rider_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ``` That averages the rider’s entire history, including trips taken after the observation date. Nobody writes it deliberately. It appears when someone adds an unbounded frame to make `LAST_VALUE` behave, then reuses the same OVER clause elsewhere by copy-paste. The subtle leak is the one that survives review: ``` SUM(complaint_count) OVER (PARTITION BY rider_id ORDER BY event_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS complaints_30d ``` Nothing here reads the future. But if the label is “did this rider file a complaint on this date”, the current row is the label event, and the feature has just counted it. The model discovers a feature that is perfectly predictive and worthless. Ending the frame one row early fixes it: ``` ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING ``` A third trap sits in the default. Writing an ORDER BY with no frame applies `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, and RANGE works on values rather than positions. Every row sharing the current timestamp is treated as part of the current row, so a batch of trips loaded with the same truncated timestamp all see each other. On event data with second-level or day-level granularity that is a real leak, and it is invisible in the query text because the frame is not written down. Always write the frame. The audit habit worth carrying: for every window function in a feature query, read the frame out loud and ask what the latest row it can touch is, then compare that against the observation timestamp. If the answer is “the current row” and the label derives from the current row, it leaks. A self-join version has exactly the same exposure. The join predicate is the frame, and `e.event_date <= f.observation_date` includes the observation day itself. `< f.observation_date` does not. The mechanism differs; the discipline is identical. **Likely follow-ups** - What does the default frame do when several events share the same timestamp? - How would you write a trailing 30-day sum that ends the day before the observation? - Would a self-join version of the same feature have the same problem? --- ## 21. How do COUNT, SUM and AVG each behave when the column has NULLs in it? Where does that difference bite you in a real report? *Medium · Common* **Short answer.** Aggregates skip NULLs. COUNT(*) counts rows, COUNT(col) counts non-NULL values only. SUM and AVG ignore NULLs entirely, so AVG divides by the number of non-NULL values, not the row count. On an empty set SUM and AVG return NULL while COUNT returns zero. 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; ``` Function NULL handling Denominator for AVG `COUNT(*)` counts every row not applicable `COUNT(col)` counts non-NULL values only not applicable `SUM(col)` adds non-NULL values only not applicable `AVG(col)` ignores NULL rows completely count of non-NULL values 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. **Likely follow-ups** - A month has no disbursals at all — what does SUM return, and what does the dashboard show? - When would you deliberately convert NULL to zero before averaging? - Does COUNT(DISTINCT column) include the NULLs? --- ## 22. Find me the rows that exist in table A but not in table B. Give me more than one way to write it, and tell me which one you'd actually ship. *Medium · Common* **Short answer.** Three ways: LEFT JOIN with IS NULL on the join key, NOT EXISTS, and NOT IN. The first two are safe. NOT IN breaks the moment the subquery returns a NULL, silently returning zero rows. NOT EXISTS is the default choice, correct with NULLs and usually the clearest to read. 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. **Likely follow-ups** - If the subquery column is declared NOT NULL, is NOT IN safe then? - How does the anti-join behave if the right table has duplicate keys? - What would you change if the right table had 200 million rows? --- ## 23. Show me how you'd run a query with a user-supplied filter from Python. Where does this go wrong? *Medium · Common* **Short answer.** Pass values as bound parameters, never as formatted strings. The driver sends the query and the values separately, so user input can never be parsed as SQL. String interpolation with f-strings or concatenation is how injection happens, and it also breaks on quotes in ordinary data. The dangerous version and the safe version differ by one character: ``` # unsafe cur.execute(f"SELECT * FROM tuition_leads WHERE city = '{city}'") # safe cur.execute("SELECT * FROM tuition_leads WHERE city = %s", (city,)) ``` The second form sends the statement and the value over separate channels. The database compiles the query first, then binds the value, so nothing inside `city` can ever become executable SQL. Injection is the headline risk and not the only one. A perfectly innocent name containing an apostrophe breaks the interpolated query with a syntax error, so the same bug that lets an attacker in also makes your pipeline fail on real data. Parameter binding also lets the database reuse a prepared plan across calls, which matters in a scoring loop. Placeholder style varies by driver: `%s` for psycopg2 and mysql-connector, `?` for sqlite3, and named `:param` styles in SQLAlchemy. The placeholder is never quoted, even for strings. Writing `'%s'` reintroduces the problem. Two cases parameters cannot cover. Identifiers. Table and column names are not values, so binding does not work. Validate against an allowlist you control, or use the driver’s identifier-quoting helper, such as `psycopg2.sql.Identifier`. Never format a raw string into that position. Variable-length IN lists. Generate the right number of placeholders and pass a matching tuple, or on PostgreSQL use `WHERE id = ANY(%s)` with a Python list, which handles any length with one parameter. An ORM protects the parameterised paths and does not protect raw fragments. `session.execute(text(f"... {user_input}"))` is exactly as exposed as the psycopg2 version. The safety comes from binding, not from the library. One practical habit for ML pipelines: keep the SQL in a file or a constant with named parameters, and pass the run date, model version and thresholds as parameters. Interpolating a date into the query text is the same anti-pattern and it also destroys reproducibility, since the query text and the data it produced no longer travel together. **Likely follow-ups** - You need the table name itself to come from a variable — how do you handle that safely? - Does an ORM make you immune to this? - What would you do about a query that has to accept a variable-length list of IDs? --- ## 24. Your team's warehouse bill tripled this month. How would you find the cause and bring it down? *Medium · Common* **Short answer.** Find the expensive queries first from the platform's own query history, then attack the two drivers: bytes scanned and compute time. Partition filters, selecting fewer columns, and materialising repeated aggregations do most of the work. Set quotas and alerts so a runaway query cannot run all month. Start with evidence rather than intuition. BigQuery exposes `INFORMATION_SCHEMA.JOBS_BY_PROJECT` with bytes billed per query; Snowflake has `QUERY_HISTORY` and `WAREHOUSE_METERING_HISTORY` with credits consumed. ``` SELECT user_email, ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 2) AS tib_billed, COUNT(*) AS queries FROM `region-asia-south1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY user_email ORDER BY tib_billed DESC LIMIT 20; ``` Cost is almost always concentrated. A handful of queries, or one scheduled job running far more often than anyone realised, usually accounts for most of the increase. The two levers differ by platform. BigQuery bills on bytes scanned, so the fixes are structural: partition on the date column and always filter on it, cluster on the columns you filter by, and stop selecting columns you do not use. On a columnar store `SELECT *` reads every column file, and an ML feature query touching 8 of 90 columns pays for 90 unless you name them. Snowflake bills on warehouse time, so the fixes are operational: right-size the warehouse, set auto-suspend to a minute or two, and separate workloads so a heavy training extract does not force the dashboard warehouse to a larger size. Result caching is free on both platforms and is defeated by `CURRENT_TIMESTAMP()` in the query text, which is a common accidental cost. Two patterns specific to ML work. A feature query rerun for every experiment should be materialised once into a table and read many times. And exploratory work on a full table should sample first, since a hash-based subset gives the same intuition at a fraction of the scan. Prevention is the part interviewers listen for. Set per-user and per-project quotas, enable a maximum bytes-billed setting on ad-hoc queries so a mistake fails instead of running, and put a weekly cost-by-user query on a dashboard. Discovering a runaway query on the invoice is discovering it three weeks late. **Likely follow-ups** - One dashboard refreshes every five minutes — how would you quantify what that costs? - What would you set up so this cannot happen again silently? - Does clustering help with cost or only with speed? --- ## 25. Explain what dbt actually does to a SQL file. What are ref and materialisation doing? *Medium · Common* **Short answer.** A dbt model is a SELECT statement in a file. dbt wraps it in the DDL implied by its materialisation, and `ref()` replaces a model name with its real relation while recording a dependency. Those dependencies build a graph that determines run order. You write only the SELECT: ``` -- models/marts/fct_orphanage_donations.sql {{ config(materialized='incremental', unique_key='donation_id') }} SELECT d.donation_id, d.donor_id, d.received_on, d.amount, c.campaign_name FROM {{ ref('stg_donations') }} d LEFT JOIN {{ ref('dim_campaigns') }} c ON c.campaign_id = d.campaign_id {% if is_incremental() %} WHERE d.received_on > (SELECT MAX(received_on) FROM {{ this }}) {% endif %} ``` dbt compiles this into real SQL, substituting each `ref()` with the fully qualified table name for the target environment, and wraps it in whatever DDL the materialisation requires. Nobody writes CREATE TABLE. `ref()` is doing two jobs, and the second is the one that matters. It resolves the name, and it declares an edge in the dependency graph. dbt derives run order from those edges, so `stg_donations` builds before this model without anyone maintaining a schedule. Hardcode the table name instead and the model still compiles, still runs, and will run in the wrong order the first time the upstream changes. Use `source()` for raw tables outside dbt’s control, for the same reason. Four materialisations cover most work. `view` creates a view, cheap to build and paying full cost on every read. `table` rebuilds fully each run, simple and correct and expensive at scale. `incremental` inserts or merges only new rows, which is what a large event fact needs. `ephemeral` inlines the SQL as a CTE into whatever refs it, producing no database object at all. Incremental is where care is required. The `is_incremental()` block is skipped on first build and on `--full-refresh`, so the model must be correct both ways. A watermark of `MAX(received_on)` misses late-arriving rows, so overlap the window and let `unique_key` deduplicate through a merge. Get this wrong and the table quietly loses rows for months. For ML work the payoff is that feature definitions live in version control, with tests attached and lineage visible, so training and downstream consumers read the same table rather than two divergent copies of one definition. **Likely follow-ups** - Two models both select from the same source — does dbt know they're related? - When would you choose incremental over table, and what does that cost you? - What happens on a full refresh of an incremental model? --- ## 26. Write me a running total of daily collections. Then explain what the frame clause is doing, whether you wrote one or not. *Medium · Common* **Short answer.** SUM(amount) OVER (ORDER BY date) gives a running total. Adding ORDER BY silently sets the frame to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps ties together. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want strict row-by-row accumulation. 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. Frame On tied dates Use when `RANGE ... CURRENT ROW` (default with ORDER BY) ties share one value you want the total as at end of each day `ROWS ... CURRENT ROW` ties accumulate individually you want a true row-by-row ledger 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. **Likely follow-ups** - Two donations land on the same date — what does your default frame do to the running total? - How would you change it to a trailing 7-day sum instead? - What resets the total at the start of each campaign? --- ## 27. You have clicks but no non-clicks. How do you build negative examples for a recommender in SQL? *Hard · Occasional* **Short answer.** Generate candidate pairs the user did not interact with, then anti-join against the observed positives to remove real interactions. Sample rather than materialising every pair, since the full cross product is enormous. Negatives drawn from items actually displayed are far more informative than uniformly random ones. Implicit feedback gives you one class. A jewellery marketplace logs which designs a shopper opened and nothing about the thousands they did not, so the model has nothing to contrast against. The uniform-random approach: ``` WITH positives AS ( SELECT DISTINCT shopper_id, design_id FROM design_views WHERE viewed_on >= DATE '2026-06-01' AND viewed_on < DATE '2026-07-01' ), candidates AS ( SELECT p.shopper_id, d.design_id FROM (SELECT DISTINCT shopper_id FROM positives) p CROSS JOIN LATERAL ( SELECT design_id FROM active_designs ORDER BY RANDOM() LIMIT 20 ) d ) SELECT c.shopper_id, c.design_id, 0 AS label FROM candidates c WHERE NOT EXISTS (SELECT 1 FROM positives p WHERE p.shopper_id = c.shopper_id AND p.design_id = c.design_id); ``` The LATERAL with a LIMIT samples per shopper instead of building the full cross product, which for 4 lakh shoppers and 60,000 designs would be 24 billion rows. Never materialise the cross join and then sample; sample inside it. The `NOT EXISTS` is the important half. Without it, some generated negatives are items the shopper actually viewed, and you are training the model that a genuine interest is a non-interest. Check against all observed interactions, not only the ones in your training window, and include purchases and wishlist adds rather than views alone. Three design choices worth raising. **Random versus exposed negatives.** A uniformly random design is one the shopper never saw, so the model partly learns what gets displayed rather than what gets clicked. Negatives sampled from items that were actually shown and not clicked are much harder and much more informative, and they need impression logs. If those logs exist, say you would use them. **Popularity.** Uniform sampling draws mostly from the long tail, so popular items rarely appear as negatives and the model over-predicts them. Sampling proportional to popularity, or a blend, is the usual correction. **Ratio.** Four to ten negatives per positive is a common starting range. The ratio changes the model’s output scale, so predicted probabilities are no longer calibrated to real click rates and need adjusting if anything downstream consumes them as probabilities. One thing an unclicked item is not: a confirmed negative. The shopper may simply never have scrolled that far. **Likely follow-ups** - A sampled negative turns out to be an item the user bought last month — does that matter? - How many negatives per positive would you generate, and what does that ratio change? - What's the difference between random negatives and negatives drawn from what was actually shown? --- ## 28. A model in production is behaving oddly and you need the exact dataset it was trained on. Can you get it back? *Hard · Occasional* **Short answer.** Only if you planned for it. Rerunning the query gives a different result once source data has been corrected, backfilled or overwritten. Persist the extracted dataset as an immutable versioned artefact, hash it, and store the hash with the model. Rerunning the training query six months later is not reproduction. Between then and now, late-arriving telemetry landed, a dimension row was overwritten, and someone corrected a batch of mislabelled records. The query is unchanged and the dataset is not. Three levels of rigour, and knowing which one you have is the honest answer. **Persisted snapshot.** Write the extracted training set to an immutable table or a Parquet file at a versioned path, and never modify it. Compute a hash over the sorted contents and store it in the model registry alongside the query text, the parameter values, the run timestamp, the row count and the label distribution. ``` CREATE TABLE training_sets.crop_yield_v7 AS SELECT ... ; INSERT INTO model_registry (model_id, dataset_table, row_count, positive_rate, extracted_at, query_git_sha) SELECT 'yield-forecast-v7', 'training_sets.crop_yield_v7', COUNT(*), AVG(label::numeric), NOW(), 'a91f3c2' FROM training_sets.crop_yield_v7; ``` **Time-travel.** Warehouses with snapshot isolation over versioned storage, such as Delta, Iceberg or Snowflake, let you query a table as at a past timestamp or version. That reproduces the inputs rather than the output, which is nearly as good and much cheaper in storage. **Nothing.** The source is a mutable table with no history and no snapshot was taken. Then the honest answer is that the dataset cannot be recovered, and you say so rather than rerunning the query and presenting the result as the original. Producing a lookalike dataset and calling it the training data is worse than admitting the gap, because every subsequent debugging conclusion rests on it. Two things the registry entry buys you. When production performance degrades, comparing the current feature distribution against the stored training distribution tells you within minutes whether the input data drifted or the model is genuinely stale. And when a regulator or a customer asks how a decision was made, a hash linking a specific dataset to a specific model version is the difference between a documented answer and a reconstruction. Store the query in version control and record the commit SHA. The table alone does not tell you how it was built. **Likely follow-ups** - The source table is mutable and has no history — what can you actually recover? - How would you prove to an auditor that a given file is the training data for a given model? - What would you change so this is a two-minute job next time? --- ## 29. The same feature is computed in a training query and again in the serving path. Why is that a problem, and what do you do about it? *Hard · Occasional* **Short answer.** Two implementations of one definition drift apart, and the model is then trained on one thing and scored on another. That is training-serving skew, and it degrades production performance silently. Define each feature once, materialise it, and have both paths read from the same computed values. The training query aggregates two years of warehouse data in SQL. The serving path recomputes the same feature in application code against the transactional database, under a latency budget. Both were written from the same description and by different people. They will disagree. The training version uses a 30-day window ending the day before; the serving version uses the last 30 days including today. One excludes reversed transactions, the other does not. One treats a missing value as zero, the other as null. None of these is a bug that any test catches, because each implementation is internally consistent. The result is a model trained on one distribution and scored on another. Offline metrics stay excellent, production performance is worse than expected, and there is nothing in the logs pointing at the cause. The fix is single definition, dual materialisation. The feature is computed once, in SQL, and written to two stores fed from the same computation: ``` -- one definition, run in batch CREATE OR REPLACE TABLE feature_store.dealer_features AS SELECT dealer_id, DATE '2026-08-19' AS feature_date, COUNT(*) FILTER (WHERE order_date >= DATE '2026-08-19' - 30 AND order_date < DATE '2026-08-19') AS orders_30d, SUM(order_value) FILTER (WHERE order_date >= DATE '2026-08-19' - 90 AND order_date < DATE '2026-08-19') AS value_90d FROM tractor_dealer_orders GROUP BY dealer_id; ``` The offline store keeps every historical `feature_date` for point-in-time training joins. The online store keeps only the latest row per dealer, in a key-value system that answers in single-digit milliseconds. The values are identical because they came from one query. Two consequences to be able to discuss. The online store is as fresh as the last batch, so a feature that must reflect the last five minutes cannot be served this way and needs a streaming computation, with the same single-definition discipline applied to the streaming job. And you should monitor agreement rather than assume it. Sample entities daily, compute the feature both ways, and alert on divergence. A mismatch on 2% of dealers usually traces to a boundary condition such as new dealers with no history, and finding it through monitoring beats finding it through a quarter of poor predictions. **Likely follow-ups** - The two implementations agree on average but differ on 2% of entities — how would you find those? - How would you serve a feature that takes forty seconds to compute in the warehouse? - What has to be true for offline and online stores to stay consistent? --- ## 30. You've added a new feature. Now backfill it across three years of historical training rows. What could go wrong? *Hard · Occasional* **Short answer.** Compute the feature as at each historical observation date, not as at today. The failure is using current data for old rows, which leaks and produces a feature that behaves differently in training than in production. Also check the source data actually existed across the whole backfill period. The tempting one-liner is the wrong one: ``` UPDATE training_rows t SET repeat_visit_ratio = (SELECT ... FROM clinic_visits v WHERE v.patient_id = t.patient_id); ``` That computes one value from all available history and writes it onto every historical row, including rows observed in 2023. Every one of those now carries information from 2024 and 2025. The feature is leaked for old rows and correct for new ones, which is worse than uniformly wrong, because the model learns a relationship that only exists in the training set. The backfill has to respect each row’s own observation date: ``` UPDATE training_rows t SET repeat_visit_ratio = f.ratio FROM ( SELECT t2.row_id, COUNT(*) FILTER (WHERE v.visit_date >= t2.as_of - INTERVAL '180 days')::numeric / NULLIF(COUNT(*), 0) AS ratio FROM training_rows t2 JOIN clinic_visits v ON v.patient_id = t2.patient_id AND v.visit_date < t2.as_of GROUP BY t2.row_id ) f WHERE f.row_id = t.row_id; ``` Every aggregation is bounded by that row’s `as_of`. Slower, and the only version that is correct. Three checks before trusting the result. **Source availability.** If the visits table only starts in 2025, rows observed in 2023 cannot have this feature. Filling them with zero tells the model that patients had no prior visits, which is false. Leave them null and let the model handle missingness, or restrict training to the period where the feature exists, and state which you chose. **Definitional consistency.** The backfilled values must match what the daily job will produce tomorrow. Recompute one recent day both ways and compare row by row. A backfill that quietly uses a different window length than the forward job creates a discontinuity at the changeover date, and the model sees a feature that shifts distribution partway through the training period. **Distribution over time.** Plot the mean of the new feature by observation month. A step change usually means an upstream data collection change rather than a real trend, and training across that boundary teaches the model two different things. Retrain only after those checks pass. A backfilled feature that fails any of them will look strong in validation for exactly the wrong reason. **Likely follow-ups** - The source table for this feature only starts in 2025 — what do you do about earlier rows? - How would you verify the backfill matches what the daily job produces going forward? - Would you retrain immediately after the backfill, and why? --- ## 31. The batch job produced scores for eight crore accounts. How do you get those back onto the entity table? *Medium · Occasional* **Short answer.** Write scores to a separate table keyed by entity and scoring date, then join on demand rather than updating the entity table in place. Keep the run identifier and model version on every row, so a bad run can be isolated and the join always picks a known-good version. Do not update the entity table. An in-place update on eight crore rows is expensive, it destroys the previous scores, and a half-completed run leaves a table where some accounts carry today’s model and some carry last week’s, with nothing to distinguish them. Write to a scores table instead: ``` CREATE TABLE model_scores ( account_id BIGINT NOT NULL, scored_on DATE NOT NULL, model_version TEXT NOT NULL, run_id TEXT NOT NULL, score REAL NOT NULL, PRIMARY KEY (account_id, scored_on, model_version) ) PARTITION BY RANGE (scored_on); ``` Partitioning by date makes each run a partition write, which is fast, and makes retention a partition drop rather than a delete over crores of rows. The run identifier lets you isolate and remove one bad run without touching anything else. Loading follows the delete-then-insert pattern on the partition, inside a transaction, so a retry produces the same table rather than doubling it. Bulk loading beats row-by-row inserts by orders of magnitude here: stage the scores as a file, copy them in, then index. Reading the current score is a join to the latest scored date: ``` SELECT a.account_id, a.segment, s.score FROM credit_accounts a LEFT JOIN model_scores s ON s.account_id = a.account_id AND s.scored_on = DATE '2026-08-19' AND s.model_version = 'v4'; ``` LEFT JOIN, deliberately. An account the run missed comes back with a null score, which is visible and correct. An inner join drops it, and the consumer silently receives a smaller population than the one it asked about. Count the nulls as a run health check before publishing. Two operational points worth raising. Serving from a warehouse table is fine for another batch process and hopeless for a real-time API, so push the latest scores into a key-value store if anything needs a fast lookup. And keeping history rather than overwriting is what makes score drift analysis possible: comparing this week’s distribution to last week’s is the first thing you will want when someone says the model is behaving strangely. **Likely follow-ups** - Yesterday's scores are still there and today's job covered only 60% of accounts — what does the table look like? - How would you serve the latest score with sub-10ms lookup? - What would you do if the scoring job fails halfway through? --- ## 32. Your model serves in 40 milliseconds but the endpoint takes 900. The feature lookup is a SQL query. Explain. *Medium · Occasional* **Short answer.** Analytical warehouses are built for throughput, not for single-row latency. A query that scans and aggregates costs hundreds of milliseconds regardless of how little it returns. Precompute features into a key-value store and let serving do a keyed lookup instead of computing anything. The query looks harmless: ``` SELECT COUNT(*) FILTER (WHERE played_at >= NOW() - INTERVAL '7 days') AS games_7d, AVG(duration_sec) FILTER (WHERE played_at >= NOW() - INTERVAL '30 days') AS avg_duration_30d FROM esports_matches WHERE player_id = 88214; ``` One row out, and it still aggregates every match that player has ever recorded. On a columnar warehouse this is worse, not better: those systems are optimised for scanning millions of rows in parallel and carry per-query overhead measured in hundreds of milliseconds, so a query returning one row costs nearly as much as one returning a lakh. Four things add up to the 860 milliseconds. Query planning and coordination overhead. Scanning the player’s history. Connection acquisition, which on a warehouse can dominate everything else. And any queueing behind other work on a shared cluster, which is why the p99 is far worse than the p50. The fix is to move the computation out of the request path entirely. A batch or streaming job computes the features on a schedule and writes one row per player to a key-value store; serving does a keyed get in one or two milliseconds and computes nothing. Staleness is the trade-off you accept and must state. Features refreshed hourly mean the model scores on hour-old activity, which is fine for a matchmaking quality model and unacceptable for detecting an ongoing cheating session. For genuinely real-time signals, maintain a counter updated by the event stream rather than querying history. Two further points worth making. If a SQL lookup is unavoidable, it must be an indexed point lookup against a transactional database with connection pooling, not an aggregate against a warehouse, and the feature must already be materialised as a column. And `NOW()` inside a serving query is its own problem beyond latency: the training version used a fixed observation date and the serving version uses wall-clock time, so the two windows differ. Latency work often surfaces a skew bug that was there all along. **Likely follow-ups** - You cache the features — what breaks when the cache is stale? - How would you handle a feature that genuinely needs the last thirty seconds of activity? - Where would you look first if p50 is fine and p99 is terrible? --- ## 33. You need to store embeddings and find nearest neighbours. Can a relational database do that, and what does it cost you? *Medium · Occasional* **Short answer.** PostgreSQL with pgvector gives a native vector type and distance operators, and several warehouses now offer similar functions. Exact search scans every row, so an approximate index such as HNSW or IVFFlat is what makes it usable, trading a little recall for a large speed gain. ``` CREATE EXTENSION vector; CREATE TABLE recipe_embeddings ( recipe_id BIGINT PRIMARY KEY, model_ver TEXT NOT NULL, embedding vector(768) ); CREATE INDEX ON recipe_embeddings USING hnsw (embedding vector_cosine_ops); SELECT recipe_id, 1 - (embedding :query_vec) AS cosine_similarity FROM recipe_embeddings WHERE model_ver = 'text-v3' ORDER BY embedding :query_vec LIMIT 10; ``` `<=>` is cosine distance in pgvector, `<->` is L2 and `<#>` is negative inner product. The operator in the ORDER BY must match the operator class the index was built with, or the index is ignored silently and you get a sequential scan that still returns correct results, just slowly. That mismatch is the most common reason someone reports pgvector being unusably slow. Without an index the query is exact and reads every row. At a few lakh vectors that is seconds, not milliseconds. HNSW gives better recall and query speed than IVFFlat at the cost of slower build and more memory; IVFFlat needs the table populated before you build it, since it clusters on existing data. Approximate means approximate. The index may miss some true neighbours, controlled by `ef_search` on HNSW or `probes` on IVFFlat, and there is a real recall-latency curve you should measure on your own data rather than accept the defaults. The operational issue that catches teams is the filter. `WHERE model_ver = 'text-v3'` combined with an ANN scan can return fewer than the requested rows, because the index finds candidates first and the filter removes some afterwards. Partial indexes per model version, or a separate table, avoid the surprise. Re-embedding with a new model invalidates everything. Old and new vectors are not comparable, so distances between them are meaningless. Keep the model version as a column, write the new vectors alongside, and cut over once the backfill is complete rather than mixing them. The honest boundary: at tens of crores of vectors with heavy query volume, a dedicated vector store usually wins. Keeping vectors in the same database as your relational data is worth a lot when you need to filter and join, and that convenience is the main reason to choose it. **Likely follow-ups** - Your ANN index returns 94 of the true top 100 — is that acceptable? - What happens to the index when you re-embed everything with a new model? - When would you keep the vectors out of the database entirely? --- ## 34. Write me the monitoring queries for a deployed model. What are you actually watching? *Hard · Occasional* **Short answer.** Watch three things separately: input feature distributions against the training baseline, the prediction score distribution over time, and outcome quality once labels arrive. Compare bucketed distributions rather than means alone, since a mean can hold steady while the shape changes completely. Prediction drift is the cheapest signal and needs no labels: ``` SELECT scored_on, AVG(score) AS mean_score, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) AS p50, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY score) AS p95, AVG(CASE WHEN score > 0.5 THEN 1.0 ELSE 0 END) AS positive_rate FROM warranty_claim_scores WHERE scored_on >= CURRENT_DATE - 60 GROUP BY scored_on ORDER BY scored_on; ``` A positive rate that moves from 4% to 11% over a fortnight is worth investigating regardless of whether accuracy has changed yet. Input drift needs a stored baseline. Compare bucketed shares rather than means, because a bimodal shift can leave the mean untouched: ``` WITH live AS ( SELECT WIDTH_BUCKET(claim_amount, 0, 200000, 20) AS bucket, COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS live_share FROM scoring_inputs WHERE scored_on = CURRENT_DATE - 1 GROUP BY 1 ) SELECT l.bucket, b.baseline_share, l.live_share, (l.live_share - b.baseline_share) * LN(NULLIF(l.live_share, 0) / NULLIF(b.baseline_share, 0)) AS psi_term FROM live l JOIN training_baseline b ON b.bucket = l.bucket; ``` Summing `psi_term` gives the population stability index. The commonly used rule of thumb treats values under 0.1 as stable and above 0.25 as a material shift, though those thresholds are convention rather than statistics, so calibrate them against your own history of false alarms before wiring them to a pager. Outcome quality comes last because labels lag. Warranty claims resolve over weeks, so accuracy on a given day’s predictions is not computable for weeks, and any monitoring built only on accuracy is blind precisely when you need it. Two things worth saying unprompted. Null rate per feature belongs in the same dashboard, because a feature that silently goes 80% null is a broken pipeline masquerading as drift, and the two need different responses. And segment the drift, since an overall stable distribution can hide one region shifting hard while others compensate. **Likely follow-ups** - A feature's mean shifts 12% — do you alert, and on what evidence? - Your labels arrive 60 days late; what can you monitor before then? - How would you tell input drift apart from a broken upstream pipeline? --- ## 35. One task in your join has been running for two hours while the rest finished in four minutes. What's happening? *Hard · Occasional* **Short answer.** Rows are distributed across workers by a hash of the join key, so a key with far more rows than others lands entirely on one worker. That worker processes a disproportionate share while the rest idle. Fix it by salting the hot key, broadcasting the small side, or handling the hot keys separately. Distributed joins shuffle rows so that matching keys meet on the same worker. If 40% of your rows carry one key, 40% of the work goes to one machine, and the job finishes when that machine finishes. Confirm before treating it. Look at the key distribution rather than guessing: ``` SELECT device_key, COUNT(*) AS rows FROM ad_impression_log GROUP BY device_key ORDER BY rows DESC LIMIT 20; ``` The classic culprit is a placeholder. A device key of `'unknown'` or `-1` assigned to every unidentified impression is not a real entity, and it can carry more rows than every genuine key combined. That case has the easiest fix: filter those rows out of the join, handle them separately, and union the results back. When the hot key is legitimate, salting spreads it: ``` WITH salted_left AS ( SELECT *, CONCAT(device_key, '-', CAST(FLOOR(RANDOM() * 20) AS TEXT)) AS salted_key FROM ad_impression_log ), exploded_right AS ( SELECT d.*, CONCAT(d.device_key, '-', CAST(s.n AS TEXT)) AS salted_key FROM device_dim d CROSS JOIN UNNEST(GENERATE_ARRAY(0, 19)) AS s(n) ) SELECT ... FROM salted_left l JOIN exploded_right r ON r.salted_key = l.salted_key; ``` The large side gets a random salt appended to the key; the small side is replicated once per salt value so every fragment still finds its match. The hot key now spreads across twenty workers. The cost is twenty times the rows on the dimension side, which is acceptable only because that side is small. Broadcasting is simpler where it applies. If one side fits in worker memory, send a full copy to every worker and skip the shuffle entirely. Most engines do this automatically below a size threshold, and you can hint it. Skew disappears because nothing is partitioned by the key. Two things worth naming. Skew also arises from partitioning and window functions, not only joins: a `PARTITION BY` on a skewed key has the same problem with the same fixes. And aggregating before joining often removes the issue entirely, since collapsing a hot key to one row per key means there is nothing left to concentrate. **Likely follow-ups** - How would you confirm skew rather than assume it? - What would you do if the skewed key is a legitimate high-volume merchant? - Does salting help if the skew is on the small side of the join? --- ## 36. How do you test a SQL transformation? It's not a function you can call with arguments. *Medium · Occasional* **Short answer.** Two layers. Assertion tests run against real output and check invariants such as uniqueness, non-null and accepted ranges. Fixture tests run the transformation against small handcrafted inputs and compare to an expected result, which is the only way to cover edge cases that may not exist in production yet. Assertion tests are the cheap layer and catch most regressions. Each is a query returning rows only when something is wrong. ``` -- grain is one row per bus per service date SELECT bus_id, service_date, COUNT(*) FROM fct_bus_utilisation GROUP BY bus_id, service_date HAVING COUNT(*) > 1; -- occupancy must be a valid proportion SELECT trip_id, occupancy_ratio FROM fct_bus_utilisation WHERE occupancy_ratio < 0 OR occupancy_ratio > 1; ``` dbt expresses these declaratively as `unique`, `not_null`, `accepted_values` and `relationships` tests in a YAML file, plus custom tests as SQL. Empty result means pass. What assertions cannot do is prove the logic is correct. A transformation can produce unique, non-null, in-range values that are wrong. For that you need fixtures: a handful of input rows written by hand, run through the transformation, compared against an expected output you also wrote by hand. Fixtures are where you cover the cases production has not produced yet. A null in the join key. A bus with two service records on the same date. A trip spanning midnight. An occupancy of exactly zero. Those are the situations that break a query eighteen months later, and no amount of testing against today’s data will surface them. Structure a fixture test as input CTEs, the transformation under test, and a comparison: ``` WITH expected AS (SELECT ... UNION ALL SELECT ...), actual AS (SELECT * FROM {{ ref('fct_bus_utilisation') }}) SELECT * FROM ( SELECT * FROM expected EXCEPT SELECT * FROM actual UNION ALL SELECT * FROM actual EXCEPT SELECT * FROM expected ) diff; ``` The two-way EXCEPT catches both missing and extra rows, where a one-way comparison misses half the failures. Two habits that make this stick. Every bug you fix gets a fixture reproducing it, so it cannot return. And keep tests fast enough to run on every change, since a suite taking twenty minutes gets skipped exactly when someone is in a hurry, which is when it was most needed. **Likely follow-ups** - Your test passes on production data and fails on the fixtures — which one do you trust? - How would you test that a query correctly handles a null in the join key? - What would you assert about a query whose output changes every day? --- ## 37. Your daily SQL task runs in Airflow. Why can't the query just use CURRENT_DATE? *Medium · Occasional* **Short answer.** CURRENT_DATE is wall-clock time at execution, so a rerun of an old day processes today instead. Use the orchestrator's logical date template, passed in as a parameter, so the task processes the day it represents whenever it happens to run. Pair it with an idempotent write. Airflow gives every run a logical date representing the interval being processed. That is not the same as the moment the task executes, and the gap is exactly where the bug lives. ``` -- templated DELETE FROM fct_lab_test_results WHERE report_date = '{{ ds }}'; INSERT INTO fct_lab_test_results (report_date, lab_id, tests, avg_turnaround_hours) SELECT DATE '{{ ds }}', lab_id, COUNT(*), AVG(turnaround_hours) FROM stg_lab_reports WHERE report_date = DATE '{{ ds }}' GROUP BY lab_id; ``` Write `CURRENT_DATE` instead and three things break. A task that fails on Tuesday and is cleared on Thursday processes Thursday, leaving Tuesday permanently missing. A ninety-day backfill runs ninety times and writes the same day ninety times. And the query is no longer reproducible, since rerunning it later gives a different answer with no change to the code. The `{{ ds }}` value is the logical date, and understanding which day that refers to matters. In Airflow’s data-interval model, a daily DAG scheduled at 02:00 with a logical date of the 3rd runs after the 3rd’s interval has closed, so it processes the 3rd while the wall clock reads the 4th. Newer versions expose `data_interval_start` and `data_interval_end` explicitly, which are clearer than reasoning about `ds` and its offsets. Pair templating with an idempotent write, as above: delete the partition then insert it, in one transaction. Templating alone gives you the right date; idempotency is what makes reruns and backfills safe. Without both, a backfill either duplicates rows or leaves gaps. Two practical points. Keep the SQL in a `.sql` file referenced by the operator rather than inline in the DAG. It stays readable, it can be linted and diffed properly, and it can be run manually against the warehouse for debugging with the parameters substituted by hand. And be careful about concurrency during backfills. Ninety tasks writing ninety different partitions is fine; ninety tasks with overlapping windows is not, and the delete-then-insert pattern will interleave badly. Bound the parallelism or make the partition boundaries strictly disjoint. **Likely follow-ups** - What date does a run triggered on the 5th for the 3rd actually process? - How would you make a backfill of ninety days safe to run? - Where would you put the SQL itself, and why not inline in the DAG file? --- ## 38. Your pipeline uses an ORM. When would you drop to raw SQL, and when would you not? *Medium · Occasional* **Short answer.** Use raw SQL for bulk analytical work: window functions, CTEs, aggregations over large tables, and anything where you need to see the exact query the database receives. Keep the ORM for row-level application logic, where its mapping, validation and migration tooling earn their place. The failure that sends most ML pipelines to raw SQL is the N+1 pattern. Loading five lakh sensor readings as objects and iterating over a related attribute issues one query per object, so a job that should be one aggregate scan becomes five lakh round trips. It works in development against a thousand rows and collapses in production. Reach for raw SQL when: - The work is set-based. Window functions, multi-CTE transformations, conditional aggregation. Most ORMs can express these awkwardly and the resulting code is harder to read than the SQL would have been. - You need to see and tune the exact query. Optimising something you cannot read is guesswork, and ORM-generated SQL is frequently not what you expect. - Volume is large. Materialising rows as objects costs memory and time you do not need to spend when the result is an aggregate. - The SQL needs to be reviewed by a data engineer or an analyst who does not read your ORM. Keep the ORM when: - You are reading or writing individual records with validation and relationships. - Schema migrations are managed through it, which is a genuine benefit worth preserving. - Portability across engines actually matters, though it matters less often than people claim. A hybrid is normal and not a compromise. SQLAlchemy Core, or `session.execute(text(...))` with bound parameters, gives you the connection pooling, transaction handling and parameter binding while you write the SQL yourself. Dropping to raw SQL never means dropping parameter binding; string interpolation is a separate and worse decision. For an ML feature pipeline the usual split is: raw SQL for the extraction and aggregation that produces the training table, ORM for the application that consumes the scores. The two live in different code paths with different demands. Keep the raw SQL in `.sql` files rather than embedded in Python strings. It stays syntax-highlighted, greppable and diffable, and an analyst can run it directly against the warehouse when a number looks wrong. **Likely follow-ups** - How would you find out whether the ORM is issuing one query or a thousand? - Does using raw SQL mean giving up on parameter binding? - Where would you keep raw SQL so it doesn't rot? --- More Machine Learning Engineer sets: https://codeayan.com/get-hired/machine-learning-engineer All interview prep: https://codeayan.com/get-hired