# Data Analyst Interview Questions — SQL Source: Codeayan (https://codeayan.com) Canonical: https://codeayan.com/get-hired/data-analyst/sql Questions: 52 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. For each showroom, give me its three best-selling models by units. How do you write that? *Medium · Very Common* **Short answer.** Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC), then filter to rank <= 3 in an outer query. The filter must be outside, because window functions are evaluated after WHERE. Choose RANK instead if ties should all be kept. The pattern is two steps, and the second step is where people get stuck. ``` WITH model_sales AS ( SELECT showroom_id, model, COUNT(*) AS units FROM used_car_sales WHERE sold_on >= '2026-01-01' GROUP BY showroom_id, model ), ranked AS ( SELECT showroom_id, model, units, ROW_NUMBER() OVER (PARTITION BY showroom_id ORDER BY units DESC) AS rn FROM model_sales ) SELECT showroom_id, model, units FROM ranked WHERE rn **Likely follow-ups** - Two models tie for third place — does your query return three rows or four? - What would you change to get the top three by revenue instead of units? - Is there a way to do this on a database with no window functions? --- ## 3. I want each film's share of its screen's total collection, with the film rows still visible. How do you calculate percent of total? *Medium · Very Common* **Short answer.** Divide the row's value by a windowed total: SUM(revenue) OVER (PARTITION BY group). That keeps the detail rows while giving each one its group total. Multiply by 100.0, not 100, or integer division returns zero, and use NULLIF on the denominator to survive empty groups. ``` SELECT screen_no, film_title, collection, ROUND(100.0 * collection / NULLIF(SUM(collection) OVER (PARTITION BY screen_no), 0), 2) AS pct_of_screen, ROUND(100.0 * collection / NULLIF(SUM(collection) OVER (), 0), 2) AS pct_of_multiplex FROM show_collections WHERE show_date = '2026-08-19'; ``` Two denominators, two questions, one pass. `PARTITION BY screen_no` totals within the screen. An empty `OVER ()` treats the whole result set as a single partition and gives the multiplex-wide share. Three details decide whether this query is right or subtly wrong. The `100.0` is not cosmetic. In PostgreSQL, dividing an integer by an integer performs integer division, so `100 * collection / total` on a film with a 30% share returns 30 only by luck of rounding order, and a 3% share can come out as 0. Force one side to a decimal or numeric type before the division. MySQL is more forgiving here, which is worse in practice, because code tested on MySQL breaks after a migration. NULLIF handles the zero denominator. A cancelled show leaves a screen with zero collection, and PostgreSQL raises a division-by-zero error that kills the entire query rather than just that row. NULLIF converts the zero to NULL, the expression yields NULL, and everything else still returns. The third one is the trap that reaches dashboards. The window sees only rows that survived WHERE. Add `AND language = 'Kannada'` and `pct_of_multiplex` silently becomes the share of Kannada collections, not of the multiplex. Every value still sums to a tidy 100, so nothing looks broken. If the denominator must stay fixed while the display is filtered, compute the total in a separate CTE over the unfiltered data and join it in. **Likely follow-ups** - How would you show both share of screen and share of the whole multiplex on the same row? - If I filter to one language, what does the denominator become? - What does your query return for a screen that collected nothing? --- ## 4. 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? --- ## 5. 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? --- ## 6. Show me month-over-month growth in fuel sales as a percentage. What can go wrong with that number? *Medium · Very Common* **Short answer.** Aggregate to one row per month first, then compare each month with the previous one and express the difference as a percentage of that previous value. Guard the denominator with NULLIF. The bigger risk is a month with no sales, which is absent from the data and quietly shifts every comparison. ``` WITH monthly AS ( SELECT DATE_TRUNC('month', filled_at) AS month, SUM(litres) AS litres FROM fuel_dispenses WHERE pump_id = 214 GROUP BY 1 ) SELECT month, litres, LAG(litres) OVER (ORDER BY month) AS prev_litres, ROUND(100.0 * (litres - LAG(litres) OVER (ORDER BY month)) / NULLIF(LAG(litres) OVER (ORDER BY month), 0), 1) AS mom_pct FROM monthly ORDER BY month; ``` `DATE_TRUNC` is PostgreSQL. On MySQL use `DATE_FORMAT(filled_at, '%Y-%m-01')`, and on SQL Server `DATEFROMPARTS(YEAR(filled_at), MONTH(filled_at), 1)`. Truncating to a real date rather than a `'2026-08'` string matters, because string months sort correctly only while the format is fixed and break the moment someone writes `8` instead of `08`. Now the part that separates a working analyst from someone reciting the pattern. LAG steps back one row. It does not step back one month. A pump shut for maintenance through April has no April row, so May’s comparison reaches back to March while the column still reads “previous month”. Growth looks flat when it actually doubled. Nothing errors, and the number is plausible enough to survive review. Build a complete month series and left join the actuals onto it before applying LAG. The second issue is the partial month. Run this on 19 August and August shows a 40% decline, because eleven days have not happened yet. Either exclude the current month from the report, or compare like periods by restricting both months to the same day count. One presentation point worth raising in the room: if the metric can be zero or negative, a percentage change is not meaningful. Growth from zero to 8,000 litres is not an infinite improvement, it is a pump coming back online. Report the absolute change alongside the percentage, and suppress the percentage where the base is zero. **Likely follow-ups** - The current month is only half over — how should it appear in this report? - How would you flag growth as a percentage-point change instead of a percentage? - What happens to the percentage when the previous month is negative? --- ## 7. 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? --- ## 8. 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? --- ## 9. 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? --- ## 10. 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? --- ## 11. 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? --- ## 12. 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? --- ## 13. 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? --- ## 14. 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? --- ## 15. 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? --- ## 16. 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? --- ## 17. 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? --- ## 18. 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? --- ## 19. 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? --- ## 20. This table has duplicate rows. How do you find them, and then how do you delete all but one of each? *Medium · Very Common* **Short answer.** Find them with GROUP BY on the columns that define a duplicate, filtered by HAVING COUNT(*) > 1. Delete with ROW_NUMBER partitioned by those same columns, removing every row where the number is above 1. Decide which copy to keep before you run anything. Start by settling what “duplicate” means. Two grievance tickets from the same citizen about the same complaint type on the same day are probably a double submission. Two tickets a month apart are not. That definition is a business decision and it goes in the PARTITION BY, so ask for it rather than assuming the whole row must match. Finding them: ``` SELECT citizen_id, complaint_type, filed_on, COUNT(*) AS copies FROM grievance_tickets GROUP BY citizen_id, complaint_type, filed_on HAVING COUNT(*) > 1; ``` That gives the duplicated keys and how many copies each has. To see the actual rows, including their IDs, ROW_NUMBER is more useful: ``` WITH marked AS ( SELECT ticket_id, citizen_id, complaint_type, filed_on, ROW_NUMBER() OVER (PARTITION BY citizen_id, complaint_type, filed_on ORDER BY ticket_id) AS rn FROM grievance_tickets ) SELECT * FROM marked WHERE rn > 1; ``` Every row with `rn > 1` is a surplus copy. Run this SELECT and eyeball the results before you delete anything. Deletes on a production table are not a thing to discover a bug in afterwards. The delete, in PostgreSQL: ``` DELETE FROM grievance_tickets WHERE ticket_id IN (SELECT ticket_id FROM marked WHERE rn > 1); ``` MySQL 8.0 will not let you delete from a table you are also selecting from in a subquery. The self join form works there: ``` DELETE t FROM grievance_tickets t JOIN grievance_tickets keep ON keep.citizen_id = t.citizen_id AND keep.complaint_type = t.complaint_type AND keep.filed_on = t.filed_on AND keep.ticket_id < t.ticket_id; ``` The `ORDER BY ticket_id` decides which copy survives, and it is a real choice, not boilerplate. Ordering by ID keeps the earliest. `ORDER BY last_updated_at DESC` keeps the most recently edited, which is usually what you want when the copies are not byte-identical and one of them has been worked on. Two things that separate a careful answer here. If the table has no unique identifier at all, ROW_NUMBER has nothing to target for deletion, and the standard route is to build a deduplicated copy and swap the tables. And once the data is clean, add a unique constraint on those columns, or the same import will recreate the duplicates next week and you will be running this again. **Likely follow-ups** - Which copy does your delete keep, and can you make it keep the most recent one? - What if the table has no primary key at all? - How would you stop this from happening again after the cleanup? --- ## 21. 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? --- ## 22. Compare this year's monthly ridership against the same month last year. Would you use a self join or LAG, and why? *Medium · Common* **Short answer.** Two ways: LAG with an offset of twelve rows over a monthly series, or a self join matching this year's month to the same month a year earlier. LAG is shorter but silently wrong when a month is missing, since it counts rows and not calendar distance. The self join matches on the date itself. The self join states the relationship explicitly: ``` WITH monthly AS ( SELECT DATE_TRUNC('month', travel_date) AS month, COUNT(*) AS rides FROM metro_journeys GROUP BY 1 ) SELECT c.month, c.rides AS this_year, p.rides AS last_year, ROUND(100.0 * (c.rides - p.rides) / NULLIF(p.rides, 0), 1) AS yoy_pct FROM monthly c LEFT JOIN monthly p ON p.month = c.month - INTERVAL '1 year' ORDER BY c.month; ``` The LAG version is `LAG(rides, 12) OVER (ORDER BY month)`, which is one line instead of a join. Both are defensible, and the interviewer wants to hear the trade-off rather than a preference. LAG counts rows. If the series is complete and contiguous, twelve rows back is twelve months back and the two agree exactly. If a month is missing, LAG reaches thirteen months back instead and labels it as last year. The self join asks for a specific calendar month and returns NULL when it is absent, which is visibly a gap rather than a wrong number quietly presented as right. The LEFT JOIN also matters. Use an inner join and the first twelve months disappear from the report entirely, because they have no prior year to match. That looks like missing data to whoever reads it. Date arithmetic is where portability breaks. `INTERVAL '1 year'` is PostgreSQL. MySQL wants `DATE_SUB(c.month, INTERVAL 1 YEAR)`, SQL Server wants `DATEADD(YEAR, -1, c.month)`. Joining on `MONTH(date) = MONTH(date) AND YEAR(date) = YEAR(date) - 1` also works and is portable, but wrapping the join key in functions usually prevents index use, so prefer the interval form on large tables. A caution worth voicing: year-on-year assumes the two periods are comparable. Metro ridership shifts with festival dates, which move against the Gregorian calendar, so a Diwali-heavy October compared against a Diwali-light one produces a swing that has nothing to do with the trend. Say that out loud. Interviewers ask this question partly to see whether you treat the number as an answer or as a starting point. **Likely follow-ups** - The network added two new stations in March — does that make the comparison invalid? - How would you get the three-year trend on one row per month? - Which approach would you pick if the data were daily rather than monthly? --- ## 23. 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? --- ## 24. Give me a 7-day moving average of PM2.5 readings per station. What does your frame actually cover? *Medium · Common* **Short answer.** Use AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). That averages the current row and the six before it, so the first six rows return a partial average over fewer points. ROWS counts rows, so any missing date shortens the real time span. ``` SELECT station_id, reading_date, pm25, ROUND(AVG(pm25) OVER (PARTITION BY station_id ORDER BY reading_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS ma_7d, COUNT(*) OVER (PARTITION BY station_id ORDER BY reading_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS points_used FROM daily_air_quality; ``` Seven rows including the current one, restarting at each station. The `points_used` column is not decoration. It tells you which rows are averaging over a full week and which are not, and it costs almost nothing. Two things about that frame deserve attention. The first six rows of every station return an average over 1, 2, 3 up to 6 readings. That is not an error, but it is also not a seven-day average, and plotting it as one produces a line that starts wherever the first reading happened to sit. Either hide those rows in the outer query, or use `points_used = 7` as a filter, or label them as provisional. Deciding this explicitly is what a reviewer looks for. The larger issue is that ROWS counts rows rather than days. A station offline for three days has no rows for those days, so on the day it returns, the window silently spans ten calendar days instead of seven. The average is smoother than it should be and the label is wrong. Two fixes: build a complete date series per station and left join the readings onto it, or switch to a range-based frame where the engine supports it. PostgreSQL allows `RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW`, which defines the window by date value rather than by row position and handles gaps correctly. MySQL 8.0 supports RANGE with numeric offsets but not date intervals, so the spine approach is the portable one. A centred window is the same syntax with a different frame: `ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING`. Better for smoothing a historical chart, useless for anything live, since it needs data from the future. **Likely follow-ups** - A station went offline for three days — what does your average cover for the days after? - How would you centre the window instead of trailing it? - Would you suppress the first six rows, and what would you show instead? --- ## 25. DISTINCT or GROUP BY for removing duplicates — is there a real difference, and which one do you reach for? *Easy · Common* **Short answer.** For plain deduplication of a column list they produce the same rows and usually the same plan. GROUP BY earns its place when you also need aggregates per group; DISTINCT reads more clearly when you only want unique values. DISTINCT applies to the whole select list, not one column. On a straightforward deduplication, these two are interchangeable: ``` SELECT DISTINCT vehicle_id FROM trips WHERE trip_date >= '2026-08-01'; SELECT vehicle_id FROM trips WHERE trip_date >= '2026-08-01' GROUP BY vehicle_id; ``` Same rows, same order guarantees (none, in both cases), and on MySQL and PostgreSQL you will typically see the same plan in EXPLAIN. Anyone who tells you one is universally faster is repeating something they read. Check the plan on your own data. The genuine split is intent. GROUP BY is the right tool the moment you want something *about* each group: trips per vehicle, total distance, last trip date. DISTINCT is the right tool when you only want the unique values and nothing else, and it signals that intent to whoever reads the query next. The gotcha that actually costs people: DISTINCT applies to the entire select list, not to the column it happens to sit next to. ``` SELECT DISTINCT vehicle_id, driver_id FROM trips; ``` That returns distinct vehicle-driver *pairs*. A truck driven by four different drivers appears four times, and someone who expected one row per truck now has an inflated fleet count. If you need one row per vehicle plus a representative driver, you need an aggregate or a window function, not DISTINCT. Related, and worth flagging in a code review: DISTINCT bolted on to a query because the join was returning duplicates. It hides a fan-out rather than fixing it, and it can also delete rows you wanted, if two legitimately different records look identical once the extra columns are dropped from the select list. **Likely follow-ups** - You add a second column to a SELECT DISTINCT and the row count goes up — why? - If someone adds DISTINCT to fix duplicate rows in a join result, what would you check first? - Would either version let you filter on a per-group count? --- ## 26. Build me a monthly cohort retention table from a signup table and an activity log. Walk me through it. *Hard · Common* **Short answer.** Assign each user a cohort month from their signup date, then join their activity and measure the month offset between activity and signup. Count distinct users per cohort and offset, and divide by the cohort size. The result is a triangle: recent cohorts have fewer observed months. Retention is a self-referential percentage, so build it in layers rather than trying to write it as one query. ``` WITH cohorts AS ( SELECT learner_id, DATE_TRUNC('month', signed_up_at) AS cohort_month FROM learners ), cohort_size AS ( SELECT cohort_month, COUNT(*) AS learners FROM cohorts GROUP BY cohort_month ), activity AS ( SELECT DISTINCT c.cohort_month, c.learner_id, (DATE_PART('year', l.lesson_at) - DATE_PART('year', c.cohort_month)) * 12 + (DATE_PART('month', l.lesson_at) - DATE_PART('month', c.cohort_month)) AS month_offset FROM cohorts c JOIN lesson_completions l ON l.learner_id = c.learner_id ) SELECT a.cohort_month, a.month_offset, COUNT(DISTINCT a.learner_id) AS active, s.learners AS cohort_size, ROUND(100.0 * COUNT(DISTINCT a.learner_id) / s.learners, 1) AS retention_pct FROM activity a JOIN cohort_size s ON s.cohort_month = a.cohort_month GROUP BY a.cohort_month, a.month_offset, s.learners ORDER BY a.cohort_month, a.month_offset; ``` The month offset is the piece people get wrong. Subtracting two dates and dividing by 30 drifts, because months are not 30 days long, and a learner active on day 31 of a 31-day month lands in the wrong bucket. Computing it from year and month components is exact. `COUNT(DISTINCT learner_id)` is non-negotiable. A learner finishing nine lessons in one month is one retained learner, not nine, and using `COUNT(*)` produces retention figures above 100% that look like an obvious bug only until a cohort happens to stay under 100. Two definitional questions to raise before writing anything. First, is month 0 always the full cohort? Only if signing up counts as activity. If retention is measured by lesson completions and some learners never open a lesson, month 0 comes out below 100%, which is a legitimate design and confuses everyone who sees it without warning. Second, does retained mean “active in that exact month” or “still active as of that month”? The query above answers the first. The second is a cumulative definition and produces a very different, always-higher curve. The output shape is a triangle, not a rectangle. A cohort from three months ago cannot have a month 6 figure. Presenting the missing cells as 0% instead of blank makes a healthy product look like it is collapsing, and that mistake reaches leadership decks more often than it should. For display, pivot the offsets into columns with conditional aggregation. Keep the calculation in long form and pivot only at the end, so the numbers stay checkable. **Likely follow-ups** - Month 0 comes out at 94% instead of 100% — what would you check? - How would you change this to weekly cohorts? - Should a user who returns in month 3 after being absent in month 2 count as retained? --- ## 27. From a single events table, give me the conversion rate at each step of the application funnel. *Hard · Common* **Short answer.** Reduce the events table to one row per user per step, then count distinct users who reached each step. Report two rates: step-to-step conversion against the previous step, and overall conversion against the entry step. Decide upfront whether steps must happen in order and within a session. Start by asking what a funnel means here, because two reasonable people will build two different queries. Must the steps happen in sequence, or is reaching a step enough regardless of order? Is there a time window, or does a candidate who applies six months after viewing a job still count as converted? Answer those and the SQL is straightforward. Skip them and you will produce a number nobody can reconcile. Take the strict-order reading, on a job portal: ``` WITH first_touch AS ( SELECT candidate_id, MIN(CASE WHEN event_name = 'job_view' THEN occurred_at END) AS viewed_at, MIN(CASE WHEN event_name = 'apply_started' THEN occurred_at END) AS started_at, MIN(CASE WHEN event_name = 'resume_attached' THEN occurred_at END) AS attached_at, MIN(CASE WHEN event_name = 'apply_submitted' THEN occurred_at END) AS submitted_at FROM portal_events WHERE occurred_at >= '2026-08-01' GROUP BY candidate_id ), steps AS ( SELECT COUNT(viewed_at) AS viewed, COUNT(CASE WHEN started_at >= viewed_at THEN 1 END) AS started, COUNT(CASE WHEN attached_at >= started_at THEN 1 END) AS attached, COUNT(CASE WHEN submitted_at >= attached_at THEN 1 END) AS submitted FROM first_touch ) SELECT * FROM steps; ``` Collapsing to one row per candidate first is the important move. Counting events directly inflates every step, because a candidate who views eleven jobs contributes eleven view events and one submission, giving a conversion rate that is not wrong so much as measuring something else. The `>=` comparisons enforce order. Without them, a candidate whose resume was already on file gets counted at the attach step even though they never touched it in this window, and the funnel widens in the middle. A step that has more users than the one before it is the classic symptom, and it is almost always an ordering problem or a duplicate-event problem. Presenting it needs two ratios and they answer different questions. Step-to-step tells you where the leak is: 62% of candidates who started an application attached a resume. Overall tells you the size of the opportunity: 11% of viewers submitted. Reporting only one of them is how a badly leaking middle step gets missed. Two operational cautions. Events arriving late or duplicated by a client retry will distort counts, so deduplicate on an event ID if one exists. And funnels defined per candidate over a month behave differently from funnels defined per session, since a candidate can start on mobile and finish on desktop a day later. Neither definition is wrong. The one that matches how the product team talks about the funnel is the right one. **Likely follow-ups** - A candidate uploads a resume before ever viewing a job — does your query count them? - How would you add median time between steps? - What changes if the funnel needs to be measured per session rather than per candidate? --- ## 28. For every player, give me the first session they ever played and their most recent one, with the details from those rows. *Medium · Common* **Short answer.** ROW_NUMBER partitioned by user, ordered by timestamp, taking rn = 1 gives the first event and the whole row that goes with it. FIRST_VALUE and LAST_VALUE do it inline, but LAST_VALUE needs an explicit frame, otherwise the default window stops at the current row. This is the arg-max pattern: you want the row where a value is extreme, not the extreme value itself. `MIN(started_at)` tells you when the first session began. It cannot tell you which level they played or which device they were on, and pairing `MIN(started_at)` with a plain `device` column in the same GROUP BY either errors or, on a permissive MySQL, returns a device from an arbitrary session. The reliable form: ``` WITH ordered AS ( SELECT player_id, session_id, started_at, device, levels_cleared, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY started_at) AS rn_first, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY started_at DESC) AS rn_last FROM game_sessions ) SELECT player_id, MAX(CASE WHEN rn_first = 1 THEN started_at END) AS first_session_at, MAX(CASE WHEN rn_first = 1 THEN device END) AS first_device, MAX(CASE WHEN rn_last = 1 THEN started_at END) AS last_session_at, MAX(CASE WHEN rn_last = 1 THEN levels_cleared END) AS last_levels FROM ordered GROUP BY player_id; ``` Two numberings, one pass, one row per player carrying columns from two different source rows. The inline alternative is FIRST_VALUE and LAST_VALUE, and LAST_VALUE has a trap that catches nearly everyone the first time: ``` LAST_VALUE(device) OVER (PARTITION BY player_id ORDER BY started_at) -- returns the CURRENT row ``` Adding ORDER BY to a window sets the frame to everything from the start of the partition up to the current row. The last value within that frame is the current row, so the column looks like it did nothing. FIRST_VALUE appears to work only because the first row of the frame genuinely is the first row of the partition. The fix is an explicit frame: ``` LAST_VALUE(device) OVER (PARTITION BY player_id ORDER BY started_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ``` Ties are worth mentioning before the interviewer does. Identical timestamps, common when sessions are batch-loaded with a truncated time, make ROW_NUMBER pick arbitrarily and inconsistently between runs. Add `session_id` as a secondary sort key so the result is at least reproducible. **Likely follow-ups** - Two sessions share the exact same timestamp — which one does your query pick? - How would you get both first and last on a single row per player? - Would MIN and MAX work here, and where would that fall apart? --- ## 29. Define DAU, WAU and MAU and write the query. What does the stickiness ratio tell you? *Medium · Common* **Short answer.** DAU is distinct users on a day, WAU over a trailing seven days, MAU over a trailing thirty. Stickiness is DAU divided by MAU, often read as how many days a month a typical user shows up. Distinct counts do not add up, so never sum daily figures to get WAU. Settle the definition of “active” first. Opening the app, reading an article, and receiving a push notification are three different populations, and a metric that quietly shifted from the second to the first is how engagement charts jump 30% overnight with no product change. Write the definition into the query as a filter and keep it visible. ``` WITH daily AS ( SELECT read_date, reader_id FROM article_reads GROUP BY read_date, reader_id ) SELECT d.read_date, COUNT(DISTINCT CASE WHEN a.read_date = d.read_date THEN a.reader_id END) AS dau, COUNT(DISTINCT CASE WHEN a.read_date > d.read_date - 7 THEN a.reader_id END) AS wau, COUNT(DISTINCT CASE WHEN a.read_date > d.read_date - 30 THEN a.reader_id END) AS mau FROM daily d JOIN daily a ON a.read_date d.read_date - 30 GROUP BY d.read_date; ``` The self join builds a 30-day lookback for every reporting date, and the three conditional distinct counts read off different slices of it. The property that makes this question worth asking is that distinct counts are not additive. Seven days at 40,000 DAU does not give a WAU of 280,000. It gives something between 40,000 and 280,000 depending on how many of the same people came back, and that overlap is exactly the information the metric carries. Anyone who sums daily figures to produce a weekly number has destroyed the measurement. The same applies to rolling up MAU across months, and to slicing MAU by city and adding the parts back together. Stickiness is DAU divided by MAU. At 0.25, a monthly reader shows up on roughly 7.5 days out of 30. News apps run high, utilities run low, and the number is only meaningful against its own history or a close competitor. Two practical cautions. A rolling 30-day MAU and a calendar-month MAU are different metrics that share a name, so state which one a chart shows. And the whole family rests on a stable user identifier: logged-out readers identified by device produce inflated counts, and one household account shared by four people produces deflated ones. Neither is visible in the SQL, which is why the caveat belongs in the answer. **Likely follow-ups** - Can I get MAU for the year by summing twelve monthly MAU figures? - Two people share one login — how does that show up in these numbers? - Would you count a push-notification open as activity? --- ## 30. Your database has no median function. Calculate the median payout per project category. *Medium · Common* **Short answer.** PostgreSQL has PERCENTILE_CONT, so use it there. Without a built-in, number the rows by value and pick the middle one, or the average of the two middle ones when the count is even. The standard trick takes rows at positions (n+1)/2 and (n+2)/2 and averages them. Where it exists, use it: ``` SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY payout) AS median_payout FROM freelance_projects GROUP BY category; ``` PostgreSQL and Oracle support this. So does SQL Server, with different syntax, as a window function rather than an aggregate. MySQL has nothing, which is why the manual version still comes up in interviews. The manual version, working per category: ``` WITH numbered AS ( SELECT category, payout, ROW_NUMBER() OVER (PARTITION BY category ORDER BY payout) AS rn, COUNT(*) OVER (PARTITION BY category) AS n FROM freelance_projects ) SELECT category, AVG(payout) AS median_payout FROM numbered WHERE rn IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2)) GROUP BY category; ``` Work through why those two positions are right. With n = 7, they give 4 and 4, the same row twice, and averaging a single row returns it unchanged. With n = 8, they give 4 and 5, the two middle rows, averaged. One expression covers both parities with no CASE statement. The AVG is doing real work in the even case and is a no-op in the odd case, which is what makes the whole thing collapse to three lines. Two things to raise. NULL payouts must be excluded explicitly. `ORDER BY payout` places NULLs first in PostgreSQL and last in MySQL, and either way they occupy positions in the numbering and shift the middle. Add `WHERE payout IS NOT NULL` to the CTE and the ambiguity disappears. The other is why the question is asked at all. Median resists outliers in a way the mean does not. One ₹18 lakh enterprise project among forty ₹25,000 gigs pulls the mean well above anything a freelancer would actually earn, while the median stays honest. Being able to say that, and to note that median is expensive because it requires a full sort while the mean does not, is usually the point of the exercise rather than the syntax itself. There is a distinction worth knowing for the follow-up: PERCENTILE_CONT interpolates between rows, PERCENTILE_DISC returns an actual value from the data. For an even count they disagree, and which you want depends on whether a real payout figure matters. **Likely follow-ups** - Why would you report median rather than mean for this metric? - How would you extend your query to the 90th percentile? - What does your query return for a category with exactly one project? --- ## 31. 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? --- ## 32. Turn these quarterly sales rows into one row per distributor with a column for each quarter. No PIVOT operator available. *Medium · Common* **Short answer.** Aggregate with CASE WHEN inside SUM, one expression per output column. Each CASE picks out the rows belonging to that column and the aggregate collapses them onto one row per group. The column list must be written by hand, so a new category needs a query edit or dynamic SQL. ``` SELECT distributor_id, SUM(CASE WHEN quarter = 'Q1' THEN litres_sold ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN litres_sold ELSE 0 END) AS q2, SUM(CASE WHEN quarter = 'Q3' THEN litres_sold ELSE 0 END) AS q3, SUM(CASE WHEN quarter = 'Q4' THEN litres_sold ELSE 0 END) AS q4, SUM(litres_sold) AS full_year FROM paint_dispatches WHERE fiscal_year = 2026 GROUP BY distributor_id; ``` The GROUP BY defines the rows. The CASE expressions define the columns. Everything the CASE does not select contributes zero and disappears into the aggregate. The `ELSE 0` versus no ELSE choice is a real decision, not a style question. With `ELSE 0`, a distributor who sold nothing in Q3 shows 0. Without it, non-matching rows produce NULL and SUM ignores them, so a distributor with no Q3 rows at all shows NULL. Zero and NULL mean different things: one is “sold nothing”, the other is “no data”. Pick deliberately based on what the reader should conclude, and be consistent across the columns. The structural limitation is the reason SQL pivoting stays awkward. The column list is fixed at the time the query is written. SQL cannot decide its own output columns from data, because the result shape must be known before execution. Twelve months, forty product lines, or an unknown set of categories means either generating the SQL text from an application, or writing dynamic SQL with the usual injection risk, or accepting long format and letting the BI tool pivot it. Long format is very often the right answer and is worth suggesting. SQL Server has a `PIVOT` operator, but it still requires the column values to be listed explicitly, so it removes the typing rather than the limitation. PostgreSQL offers `crosstab` in the tablefunc extension, which has the same constraint. MySQL has neither. Going the other way, from wide to long, is a UNION ALL over the four columns, or `UNPIVOT` on SQL Server. Analysts hit this more often than they expect, usually when someone hands over a spreadsheet already pivoted. **Likely follow-ups** - Next year adds four more quarters — what has to change in your query? - How would you unpivot this back into rows? - A distributor sold nothing in Q3 — does that cell show zero or blank? --- ## 33. Here's a staff table with a manager_id column pointing back at staff_id. How do you list each employee alongside their manager's name? *Medium · Common* **Short answer.** A self join treats one table as two, with different aliases, so you can compare rows within it. For a staff table where manager_id points at another row's staff_id, joining the table to itself pairs each person with their manager. Use a LEFT JOIN if you want the top of the hierarchy included. Nothing special happens in the engine. The table appears twice in the FROM clause, and the aliases are what make it readable: ``` SELECT e.staff_id, e.full_name AS employee, m.full_name AS reports_to FROM store_staff e LEFT JOIN store_staff m ON m.staff_id = e.manager_id; ``` `e` is the employee side, `m` is the manager side. Same physical table, two independent row sources. The choice of LEFT over INNER is the whole interview question. A retail chain’s regional head has `manager_id` set to NULL. With an INNER JOIN that row fails to match and disappears, so your org chart is missing exactly the person at the top, and the row count is one short in a way nobody notices. LEFT JOIN keeps them with `reports_to` as NULL, and `COALESCE(m.full_name, 'Regional Head')` presents it cleanly. Ask the interviewer one thing before writing: is `manager_id` guaranteed to point at a live row? Real HR tables accumulate orphans when a manager leaves and their record is soft-deleted or archived. Those employees also come back with a NULL manager under a LEFT JOIN, and they mean something completely different from the person at the top. Distinguishing the two needs an explicit check on the manager row’s status, not just a NULL test. Self joins are not only for hierarchies. Comparing a row to its neighbour uses the same shape, such as finding two stock transfers between the same pair of stores on the same day: ``` SELECT a.transfer_id, b.transfer_id FROM transfers a JOIN transfers b ON b.from_store = a.from_store AND b.transfer_date = a.transfer_date AND b.transfer_id > a.transfer_id; ``` That `>` on the key is doing real work. Without it every row pairs with itself, and every genuine pair appears twice in reversed order. On a large table, forgetting it turns a modest result into something the query planner spends a very long time producing. One honest caveat for the follow-up: a single self join gives you one level. Arbitrary depth needs a recursive CTE, available in MySQL 8.0 and PostgreSQL, not in MySQL 5.7. **Likely follow-ups** - The CEO has no manager — does your query still return that row? - How would you get the manager's manager as well, without knowing how deep the tree goes? - Which employees are managing nobody at all? --- ## 34. Daily doses given at a vaccination camp. Some days had no doses at all and those days are missing from the table. How do you get zeros instead of gaps? *Medium · Common* **Short answer.** Generate the complete list of dates independently, then LEFT JOIN the actuals onto it and COALESCE the aggregate to zero. Without a spine, a day with no records has no row at all, so charts close the gap and averages divide by the wrong number of days. An aggregate can only summarise rows that exist. Nine doses on Monday and none on Tuesday produces one row, not two, and every downstream calculation inherits that absence. ``` WITH spine AS ( SELECT generate_series(DATE '2026-08-01', DATE '2026-08-31', INTERVAL '1 day')::date AS camp_date ) SELECT s.camp_date, COALESCE(COUNT(v.dose_id), 0) AS doses FROM spine s LEFT JOIN vaccinations v ON v.given_on = s.camp_date GROUP BY s.camp_date ORDER BY s.camp_date; ``` `generate_series` is PostgreSQL. MySQL 8.0 needs a recursive CTE: ``` WITH RECURSIVE spine AS ( SELECT DATE '2026-08-01' AS camp_date UNION ALL SELECT camp_date + INTERVAL 1 DAY FROM spine WHERE camp_date < '2026-08-31' ) ``` The LEFT JOIN direction is not negotiable. Spine on the left, facts on the right. Reverse them, or use an inner join, and the missing days vanish again, which defeats the whole exercise. Counting `v.dose_id` rather than `*` matters for the same reason: on a padded row, `COUNT(*)` returns 1 and reports a phantom dose. The damage from skipping this is quiet and cumulative. A line chart joins Monday straight to Wednesday, so the outage renders as a gentle slope rather than a hole. A 7-day moving average using a row-based frame reaches back eight calendar days without saying so. And an average of daily doses divides by the number of days that had activity, not by the number of days in the period, which inflates the figure in exactly the situations where you most want to know about downtime. For anything beyond a one-off query, keep a permanent calendar table rather than generating the series each time. One row per date, with columns for month start, fiscal quarter, weekday, and a holiday flag. It can be indexed, it joins cheaply, and it puts the definition of “working day” in one place instead of scattered across twenty reports. If the report needs every centre on every day, cross the spine with the centres list first, then LEFT JOIN the doses onto that grid. **Likely follow-ups** - Where would you keep the calendar table, and what columns would you put on it? - How does the missing day affect a 7-day average if you don't fix it? - What would you do if you also needed every centre listed for every day? --- ## 35. When would you deliberately write a CROSS JOIN? Most people only ever meet one by accident. *Medium · Common* **Short answer.** CROSS JOIN pairs every row on the left with every row on the right, with no join condition. Accidentally it is a bug, but deliberately it builds the scaffold a report needs: every date crossed with every room type, so nights with zero bookings appear as zero instead of vanishing. Booking data only contains nights that were actually booked. A hotel with no suite bookings on 14 August has no row for suites on 14 August, so a GROUP BY over that table produces an occupancy chart with holes in it. Every gap is a night the revenue team most wants to see. The fix is to generate the complete grid first, then attach the facts to it: ``` SELECT d.stay_date, rt.room_type, COALESCE(COUNT(b.booking_id), 0) AS rooms_sold FROM calendar d CROSS JOIN room_types rt LEFT JOIN bookings b ON b.stay_date = d.stay_date AND b.room_type = rt.room_type WHERE d.stay_date BETWEEN '2026-08-01' AND '2026-08-31' GROUP BY d.stay_date, rt.room_type; ``` 31 dates crossed with 5 room types gives 155 rows, guaranteed, whether or not anything was sold. The LEFT JOIN is not optional here. Swap it for an INNER JOIN and you have thrown away the empty combinations you just built the spine to expose. Category completion is the same pattern. Every sales region crossed with every product line, so a region that sold nothing this quarter shows a zero rather than being absent from the comparison. Two things to keep in view. First, the output size is the product of the inputs, so it grows fast: dates by room type by rate plan by channel reaches six figures before you have joined anything. Restrict the date range inside the spine rather than after the cross, so the smaller set is what gets multiplied. Second, the accident. An old-style comma join with a missing predicate is a CROSS JOIN wearing a disguise: ``` SELECT * FROM bookings b, room_types rt; -- no WHERE, no ON ``` Nobody writes that on purpose in a two-table query. It happens in a five-table FROM clause where one join condition was dropped during an edit. The query does not fail. It just runs for a long time and returns an implausible number of rows. Writing `CROSS JOIN` explicitly when you mean it, and using ANSI JOIN syntax everywhere else, makes the accidental version visible on sight. **Likely follow-ups** - Where does the list of dates itself come from if you don't have a calendar table? - After you build the spine and join the actuals on, which join type keeps the empty days? - What stops this from exploding if I add two more dimensions to the cross? --- ## 36. What does a query return if I use SUM and MAX but never write a GROUP BY? *Easy · Common* **Short answer.** The whole result set becomes a single group, so the query returns exactly one row even when the table is empty or the WHERE matched nothing. Mixing a bare column with an aggregate is an error in PostgreSQL and SQL Server; MySQL may allow it and return an arbitrary row's value. Everything the WHERE clause let through collapses into one group. One group, one row: ``` SELECT COUNT(*) AS bills_issued, SUM(units_billed) AS total_units, MAX(amount_due) AS highest_bill FROM electricity_bills WHERE billing_month = '2026-07'; ``` One row back, always. Not one row per consumer, not one per division. The interesting case is when the filter matches nothing. Point that query at a division with no billing run and you still get one row, containing 0 for the count and NULL for the sum and the max. This catches people writing validation logic. A check like `IF (SELECT SUM(units_billed) ...) > 0` does not go to the false branch on empty data. It goes to unknown, which is not true, and depending on the surrounding code that can look like a pass. Wrap it in COALESCE and compare a real number. The other half of the question is what happens when you add a plain column: ``` SELECT consumer_id, MAX(amount_due) FROM electricity_bills; ``` There is one group covering millions of rows, and one consumer_id has to be chosen out of all of them. PostgreSQL and SQL Server refuse to guess and raise an error. MySQL with `ONLY_FULL_GROUP_BY` disabled runs it and returns some consumer_id, not necessarily the one holding the maximum bill. That is the actual danger: the output looks exactly like the answer to “who had the highest bill”, it is formatted like the answer, and it is frequently not the answer. Getting the row that holds the maximum needs a window function, an ORDER BY with LIMIT, or a subquery on the max value. Because there is a group, HAVING is legal here too. `SELECT SUM(units_billed) FROM electricity_bills HAVING SUM(units_billed) > 100000` gives you either one row or an empty result, which is a compact way to write a threshold check. **Likely follow-ups** - The WHERE clause matches nothing — how many rows come back, and what's in them? - Can you put a HAVING on a query with no GROUP BY? - If I add consumer_id to that SELECT list, what happens on MySQL versus PostgreSQL? --- ## 37. Group these ticket sales by week. What's the first thing you check before you write that? *Easy · Common* **Short answer.** Check which day the week starts on. PostgreSQL's DATE_TRUNC always starts weeks on Monday, MySQL's WEEK function defaults to Sunday and is configurable, and SQL Server depends on a server setting. The same data grouped under two conventions produces two different weekly series. Monthly truncation is safe. Every engine agrees a month starts on the 1st. ``` -- PostgreSQL SELECT DATE_TRUNC('month', booked_at)::date AS month, SUM(fare) FROM bus_tickets GROUP BY 1; -- MySQL SELECT DATE_FORMAT(booked_at, '%Y-%m-01') AS month, SUM(fare) FROM bus_tickets GROUP BY 1; ``` Weeks are where it goes wrong. PostgreSQL’s `DATE_TRUNC('week', ...)` follows ISO-8601 and always returns a Monday, with no option. MySQL’s `WEEK()` and `WEEKOFYEAR()` take a mode argument controlling both the start day and how week 1 is defined, and the default varies by function. SQL Server’s `DATEPART(WEEK, ...)` depends on `DATEFIRST`, which is a session setting influenced by the login’s language, so the same query can return different results for two users on the same server. The failure that reaches production is not an error. Marketing pulls weekly signups in PostgreSQL and gets Monday-start weeks. Finance pulls the same metric through a MySQL replica with Sunday-start weeks. Both totals are right, they disagree on every week, and the meeting spends forty minutes on it. Two habits fix this. Truncate to a real date rather than a week number, so the column is self-documenting and sortable: ``` DATE_TRUNC('week', booked_at)::date -- returns 2026-08-17, not "34" ``` And when the convention has to be Sunday, subtract explicitly rather than hoping the engine agrees: ``` (booked_at::date - EXTRACT(DOW FROM booked_at)::int) AS week_start_sunday ``` A related trap in the follow-up: ISO week numbers do not align with calendar years. The last days of December frequently belong to week 1 of the following year, so grouping by `YEAR(date), WEEK(date)` produces a stray week with three days in it. Use `EXTRACT(ISOYEAR ...)` alongside the ISO week, or avoid week numbers entirely and group by the week’s start date. Indian fiscal quarters starting in April need their own CASE expression or a calendar table. No engine has that built in. **Likely follow-ups** - Your week starts Monday and the finance team's starts Sunday — how do you reconcile? - What does week 1 mean for a year that starts on a Thursday? - How would you group by fiscal quarter starting in April? --- ## 38. Calculate how many days each complaint took to resolve. Is a complaint opened and closed on the same day zero days or one? *Easy · Common* **Short answer.** Ask whether the boundary counts. Subtracting dates gives an exclusive difference: same-day resolution returns 0. Add 1 for an inclusive count where both endpoints are part of the duration. Timestamp subtraction is worse, since it measures elapsed time and ignores calendar days entirely. Ask the question before you write the query, because both answers are defensible and only one matches the SLA the team is reporting against. ``` -- PostgreSQL: date subtraction returns an integer, exclusive SELECT complaint_id, closed_on - opened_on AS days_open FROM consumer_complaints; ``` Opened and closed on 12 August gives 0. If your service commitment says “resolved within 3 days” and counts the opening day, you want `closed_on - opened_on + 1`. Neither is more correct. The one that goes into a compliance report is whichever the policy document means, and the only way to know is to ask. Syntax splits by engine. MySQL uses `DATEDIFF(closed_on, opened_on)`, arguments in that order, returning end minus start. SQL Server uses `DATEDIFF(DAY, opened_on, closed_on)`, with the arguments the other way round, which is a genuine source of sign errors when people move between the two. The nastier version involves timestamps. SQL Server’s `DATEDIFF(DAY, ...)` counts boundary crossings, so 11:55 PM Monday to 12:05 AM Tuesday is one day, despite being ten minutes. MySQL’s `DATEDIFF` also works on the date portion only, giving 1 for the same pair. PostgreSQL subtracting two timestamps gives an interval of ten minutes, which truncates to 0 days. Same data, three answers. Cast to date explicitly when you mean calendar days, and use full timestamp arithmetic when you mean elapsed time. Age is the classic trap in the follow-up. Dividing days by 365 drifts because of leap years, and by 365.25 it drifts differently. PostgreSQL has `AGE(dob)` and `EXTRACT(YEAR FROM AGE(dob))` for exact years; MySQL has `TIMESTAMPDIFF(YEAR, dob, CURDATE())`, which handles the birthday-not-yet-reached case correctly. Both are right where manual division is approximately right, and approximately right is what gets someone bucketed into the wrong age band. Working-day counts need a calendar table with a holiday flag. There is no portable built-in, and Indian holiday calendars vary by state, so a lookup table is the only honest approach. **Likely follow-ups** - How would you count only working days, excluding Sundays and holidays? - Two timestamps eleven hours apart on either side of midnight — how many days is that to your query? - Why can't you calculate age by dividing days by 365? --- ## 39. What kinds of subqueries are there, and where in a statement is each one allowed to appear? *Medium · Common* **Short answer.** A scalar subquery returns one row and one column and can sit anywhere a value can, including SELECT. A row subquery returns one row of several columns, compared with a row constructor. A table subquery returns many rows and belongs in FROM, IN or EXISTS. The shape of what comes back decides where it is legal. **Scalar.** One row, one column. It behaves like a value, so it can appear in SELECT, WHERE, or an expression: ``` SELECT listing_id, asking_price, asking_price - (SELECT AVG(asking_price) FROM listings) AS diff_from_city_avg FROM listings WHERE locality = 'Whitefield'; ``` The failure mode is sharp. If a scalar subquery returns more than one row at runtime, the query aborts with a cardinality error. It is a data-dependent bug: correct in staging where each locality has one benchmark row, dead in production the day a second benchmark is inserted. Anything with a GROUP BY or a non-unique filter inside a scalar position deserves a second look. Returning zero rows is gentler, giving you NULL rather than an error, which then quietly poisons the arithmetic around it. **Row.** One row, several columns, compared against a row constructor: ``` SELECT * FROM listings WHERE (locality, bhk) = (SELECT locality, bhk FROM listings WHERE listing_id = 4471); ``` MySQL and PostgreSQL support this. SQL Server does not, so there you write out the columns separately, which is why row subqueries rarely turn up in portable code. **Table.** Many rows, one or more columns. It goes in FROM as a derived table, or feeds IN and EXISTS: ``` SELECT l.locality, l.median_price FROM ( SELECT locality, AVG(asking_price) AS median_price, COUNT(*) AS listings FROM listings GROUP BY locality ) l WHERE l.listings >= 20; ``` A derived table in FROM must be given an alias in MySQL and PostgreSQL, and forgetting it is the most common syntax error people hit with this form. Cutting across all three is correlation. An uncorrelated subquery does not reference the outer query and can be evaluated once. A correlated one references an outer column and is logically evaluated per outer row, though optimisers frequently rewrite it into a join. Correlated scalar subqueries in a SELECT list over a large result set are the classic slow query in a review, and turning one into a join against a pre-aggregated derived table is usually the fix. **Likely follow-ups** - Your scalar subquery in SELECT returns two rows one day — what does the database do? - Which of these can reference a column from the outer query, and which cannot? - Would you rewrite that SELECT-clause subquery as a join, and why? --- ## 40. This column has 'MUMBAI', ' mumbai ' and 'Mumbai.' all meaning the same thing. Clean it up. *Easy · Common* **Short answer.** Chain TRIM to remove padding, UPPER or LOWER to normalise case, and REPLACE for known noise characters. Then group by the cleaned value to see what remains. Do it once in a CTE rather than repeating the expression, and push the fix upstream if the data is loaded regularly. Start by measuring the mess rather than guessing at it: ``` SELECT city_raw, COUNT(*) AS rows FROM courier_pickups GROUP BY city_raw ORDER BY rows DESC; ``` That list tells you which problems are actually present. Cleaning for problems you imagined is wasted effort, and it hides the ones you did not. Then normalise in one place: ``` WITH cleaned AS ( SELECT pickup_id, UPPER(TRIM(REPLACE(REPLACE(city_raw, '.', ''), '-', ' '))) AS city FROM courier_pickups ) SELECT city, COUNT(*) FROM cleaned GROUP BY city ORDER BY 2 DESC; ``` Doing it in a CTE matters more than it looks. Repeat that expression in the SELECT, the GROUP BY and a JOIN, and someone will eventually edit two of the three. The characters you cannot see are what make this genuinely hard. Trailing non-breaking spaces from an Excel paste, a zero-width character from a web form, and `CHAR(160)` instead of `CHAR(32)` all survive TRIM, because standard TRIM removes ordinary spaces only. Two values look identical in the result grid and still group separately. When that happens, `LENGTH(city)` next to the value exposes it instantly, and `REPLACE(city, CHAR(160), ' ')` clears the common case. PostgreSQL’s `regexp_replace(city, 's+', ' ', 'g')` handles whitespace variants in one pass. Collation is the other invisible one. MySQL’s default collations are case-insensitive, so `'Mumbai' = 'MUMBAI'` returns true and GROUP BY merges them without any UPPER at all. PostgreSQL is case-sensitive and will not. Code that appeared to work on MySQL splits into two rows after a migration, and nothing errors. Synonyms are a different problem and TRIM will never solve it. ‘Bombay’ and ‘Mumbai’ need a mapping table joined in, not a string function. Say that out loud, because the interviewer is often waiting to see whether you know the difference between formatting noise and semantic variation. One judgement call worth voicing: cleaning in the query is a patch. If this data arrives daily, the fix belongs in the load process or as a constrained lookup column, otherwise every analyst rewrites the same CASE statement slightly differently. **Likely follow-ups** - Your cleaned values still show two variants that look identical on screen — what would you check? - Would you fix this in the query or upstream, and why? - How would you handle 'Bombay' and 'Mumbai' in the same column? --- ## 41. What makes a subquery correlated, and what does that cost you when the outer query returns a few hundred thousand rows? *Medium · Common* **Short answer.** A correlated subquery references a column from the outer query, so it cannot be evaluated once up front. Logically it runs per outer row. Optimisers often rewrite it into a join, but when they cannot, a scan inside a scan turns linear work into quadratic work. The dependency is the whole definition. Remove the reference to the outer table and the subquery becomes independent, computable once, and cheap. ``` -- uncorrelated: evaluated once SELECT crate_id FROM shipments WHERE weight_kg > (SELECT AVG(weight_kg) FROM shipments); -- correlated: depends on s.route_id, evaluated per outer row SELECT s.crate_id, s.weight_kg FROM shipments s WHERE s.weight_kg > (SELECT AVG(x.weight_kg) FROM shipments x WHERE x.route_id = s.route_id); ``` The second one compares each crate against the average for its own route, which the first cannot express. Now the cost. “Runs once per outer row” is the semantics, not a promise about execution. PostgreSQL and MySQL 8.0 will often transform a correlated subquery into a semi-join or a hash join and the plan comes out fine. What you should be able to say in the room is when the transform fails. Put a correlated aggregate in the SELECT list over a large result set and it frequently does not: ``` SELECT s.crate_id, (SELECT COUNT(*) FROM scan_events e WHERE e.crate_id = s.crate_id) AS scans FROM shipments s; ``` With 400,000 shipments and no index on `scan_events.crate_id`, that is 400,000 full scans of the events table. In EXPLAIN it shows up as a subplan with a high loop count, and the query that finished in eight seconds during testing on a month of data takes forty minutes against two years of it. The rewrite is to aggregate once and join: ``` SELECT s.crate_id, COALESCE(e.scans, 0) AS scans FROM shipments s LEFT JOIN (SELECT crate_id, COUNT(*) AS scans FROM scan_events GROUP BY crate_id) e ON e.crate_id = s.crate_id; ``` One pass over the events table instead of 400,000. Note the COALESCE: the correlated version returns 0 for a crate with no scans, while the join returns NULL, and losing that difference silently changes the report. An index on the correlated column is the other lever, and often the faster fix in production. Before reaching for either, check whether a window function expresses the intent directly, because for per-group comparisons it usually does and it reads better. **Likely follow-ups** - How would you rewrite that as a join and get the same numbers? - Would an index change your answer about the cost? - Is there a case where you'd keep the correlated version on purpose? --- ## 42. You expected around 5,000 rows and got 41,000. Talk me through how you find out why. *Medium · Common* **Short answer.** Work backwards from the base table. Count it alone, then add one join at a time and watch where the number jumps. A sudden multiplication means a one-to-many join or duplicate keys on the right side. Slapping DISTINCT on the end hides the cause and can also delete legitimate rows. Resist the instinct to add DISTINCT. It makes the row count look right, leaves any SUM in the query still inflated, and removes the evidence you need. Start from the base and build up: ``` SELECT COUNT(*) FROM auction_lots; -- 5,120 SELECT COUNT(*) FROM auction_lots l JOIN bids b USING (lot_id); -- 41,300 ``` The jump happens at the bids join, so the problem is there. Now check the key directly: ``` SELECT lot_id, COUNT(*) AS copies FROM bids GROUP BY lot_id HAVING COUNT(*) > 1 ORDER BY copies DESC LIMIT 10; ``` Two possibilities, and they need different fixes. Either the relationship is genuinely one-to-many, in which case the query is doing what you asked and your expectation was wrong — aggregate the bids side before joining. Or the right table contains duplicate rows it should not, which is a data quality issue, and deduplicating in your query patches a problem everyone else is also hitting. A few other causes worth ruling out. A join condition on the wrong column, such as joining on a non-unique auction code instead of the lot ID, multiplies quietly and looks correct in the plan. A missing condition in a multi-column key does the same. And in a five-table FROM clause, an entirely absent join predicate produces a cross join, which shows up as an implausibly round multiplication. Then pick one specific case and read it end to end: ``` SELECT * FROM auction_lots l JOIN bids b USING (lot_id) WHERE l.lot_id = 4417; ``` Looking at the actual rows for one key tells you in ten seconds what counting will take twenty minutes to imply. If the count is lower than expected instead, the usual suspects are an inner join where you meant a LEFT, a WHERE condition on the right table of a LEFT JOIN, or a NULL-related filter dropping rows silently. Interviewers ask this because a wrong count is the most common bug an analyst produces, and the difference between someone who bisects the query and someone who starts randomly editing it shows up immediately. **Likely follow-ups** - You find the join is one-to-many and it's legitimate — now what? - How would you check this before running the full query? - What if the count were lower than expected instead of higher? --- ## 43. Walk me through this query you wrote. Why did you structure it this way? *Medium · Common* **Short answer.** Explain in the order the data flows: what the base table is, what each step filters or joins, and what one row of output represents. Name the decisions you made and why, especially anything a reviewer might question. Finish with how you validated the numbers. Interviewers ask this because most analytical mistakes survive review when nobody can follow the query. What is being tested is whether you can make your reasoning inspectable, not whether you can recite syntax. Structure the walkthrough as a story about the data: - **The grain.** Say what one row of the output represents before anything else. “One row per NGO per quarter, counting distinct beneficiaries served.” Everything after that has a reference point. - **The base table and filter.** Which table defines the population, and what you excluded. “Starting from `service_records`, excluding cancelled entries, because they were logged but never delivered.” - **Each join and what it does to the grain.** “Joining the NGO master is one-to-one, so the row count is unchanged. Joining volunteers would have been one-to-many, which is why that count is aggregated in a CTE first.” - **The decisions.** This is the part people skip and the part that earns the mark. Why COUNT DISTINCT rather than COUNT. Why a LEFT JOIN so NGOs with no activity still appear at zero. Why the quarter is derived from the service date and not the entry date. - **The validation.** What you compared against. A total that matches the monthly report, a spot check on one NGO, a row count that matches the base table. Two things to volunteer without being asked. Say what the query does not cover, because a stakeholder will assume it covers everything. And name the assumption most likely to be wrong, such as a period where data collection changed. Flagging your own weak point reads as confidence, not doubt. For a non-technical audience, drop the SQL entirely and describe the filters as sentences. “We took every service delivered between April and June, removed cancelled entries, and counted each beneficiary once even if they came multiple times.” If you cannot say it in that form, the query may be doing something you have not fully understood yourself. **Likely follow-ups** - Which part of this would you expect a reviewer to push back on? - How would you explain the same result to someone who has never seen SQL? - What did you check to convince yourself the numbers were right? --- ## 44. 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? --- ## 45. Split the finishers into quartiles by finish time. What's the difference between NTILE, PERCENT_RANK and CUME_DIST? *Medium · Occasional* **Short answer.** NTILE(4) drops rows into four buckets of near-equal size by position. PERCENT_RANK gives the proportion of rows strictly below a value, from 0 to 1, and CUME_DIST includes the current row. NTILE splits tied values across buckets, so equal figures can land in different quartiles. ``` SELECT bib_no, finish_minutes, NTILE(4) OVER (ORDER BY finish_minutes) AS quartile, ROUND(PERCENT_RANK() OVER (ORDER BY finish_minutes)::numeric, 4) AS pct_rank, ROUND(CUME_DIST() OVER (ORDER BY finish_minutes)::numeric, 4) AS cume_dist FROM race_results WHERE race_id = 91 AND finish_minutes IS NOT NULL; ``` NTILE distributes by count. With 4,002 finishers, two buckets get 1,001 runners and two get 1,000, and the extras go to the earliest buckets. The boundaries fall wherever the row positions land, which is the behaviour you want for “fastest quarter of the field” and the wrong behaviour for anything with fixed cut-offs. PERCENT_RANK is `(rank - 1) / (rows - 1)`, so the fastest runner is exactly 0 and the slowest exactly 1. CUME_DIST is `rows at or before this value / total rows`, so it never returns 0 and the slowest runner is 1. Both are continuous measures of position; NTILE is a bucket label. If someone asks for “the top 10%”, CUME_DIST or PERCENT_RANK expresses it directly and NTILE(10) only approximates it. The gotcha that matters in production is ties. Three runners finishing at exactly 214 minutes get the same PERCENT_RANK and the same CUME_DIST, because both are value-based. NTILE is position-based, so if the 1,000th row falls in the middle of that group, one of those runners is in quartile 1 and the other two are in quartile 2, with identical times. A participant looking at their certificate cannot be told why they are in a lower band than someone who finished at the same second. Where ties are meaningful, derive the bucket from CUME_DIST with a CASE rather than using NTILE. A NULL warning applies here as it does to all ordered windows. Runners who did not finish carry NULL, and depending on the engine those sort to one end and still occupy bucket positions, shifting every boundary. Filter them out in the query rather than trusting the default null ordering. `PERCENT_RANK` and `CUME_DIST` need at least two rows to be meaningful; with a single row PERCENT_RANK returns 0 and CUME_DIST returns 1, which is technically correct and rarely what a report wants to display. **Likely follow-ups** - 4,002 runners into four buckets — how are the extra two distributed? - Which of these would you use to define a "top 10%" badge, and why? - How would you make the quartile boundaries fixed rather than recalculated each race? --- ## 46. Here's a raw clickstream. Group those events into sessions, where a gap of more than 30 minutes starts a new one. *Hard · Occasional* **Short answer.** Use LAG to get the previous event time per user, flag every row where the gap exceeds 30 minutes, then take a running sum of those flags. The cumulative sum increments only at boundaries, so it becomes a session number within each user. Three layers, each doing one small thing. Trying to write it as a single query is how this becomes unreadable. ``` WITH gaps AS ( SELECT viewer_id, occurred_at, page_url, LAG(occurred_at) OVER (PARTITION BY viewer_id ORDER BY occurred_at) AS prev_at FROM property_clickstream ), flagged AS ( SELECT *, CASE WHEN prev_at IS NULL OR occurred_at > prev_at + INTERVAL '30 minutes' THEN 1 ELSE 0 END AS is_new_session FROM gaps ), sessions AS ( SELECT *, SUM(is_new_session) OVER (PARTITION BY viewer_id ORDER BY occurred_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_seq FROM flagged ) SELECT viewer_id, session_seq, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end, COUNT(*) AS events FROM sessions GROUP BY viewer_id, session_seq; ``` The cumulative sum is the clever part and worth being able to explain out loud. Boundary rows contribute 1, every other row contributes 0, so the running total holds steady inside a session and steps up exactly when a new one begins. It is a counter built out of an aggregate. `prev_at IS NULL` handles the very first event per viewer. Without it, that row gets flagged 0, the running sum starts at 0, and your first session is numbered zero while everyone else starts at one. Harmless until someone joins on it. Two things that break this in production. Clickstreams are frequently loaded out of order or with duplicated events from client retries, and both corrupt the gap calculation, so deduplicate on an event ID and confirm the timestamps are in a single time zone before you begin. And `session_seq` is only unique within a viewer. Concatenate it with the viewer ID, or hash the pair, before exposing it as a session identifier. The 30 minutes is a convention, not a law. Ask what a session means for this product. A property portal where people browse listings over a lunch break behaves nothing like a support tool. Ask too whether a session should be capped regardless of activity, since a tab left open overnight otherwise produces one nine-hour session. Incremental loading is the real operational headache. Yesterday’s last session may continue into today’s first events, so a daily job that sessionises only new rows will split it. Reprocess a lookback window that overlaps the boundary. **Likely follow-ups** - A user has two devices open at once — does your query interleave them into one session? - How would you cap a session at four hours regardless of activity? - What happens to your session IDs when tomorrow's events arrive? --- ## 47. The business says a subscriber has churned if they've "gone quiet". Turn that into a query, and tell me what you had to decide. *Hard · Occasional* **Short answer.** Churn needs three decisions before any SQL: what counts as activity, how long silence must last, and whether the population is measured at period start or throughout. Write those into the query as explicit filters, since changing any one of them changes the number without changing the code's appearance. The question is not really about SQL. It is about whether you will invent a definition silently or surface the choices, and interviewers ask it because analysts do the former far too often. Three things have to be pinned down. What counts as activity for a meal-kit subscription: an order placed, a box delivered, or logging in? How long is quiet? Thirty days works for a weekly box and is meaningless for a monthly one. And who is in the denominator: everyone active at the start of the period, or everyone who existed at any point during it? Different answers, same query shape, and reports built on different choices will never reconcile. Take activity as a delivered box, silence as 60 days, and the population as subscribers active at the start of the month: ``` WITH last_box AS ( SELECT subscriber_id, MAX(delivered_on) AS last_delivery FROM deliveries GROUP BY subscriber_id ), base AS ( SELECT s.subscriber_id, lb.last_delivery FROM subscribers s JOIN last_box lb ON lb.subscriber_id = s.subscriber_id WHERE lb.last_delivery >= DATE '2026-06-01' - INTERVAL '60 days' AND lb.last_delivery < DATE '2026-06-01' ) SELECT COUNT(*) AS active_at_start, COUNT(CASE WHEN NOT EXISTS ( SELECT 1 FROM deliveries d WHERE d.subscriber_id = base.subscriber_id AND d.delivered_on >= DATE '2026-06-01' AND d.delivered_on < DATE '2026-07-01') THEN 1 END) AS churned_in_june FROM base; ``` The trap in this whole family of queries is right-censoring. A 60-day silence rule cannot classify anyone whose last activity was 20 days ago, because their 60 days is not up. Include them as “not churned” and you understate churn in every recent period, which produces a chart that always looks best at the right-hand edge, exactly where management looks. Either exclude periods that have not fully matured, or mark them provisional on the chart. Two more distinctions worth naming. Voluntary churn, where someone cancels, is an event with a date and is easy. Silent churn is inferred, and the two should not be mixed in one figure without saying so. And a subscriber who cancels while paid up to August has churned commercially in June and behaviourally in August. Say which one you measured. **Likely follow-ups** - A subscriber cancels but their plan runs another two months — when did they churn? - How would you measure churn for a product people use twice a year? - What's the difference between the churn rate you just computed and revenue churn? --- ## 48. The average discount across our stores is 12%, but overall we gave away 19%. How is that possible? *Easy · Occasional* **Short answer.** A simple average treats every store equally. A weighted average accounts for size, so a high-discount store with most of the volume dominates the true figure. Compute it as SUM(discount_amount) / SUM(gross_value), never as AVG of a per-store percentage. Both numbers are arithmetically correct. They answer different questions, and only one of them is about the business. ``` SELECT AVG(discount_pct) AS avg_of_store_rates, -- 12% 100.0 * SUM(discount_amount) / SUM(gross_value) AS overall_rate -- 19% FROM store_month_summary WHERE month = '2026-07-01'; ``` `AVG(discount_pct)` gives each store one vote. A kiosk that sold four sarees at 5% off counts exactly as much as the flagship outlet that moved 40% of the chain’s value at 24% off. The second expression divides total rupees discounted by total rupees of gross sales, which is the figure that actually left the business. The rule underneath: you cannot average a ratio by averaging its results. Ratios have to be recombined from their numerators and denominators. This bites on conversion rates, cost per acquisition, defect rates and anything else expressed as a percentage. Where it becomes a real problem is roll-ups. Someone computes a per-store discount rate in one query, saves it to a summary table, and a later report averages that column. The intermediate step has thrown away the weights and there is no way to recover them. If you are building a summary table, always store the numerator and the denominator alongside the percentage, so downstream aggregation stays possible. If you want to weight by units rather than value, swap the denominator to `SUM(units_sold)` and the numerator to a per-unit measure. Choosing the weight is a business decision, so say which you used when you present the number. **Likely follow-ups** - Which of those two numbers would you put in a board deck, and how would you caption it? - How would you weight by units rather than by value? - Does this same problem affect an average of percentages you've already rounded? --- ## 49. Our servers store timestamps in UTC and the business reports in IST. How do you handle that, and where does it go wrong? *Medium · Occasional* **Short answer.** Store everything in UTC with a timezone-aware type, and convert at query time for display or grouping. Converting after truncating is the common bug: group by the converted timestamp, not the raw one, or your daily boundaries sit at 5:30 AM IST instead of midnight. Getting the order of operations wrong is the whole failure mode here. ``` -- wrong: buckets run 05:30 IST to 05:30 IST SELECT DATE_TRUNC('day', pledged_at) AS day, COUNT(*) FROM tv_pledges GROUP BY 1; -- right: convert first, then truncate SELECT DATE_TRUNC('day', pledged_at AT TIME ZONE 'Asia/Kolkata') AS day, COUNT(*) FROM tv_pledges GROUP BY 1; ``` The first version does not error and the totals look reasonable. Every evening pledge between midnight and 5:30 AM IST lands in the previous day’s bucket, so a late-night telethon push shows up on the wrong date and yesterday’s closed figures keep moving. India’s offset makes this worse than most places. UTC+5:30 is not a whole number of hours, so a mistake never shifts by a clean day boundary that someone might spot. The error lands mid-morning, which looks like a plausible quiet period rather than a bug. Use the timezone-aware type. In PostgreSQL, `timestamptz` stores an absolute instant and converts on output; plain `timestamp` stores wall-clock digits with no offset, and the database has no way to know what they meant. MySQL’s `TIMESTAMP` converts to UTC on write using the session time zone and back on read, which sounds helpful and means the same stored row can display differently for two users. MySQL’s `DATETIME` does no conversion at all. Knowing which type a column is declared as is the first thing to check when numbers disagree. Always name the zone rather than the offset. `'Asia/Kolkata'` carries the historical rules; `'+05:30'` is a fixed number that would be wrong for any region observing daylight saving. India does not, but a report covering Singapore and London does. Two operational points. Filtering a UTC column with `WHERE pledged_at >= '2026-08-01'` compares against midnight UTC, which is 5:30 AM IST, so build the boundaries in IST and convert them. And when audiences span countries, decide whether “daily” means the viewer’s local day or one reporting zone, because a single global figure and a sum of local figures will not match. **Likely follow-ups** - A daily total for 1 August in IST — what UTC range does that actually cover? - Why does a 5:30 offset make this worse than most time zones? - If users are in three countries, what time zone should the report use? --- ## 50. One column holds comma-separated tags like 'organic,imported,fragile'. Get me one row per tag. *Medium · Occasional* **Short answer.** PostgreSQL has unnest with string_to_array, MySQL 8.0 needs JSON_TABLE or a recursive CTE, and SQL Server has STRING_SPLIT. All of them are working around a schema problem: a delimited column should usually be a separate table with one row per tag. PostgreSQL makes this a one-liner: ``` SELECT s.sku_id, TRIM(t.tag) AS tag FROM spice_skus s CROSS JOIN LATERAL unnest(string_to_array(s.tags, ',')) AS t(tag); ``` MySQL 8.0 has no split function, so the usual route is JSON_TABLE after converting the string into a JSON array: ``` SELECT s.sku_id, TRIM(j.tag) AS tag FROM spice_skus s, JSON_TABLE(CONCAT('["', REPLACE(s.tags, ',', '","'), '"]'), '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')) j; ``` SQL Server uses `CROSS APPLY STRING_SPLIT(s.tags, ',')`, which is the cleanest of the three but returns values in no guaranteed order and, in older compatibility levels, gives you no position column. The `TRIM` is not optional. Real data contains `'organic, imported'` with a space after the comma, and without trimming, `' imported'` and `'imported'` count as two different tags. The symptom is a frequency report where a common tag appears twice with the totals split, which is easy to miss when the list is long. Two related landmines. An empty string yields one empty tag rather than zero rows, so filter `WHERE TRIM(tag) <> ''`. And a tag containing the delimiter itself, from someone typing a comma inside a description field, silently becomes two tags with no way to recover the original. The answer the interviewer is usually waiting for sits underneath all this. A comma-separated column is a normalisation failure. You cannot index it, you cannot constrain the values to a known list, filtering with `LIKE '%fragile%'` matches `'non-fragile'`, and every query pays parsing cost. A junction table with one row per SKU and tag makes all of those problems disappear. If you cannot change the schema, at least say that you would, and note that PostgreSQL’s array and JSONB types with GIN indexes are a real middle ground where the delimited form is genuinely convenient. **Likely follow-ups** - A tag has a space after the comma — does your count treat it as a separate tag? - How would you rank tags by frequency once they're split? - Would you argue for changing the schema, and what would you propose? --- ## 51. Finance says last month's revenue was ₹4.2 crore. The product dashboard says ₹3.9 crore. Find the difference using SQL. *Hard · Occasional* **Short answer.** Do not compare totals. Compare at a common grain, ideally transaction level, using a FULL OUTER JOIN on the identifier so you see rows present in one side and absent in the other, plus rows present in both with different values. The pattern of the difference tells you the cause. A single number gap of ₹30 lakh tells you nothing. It could be one large missing transaction or three lakh small differences, and those have opposite explanations. Push the comparison down to the finest grain both sides share. ``` SELECT COALESCE(f.txn_id, p.txn_id) AS txn_id, f.amount AS finance_amount, p.amount AS product_amount, CASE WHEN p.txn_id IS NULL THEN 'finance only' WHEN f.txn_id IS NULL THEN 'product only' WHEN f.amount p.amount THEN 'amount differs' ELSE 'match' END AS status FROM finance_ledger f FULL OUTER JOIN product_revenue p ON p.txn_id = f.txn_id WHERE f.txn_id IS NULL OR p.txn_id IS NULL OR f.amount p.amount; ``` The FULL OUTER is doing the work. An inner join would show only the rows both sides have, hiding exactly the rows that explain the gap. On MySQL, which lacks FULL OUTER JOIN, union a LEFT and a RIGHT join instead. Then read the shape of what comes back, because each pattern has a different cause. Pattern Usual cause Many rows on one side only, clustered in time Different date filter, or a timezone boundary Rows on one side only, scattered Different status filter, such as refunds or test accounts Amounts differ by a consistent ratio Tax inclusive versus exclusive, or a currency conversion Amounts differ on a few rows only Late corrections applied to one system Totals match, breakdown does not Rows allocated to different periods, not missing Before running any of this, check the four boundary definitions, because most mismatches live there rather than in the data: the date range, the timezone, which statuses each side counts, and whether the amount is gross or net of tax, refunds and discounts. The important thing to say at the end is that “wrong” is usually the wrong frame. Finance counting invoiced revenue and product counting recognised revenue are both correct and will never agree. The output of a reconciliation is a documented, quantified explanation of the difference, not a single blessed number. Once you have it, write the reconciliation as a scheduled query so the gap is monitored rather than rediscovered every quarter. **Likely follow-ups** - The gap turns out to be refunds — is either report wrong? - How would you stop this from recurring next month? - What if the totals matched but the monthly breakdown didn't? --- ## 52. You've been doing this analysis in Excel. When would you tell your manager it needs to move into SQL? *Easy · Occasional* **Short answer.** Move to SQL when the work repeats, when the data outgrows what a sheet can hold reliably, or when correctness depends on nobody typing in the wrong cell. Excel stays better for one-off exploration, manual adjustments and putting a number in front of someone quickly. The question is about judgement, not loyalty to a tool. An answer that treats Excel as beneath you reads badly, because most businesses run on it. Four signals that the work has outgrown a spreadsheet. It repeats. Anything rebuilt every Monday should be a query, because the effort is spent once and the definition stops drifting between people. The volume is unsafe. Well before the row limit, a sheet with several lakh rows and nested lookups becomes slow, and slow is when people start taking shortcuts like pasting values over formulas. Correctness matters and the audit trail does not exist. A formula dragged one row short, a filter left applied during a copy, a hardcoded number typed over a cell: none of these leave a trace. A query is a text file that can be read, diffed and reviewed. The logic has grown past what a formula can express clearly. Joining four sources, deduplicating, and ranking within groups is routine SQL and a nightmare of helper columns in a sheet. Where Excel remains the better tool: genuinely one-off questions, work involving judgement that has to be applied per row, financial models built around scenarios, and anything a stakeholder wants to poke at themselves. Forcing those into SQL creates a query nobody but you can modify, which is its own failure. The honest answer for most teams is a split. Compute in SQL, export the result, present and adjust in the sheet. Keep the calculation in one place and let people work with the output. One practical warning worth mentioning: a spreadsheet process usually encodes rules nobody has written down, sitting in a manual override column or a filter someone applies from memory. Migrating without extracting those rules produces a query that is technically correct and disagrees with the number everyone trusts. Reconcile the new output against the old sheet for a few cycles before switching anyone over. **Likely follow-ups** - The manager wants to keep editing the numbers by hand — how do you handle that? - Where would you still choose Excel over SQL? - How would you move an existing spreadsheet process across without breaking anyone's workflow? --- More Data Analyst sets: https://codeayan.com/get-hired/data-analyst All interview prep: https://codeayan.com/get-hired