Data Scientist Interview Questions — SQL

The deciding factor is data movement. Every row you pull crosses a network, gets deserialised, and occupies memory on one machine. Aggregating two hundred crore telematics events down to one row per vehicle in SQL moves a few lakh rows instead of a few billion, and the warehouse does the reduction in parallel across nodes that pandas cannot match on a single box.

Do in SQL: filtering, joins, GROUP BY aggregation, window functions, deduplication, date bucketing, anything that shrinks the data.

Do in pandas: fitted transformations where the parameters come from the training set, statistical tests, plotting, model fitting, and iterative exploration where you are reshaping the same modest frame twenty times in ten minutes.

The pattern that works is a coarse cut in SQL and refinement in memory. Aggregate to the modelling grain, pull the result, iterate freely.

Two things worth raising in an interview.

Fitted transformations belong on the pandas side, and this is where leakage creeps in. Standardising a column by its mean and standard deviation is trivially expressible in SQL, and if you compute those statistics over the full table including your holdout period, you have leaked. Compute them on the training split, keep the fitted object, apply it to both.

Production changes the calculus entirely. Feature logic living in a notebook has to be reimplemented for scoring, and the two implementations drift. If the same features must be computed at serving time, writing them in SQL once and using them for both training and scoring removes an entire class of training-serving skew. That argument often outweighs convenience during exploration.

One practical caution: verify equivalence when you move logic between the two. Null handling differs. SQL’s AVG ignores NULLs; a pandas mean does too by default, but COUNT(*) versus len(df) diverges the moment a join padded a row. Reconcile row counts and a couple of totals before trusting the migration.

Start with the query that breaks:

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

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

The logical sequence is:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Now the trap, and it is a common one:

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

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

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

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

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

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

MySQL has no FULL OUTER JOIN. You emulate it:

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

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

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

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

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

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

The fix depends on what you meant.

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

For daily mandi arrivals:

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

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

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

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

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

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

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

The CTE version:

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

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

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

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

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

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.

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

For a supermarket’s daily till log:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Three practical differences follow from that.

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

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

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

They compose, and the combination is genuinely useful:

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

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

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

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

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

Three things to have ready when this is asked.

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

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

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

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

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

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

Finding them:

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

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

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

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

The delete, in PostgreSQL:

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

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

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

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

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

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

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

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

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

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

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

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

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

The naive version:

SELECT * FROM claim_intimations ORDER BY RANDOM() LIMIT 10000;

Correct and expensive. It assigns a random value to every row and sorts the entire table to find the smallest ten thousand. On a few lakh rows, fine. On a few crore, it is a full sort you are paying for to keep 0.01% of the output.

PostgreSQL’s TABLESAMPLE is far cheaper because it samples physical blocks rather than rows:

SELECT * FROM claim_intimations TABLESAMPLE BERNOULLI (2) REPEATABLE (42);

REPEATABLE fixes the seed, so the same sample comes back every run against unchanged data. BERNOULLI evaluates each row independently and is closer to true random; SYSTEM picks whole blocks and is faster but can be biased when rows are clustered by something correlated with what you are studying.

MySQL has no TABLESAMPLE. The usual route is ORDER BY RAND() with RAND(42) for a seed, with the same full-sort cost.

The approach that holds up best is hashing a stable key rather than seeding a generator:

SELECT * FROM claim_intimations
WHERE MOD(ABS(HASHTEXT(claim_ref)), 100) < 2;

Two properties fall out of this. The sample is reproducible with no seed at all, because the hash of a claim reference never changes. And it is stable as data grows: a new claim either hashes into the sample or does not, and no existing member is displaced. A seeded ORDER BY RANDOM() reruns differently once the table changes, which is the failure people discover a month into an experiment.

The gotcha worth naming: sampling rows is not sampling entities. Ten thousand random claims over-represent policyholders with many claims, so any per-policyholder statistic computed from that sample is biased. Hash the policyholder ID instead, and take all claims belonging to the selected ones.

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

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

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

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

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

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

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.

Start by fixing two things before writing SQL: the grain, one row per customer, and the observation date, the moment from which the model is allowed to see history. Everything else follows.

WITH obs AS (SELECT DATE '2026-07-01' AS as_of),
features AS (
  SELECT e.customer_id,
         COUNT(*)                                                    AS events_total,
         COUNT(DISTINCT DATE(e.occurred_at))                         AS active_days,
         SUM(CASE WHEN e.event_type = 'quote_request' THEN 1 ELSE 0 END) AS quote_requests,
         SUM(CASE WHEN e.event_type = 'callback'      THEN 1 ELSE 0 END) AS callbacks,
         MAX(e.occurred_at)                                          AS last_event_at,
         MIN(e.occurred_at)                                          AS first_event_at,
         AVG(e.quoted_amount)                                        AS avg_quote
  FROM broker_events e, obs
  WHERE e.occurred_at < obs.as_of
  GROUP BY e.customer_id
)
SELECT c.customer_id,
       COALESCE(f.events_total, 0)   AS events_total,
       COALESCE(f.quote_requests, 0) AS quote_requests,
       f.avg_quote,
       (SELECT as_of FROM obs) - DATE(f.last_event_at)  AS days_since_last_event,
       DATE(f.first_event_at)                           AS tenure_start
FROM customers c
LEFT JOIN features f ON f.customer_id = c.customer_id;

The LEFT JOIN from the customer list is the structural point. Aggregating the event table alone gives you only customers who did something, and if your target is churn or conversion, the silent ones are precisely the population you cannot afford to drop. They belong in the table with zeros.

Zero and NULL are different and the distinction should be deliberate. COALESCE(events_total, 0) is right, because no events genuinely means zero events. avg_quote should stay NULL, because a customer who never requested a quote does not have an average of zero; imputing zero there tells the model something false. Decide per column, and document it.

Four families cover most tabular feature work: counts and sums by event type, recency in days since last occurrence, tenure since first occurrence, and ratios between counts. Ratios need a guarded denominator, since a customer with one event produces divide-by-zero or a meaningless value.

The failure that damages models rather than just the table: a feature computed without the observation cut-off. One MAX(occurred_at) that forgot the WHERE e.occurred_at < as_of reads the future, and it will not show up as an error, only as validation performance you cannot reproduce in production.

High-cardinality categoricals do not belong as two hundred conditional-count columns. Keep the top ten by frequency, bucket the rest, and handle the long tail with target or frequency encoding computed on the training split only.

Rolling features are where row-based and date-based frames diverge in a way that quietly corrupts a model.

WITH daily AS (
  SELECT account_id, txn_date, SUM(units) AS units
  FROM commodity_trades
  GROUP BY account_id, txn_date
),
grid AS (
  SELECT a.account_id, d.cal_date,
         COALESCE(t.units, 0) AS units
  FROM accounts a
  CROSS JOIN calendar d
  LEFT JOIN daily t ON t.account_id = a.account_id AND t.txn_date = d.cal_date
  WHERE d.cal_date BETWEEN DATE '2026-01-01' AND DATE '2026-07-01'
)
SELECT account_id, cal_date,
       SUM(units) OVER (PARTITION BY account_id ORDER BY cal_date
                        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)  AS units_7d,
       SUM(units) OVER (PARTITION BY account_id ORDER BY cal_date
                        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS units_30d
FROM grid;

Two design choices are carrying the weight here.

The grid. Without it, an account that traded on the 3rd and the 19th has two rows, and a row-based frame of six preceding rows spans weeks. The cross join with the calendar guarantees one row per account per day, so the window means what its name says. RANGE with an interval makes it robust even if the grid has holes, but building the grid also gives you observation rows on quiet days, which the model needs.

The frame boundary. CURRENT ROW includes today’s activity. If your label is defined on the same day, that is leakage: the feature knows about the transaction the label is derived from. Shift to RANGE BETWEEN INTERVAL '7 days' PRECEDING AND INTERVAL '1 day' PRECEDING so the window ends the day before the observation. Ask what the label horizon is before choosing, because this single boundary decision separates a model that works from one that scores well offline and does nothing live.

RANGE with a date interval is PostgreSQL and Oracle. MySQL 8.0 supports RANGE with numeric offsets but not date intervals, so with a complete grid you fall back to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which is equivalent only because the grid guarantees one row per day. Say that dependency out loud; it is the kind of detail that breaks when someone later removes the grid to save space.

Ratios of the two windows are often stronger features than either alone. Seven-day units over thirty-day units captures acceleration, and it needs a NULLIF on the denominator for dormant accounts.

SELECT reader_id,
       SUM(CASE WHEN genre = 'literary'   THEN minutes ELSE 0 END) AS mins_literary,
       SUM(CASE WHEN genre = 'thriller'   THEN minutes ELSE 0 END) AS mins_thriller,
       SUM(CASE WHEN genre = 'devotional' THEN minutes ELSE 0 END) AS mins_devotional,
       SUM(CASE WHEN genre = 'academic'   THEN minutes ELSE 0 END) AS mins_academic,
       SUM(minutes)                                                AS mins_total
FROM audiobook_listens
WHERE listened_on < DATE '2026-07-01'
GROUP BY reader_id;

One row per reader, one column per genre, which is the shape a model expects.

Zero versus NULL is a modelling decision here, not a formatting one. ELSE 0 says the reader listened to nothing in that genre, which is true and is what a tree or a linear model should see. Dropping the ELSE gives NULL, which most libraries treat as missing and impute, and imputing a mean minutes value for a genre someone deliberately never touched is worse than the honest zero. For an aggregate like an average rating per genre, the reverse holds.

The structural problem is that SQL cannot decide its own output columns. Your training pivot has four genres because those were the four present in the training window. Production data contains a fifth, and the query silently omits it, or the pipeline generating the SQL adds a column your model has never seen and the matrix width no longer matches. Either failure produces an error at scoring time if you are lucky and wrong predictions if you are not.

Two ways to handle it. Freeze the category list as part of the model artefact, generate the SQL from that list, and bucket anything unseen into an other column that exists in both training and scoring. Or pivot after loading, using a one-hot encoder fitted on the training data, which raises the same question but at least fails loudly.

High cardinality kills this approach. Two hundred genres means two hundred sparse columns and a matrix that is mostly zeros. Keep the top ten or fifteen by volume, aggregate the rest into other, and reach for target encoding fitted on the training split when the tail genuinely carries signal.

Adding the row total lets you convert every column to a share afterwards, which is often a better feature than raw minutes because it separates preference from volume.

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.

For a handful of columns, write it out:

SELECT COUNT(*) AS rows_total,
       COUNT(*) - COUNT(soil_ph)         AS null_soil_ph,
       COUNT(*) - COUNT(yield_quintal)   AS null_yield,
       ROUND(100.0 * (COUNT(*) - COUNT(soil_ph)) / COUNT(*), 2) AS pct_null_soil_ph
FROM plot_surveys;

COUNT(column) skips nulls, so subtracting it from COUNT(*) gives the null count without any CASE expression.

For a hundred columns, generate the SQL rather than typing it. PostgreSQL exposes the column list in information_schema.columns, and a short query producing the text of the audit query is faster and less error-prone than hand-writing a hundred expressions. Some engines also expose per-column null fractions in their statistics catalogs, which is approximate but instant.

The percentage alone is the least interesting output. Three follow-up cuts matter more.

When. Group the null rate by month. A column that is 30% null overall but 0% before March and 95% after is not a missing-data problem, it is a broken pipeline or a removed form field, and treating it as random missingness will mislead the model badly.

Where. Group the null rate by segment. If soil pH is missing almost entirely for rainfed plots, the missingness carries information about the plot type, and imputing a mean erases it.

Together. Columns that go null in the same rows point at one upstream source failing rather than several independent gaps.

Nulls are also not the only form of missing. A column can be fully populated and useless: zeros standing in for unknown, 'NA' or '-' as text, a default date of 1900-01-01, or a single value repeated across every row. Check distinct counts and the most frequent values alongside the null audit, or you will conclude a column is complete when it carries no information at all.

What to do about a 40% null column depends on the mechanism, which is why the audit comes before the decision. If the nullness itself is predictive, an explicit missing indicator often beats both dropping and imputing.

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.

The first thing to run against any table you plan to model on:

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT tank_id) AS distinct_tanks
FROM aquaculture_harvests;

If those disagree and your grain is meant to be one row per tank, stop and find out why before writing a single feature.

Three distinct damages follow from duplicates.

The reweighting is the subtle one. A row appearing four times contributes four times the gradient, so the model fits that observation harder than the data justifies. If duplication is not uniform across the target classes, and it rarely is, you have also shifted the class balance without meaning to.

Count features inflate directly. A duplicated harvest event doubles the harvest count for that tank, and every ratio built on it is wrong.

The leakage is the one that fools you. The same row landing in both train and test means the model has memorised an exact answer it is then scored on. Validation performance looks excellent, production performance does not resemble it, and there is nothing in the metrics to indicate why.

Find them at the grain you actually care about, not on whole-row equality:

SELECT tank_id, harvest_date, COUNT(*) AS copies
FROM aquaculture_harvests
GROUP BY tank_id, harvest_date
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Whole-row duplicates are easy. The dangerous case is rows that share the modelling key and differ in a payload column, because deduplication then means choosing, and the choice changes the labels. Look at a few of those pairs before deciding. If they differ because one is a correction of the other, keep the later one. If they differ because your key is not actually the grain, the fix is a different key, not deduplication.

The question worth asking is where the duplicates came from. A join that fanned out, an at-least-once delivery pipeline, and a genuinely repeating business event all look identical in the output and need completely different responses. Deduplicating in your query when the cause is upstream means every other consumer of that table is still wrong.

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.

WITH bounds AS (
  SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY repair_cost) AS q1,
         PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY repair_cost) AS q3
  FROM turbine_repairs
  WHERE repair_cost IS NOT NULL
)
SELECT r.repair_id, r.repair_cost,
       CASE WHEN r.repair_cost < b.q1 - 1.5 * (b.q3 - b.q1) THEN 'low'
            WHEN r.repair_cost > b.q3 + 1.5 * (b.q3 - b.q1) THEN 'high' END AS flag
FROM turbine_repairs r
CROSS JOIN bounds b
WHERE r.repair_cost < b.q1 - 1.5 * (b.q3 - b.q1)
   OR r.repair_cost > b.q3 + 1.5 * (b.q3 - b.q1);

PERCENTILE_CONT exists in PostgreSQL, Oracle and SQL Server. MySQL has no percentile aggregate, so you rank the rows with ROW_NUMBER and pick the quartile positions manually.

The reason to prefer IQR over a three-sigma rule is circularity. A handful of extreme values pull the mean toward themselves and inflate the standard deviation, which widens the threshold, which lets the outliers pass. Quartiles do not move much when the tails are extreme, so the boundary stays where it should.

The 1.5 multiplier is a convention from symmetric distributions and not a law. Repair costs are strongly right-skewed, and on such a distribution IQR flags a large chunk of the upper tail as outliers when those values are perfectly normal for the process. On skewed data, either work on a log scale, use raw percentile cut-offs such as the 1st and 99th, or use an asymmetric adjustment. Saying this unprompted matters more than the query.

What to do with what you find is a separate decision, and interviewers listen for whether you treat it as one. A repair cost of ₹0 is almost certainly a data error and should be excluded or corrected. A repair cost of ₹42 lakh on a gearbox replacement is real and rare, and dropping it teaches the model that such events do not happen. Winsorising at the 1st and 99th percentiles keeps the row and limits its leverage, which suits linear models. Tree models handle extremes reasonably and often need no treatment at all.

Compute the bounds on the training split only. Quartiles derived from the full dataset, holdout included, are a small but real leak.

Global bounds also hide segment structure. A cost that is extreme for a small onshore unit is unremarkable for an offshore one, so PARTITION BY the segment when the segments differ in scale.

The arithmetic version works everywhere:

SELECT FLOOR(prep_minutes / 5) * 5      AS bucket_start,
       FLOOR(prep_minutes / 5) * 5 + 5  AS bucket_end,
       COUNT(*)                         AS orders
FROM tiffin_deliveries
WHERE prep_minutes IS NOT NULL
GROUP BY 1, 2
ORDER BY 1;

Integer division would collapse the buckets, so make sure the divisor forces the right type in your engine. PostgreSQL also has width_bucket(prep_minutes, 0, 120, 24), which handles the boundaries and gives you overflow buckets at both ends.

The floor form puts boundary values in the upper bucket: a 15-minute delivery lands in the 15-to-20 bar, not 10-to-15. That is a defensible convention and it must be stated, because half the confusion in histogram review meetings comes from two people assuming opposite conventions and comparing counts.

Two problems this simple version has.

Empty buckets vanish. If no delivery took between 45 and 50 minutes, that bar has no row and the chart closes the gap, making the distribution look continuous where it is not. Generate the bucket list and left join the counts onto it if the shape matters.

Skew wrecks equal-width bucketing. Most deliveries cluster between 10 and 30 minutes and a handful take three hours, so you get three tall bars and forty empty ones stretching right. Options: cap the axis and put everything beyond into a final overflow bucket, bucket on LOG(prep_minutes), or switch to equal-frequency buckets with NTILE, which gives every bar the same count and varying width. Equal-frequency answers “where do the boundaries between deciles sit”, which is a different question from “what does the distribution look like”, and choosing between them deliberately is what separates a considered answer.

Bucket count is worth a sentence. Too few hides multimodality, too many turns the histogram into noise. Starting at roughly the square root of the row count, then adjusting by eye, is a reasonable heuristic that nobody should treat as more than that.

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.

Start before the first query. Read the DDL: what the partition key is, what is clustered or sorted, what types the columns are. On a partitioned table, every exploratory query should carry a partition filter from the very first one, or you are scanning a billion rows to look at five.

SELECT * FROM ledger_postings
WHERE posted_on = DATE '2026-08-19'
LIMIT 50;

LIMIT bounds the rows returned, not the work done. It only stops the scan early when the plan can produce rows without processing everything, which a plain filtered scan can and a sort, an aggregate or a large join cannot. SELECT ... ORDER BY amount DESC LIMIT 10 on an unindexed column sorts the entire table before returning ten rows, and people are genuinely surprised by that.

For the row count, ask the catalog rather than the table:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'ledger_postings';

Approximate, instant, and adequate for deciding how to proceed. The same is available through information_schema.tables on MySQL, again as an estimate.

A few habits that keep exploration cheap. Never SELECT * on a columnar warehouse, because it reads every column file. Sample rather than scan when you want a distribution: a hash filter on a key gives a stable, cheap subset. Materialise the filtered slice you are working on into a small table once, then iterate against that instead of hitting the large table twenty times.

Set guardrails before you need them. A statement timeout at the session level turns a runaway query into an error instead of an incident. Many warehouses also let you preview the bytes a query will scan before running it, which takes two seconds and prevents the expensive mistake.

The failure worth naming is not the slow query. It is the query that consumes the shared cluster and slows every scheduled pipeline behind it, and nobody connects the two for an hour. When such an environment exists, use the sandbox or the smaller replica for exploration and reserve the production warehouse for the query you have already decided is correct.

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.

WITH assignment AS (
  SELECT user_id, variant, MIN(assigned_at) AS assigned_at
  FROM experiment_assignments
  WHERE experiment_key = 'onboarding_v3'
  GROUP BY user_id, variant
),
single AS (
  SELECT user_id, MIN(variant) AS variant, MIN(assigned_at) AS assigned_at
  FROM assignment
  GROUP BY user_id
  HAVING COUNT(DISTINCT variant) = 1
),
outcome AS (
  SELECT s.user_id, s.variant,
         MAX(CASE WHEN c.completed_at > s.assigned_at THEN 1 ELSE 0 END) AS converted
  FROM single s
  LEFT JOIN profile_completions c ON c.user_id = s.user_id
  GROUP BY s.user_id, s.variant
)
SELECT variant, COUNT(*) AS users, AVG(converted::numeric) AS conversion_rate
FROM outcome GROUP BY variant;

Four decisions are embedded in that, and each one is a way the analysis goes wrong.

LEFT JOIN, not INNER. An inner join to the conversions table drops every user who did not convert, and conversion rate comes out near 100% in both arms. This is the single most common error in experiment SQL and it is obvious only once you look at the denominator.

Outcomes after assignment. c.completed_at > s.assigned_at excludes conversions that happened before the user entered the experiment. Without it, pre-existing behaviour is attributed to the variant.

One row per user. Assignment logs contain repeats, and a user who somehow appears under both variants cannot be attributed to either. The HAVING COUNT(DISTINCT variant) = 1 drops them. Before you do that, count how many there are: a handful is a logging quirk, a few percent is a broken assignment mechanism and the experiment result is not trustworthy.

The denominator is the assigned population. Analysing only users who were exposed to the feature breaks randomisation, because exposure depends on behaviour that the variant itself may have changed. Intention-to-treat, using everyone assigned, is the defensible default. Say so, and if the team wants a treated-only analysis, flag that it is a different and weaker claim.

Conversion should be binary per user here, not a count of events, or heavy users dominate the rate.

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.

Cumulative donations for a crowdfunding campaign:

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

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

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

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

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

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

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

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

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

Simple random sampling gives the right mix on average and can badly under-serve a small stratum on any single draw. If rural applicants are 6% of your population and you need enough of them to fit a segment-level model, leaving it to chance is not a plan.

Proportional allocation, 5% of each stratum:

WITH ranked AS (
  SELECT a.*,
         ROW_NUMBER() OVER (PARTITION BY locality_type ORDER BY RANDOM()) AS rn,
         COUNT(*)     OVER (PARTITION BY locality_type)                   AS stratum_n
  FROM scholarship_applications a
)
SELECT * FROM ranked
WHERE rn <= CEIL(stratum_n * 0.05);

Equal allocation is the same query with WHERE rn <= 500, giving every stratum the same count regardless of size.

Which one depends on the goal, and the interviewer wants to hear you distinguish them. Proportional preserves the population structure, so estimates from the sample apply to the population directly. Equal allocation gives every stratum enough rows for its own analysis, and you then need weights to say anything about the population as a whole. Analysing an equal-allocation sample as though it were representative is a real and common mistake.

Two edge cases to raise unprompted.

A stratum with forty rows cannot yield five hundred, and the query silently returns all forty. Nothing errors. Check the realised counts before using the sample:

SELECT locality_type, COUNT(*) FROM sample GROUP BY locality_type;

Either take the whole small stratum and note it, or merge it into an adjacent one, but decide deliberately.

ORDER BY RANDOM() inside the window is not reproducible. For anything that has to be rerun, replace it with a hash of the row key, which gives the same ordering every time and stays stable as rows are added.

Multi-column strata multiply fast. Locality crossed with income band crossed with course type is easily sixty cells, several of which will be nearly empty. Keep the stratification variables to the ones that genuinely drive the outcome.

SELECT *,
       CASE WHEN listed_on <  DATE '2026-04-01' THEN 'train'
            WHEN listed_on >= DATE '2026-04-01'
             AND listed_on <  DATE '2026-07-01' THEN 'test'
       END AS split_label
FROM handloom_listings
WHERE listed_on < DATE '2026-07-01';

The reason to prefer this over a random split is that a deployed model predicts forward. Train it on rows randomly scattered through the same period as the test rows and it has seen the market conditions, the seasonal pattern and the price regime it is being scored on. Your offline metric looks strong and the live metric collapses, because live has no access to next month.

Time splitting also surfaces drift rather than hiding it. If the test period performs much worse than the training period, that gap is real information about how fast the relationship decays, and it should inform your retraining cadence.

Three details worth voicing.

The gap between train and test matters when the target has a horizon. If you are predicting whether a listing sells within thirty days, a listing from 28 March has an outcome that resolves in April, inside the test window. Leave a buffer at least as long as the label horizon, or the training labels encode test-period information.

Choose the timestamp carefully. Splitting on the date a row was loaded rather than the date the event occurred puts late-arriving records on the wrong side of the boundary. Use the event time.

Entities spanning both periods are usually fine and sometimes not. A weaver with listings in both halves is realistic, since the deployed model will see returning weavers. But any feature computed over the weaver’s full history, including the test period, is leakage regardless of which side the row sits on.

Store the split label as a column rather than recomputing it from a date each run. Six months later, “before April” means something different if the pipeline reran with a shifted boundary, and your published metrics stop being comparable.

Four things change under you between the first run and the rerun.

Wall-clock functions. WHERE enrolled_on >= CURRENT_DATE - 180 means something different every day it runs. Pass explicit boundaries.

WHERE enrolled_on >= DATE '2026-01-01' AND enrolled_on < DATE '2026-07-01'

Mutable source data. Rows get corrected, late records arrive, and a dimension gets a type 1 overwrite. The query is unchanged and the answer is not. If the source keeps history you can filter to it; if it does not, snapshot the inputs into a versioned table before analysing and point the query at the snapshot.

Unstable ordering. LIMIT 100 without a deterministic ORDER BY returns whatever the engine produces this time, which changes with the plan. Add a unique tiebreaker to every ORDER BY that feeds a limit or a ranking.

Environment. Session time zone, ONLY_FULL_GROUP_BY on MySQL, collation settings and even engine version alter results. Setting time zone explicitly at the top of the script costs one line and removes a genuinely confusing failure mode.

Then record enough to prove the rerun matches. Save the query text under version control, the parameter values used, the run timestamp, the row count, and two or three key totals. When the rerun disagrees, those numbers tell you immediately whether the query changed or the data did, which is otherwise an afternoon of work to determine.

A saved CSV of the output is not reproducibility. It is a record of what you got, with no way to demonstrate how, and no way to extend the analysis without starting over. Keep both.

Interviewers ask this because a result nobody can reproduce is a result nobody can defend. When a regulator, an auditor or a sceptical VP questions a number from March, “the table has changed since then” is not an answer anyone accepts.

The leak is easy to write and hard to see:

SELECT l.seller_id, l.label_date, l.was_suspended, p.avg_rating
FROM suspension_labels l
JOIN seller_profile p ON p.seller_id = l.seller_id;

seller_profile holds current state. For a label dated eleven months ago, avg_rating is today’s rating, which already reflects the collapse in quality that caused the suspension. The model learns that low current ratings predict past suspensions, scores beautifully in validation, and predicts nothing useful about a seller who has not been suspended yet.

The correct form asks what the value was on the label date:

SELECT l.seller_id, l.label_date, l.was_suspended, h.avg_rating
FROM suspension_labels l
LEFT JOIN seller_profile_history h
       ON h.seller_id = l.seller_id
      AND l.label_date >= h.valid_from
      AND l.label_date <  h.valid_to;

This requires the source to keep history, which is the practical crux. If the dimension only holds current state, no join can recover the past, and the honest answers are: start snapshotting now and wait, reconstruct from an event log if one exists, or drop the feature. Building the model on the current-state join and hoping is the option that produces a confident, useless model.

The half-open interval matters. Using BETWEEN valid_from AND valid_to with adjacent versions sharing a boundary date matches two rows and duplicates the training row. Consistent >= from and < to avoids it.

Recording delay is the subtler version. A rating submitted on the 3rd but batch-processed into the profile on the 6th was not available on the 4th, even though the history table dates it to the 3rd. Point-in-time correctness means joining on when the data became knowable, not when the event happened. Feature stores handle this with a separate ingestion timestamp, and doing it by hand means keeping that column and joining on it.

The test for leakage that catches most of these: if a single feature gives suspiciously strong validation performance, check whether its value could have been known at prediction time. Ask that question of every feature, not just the suspicious ones.

WITH RECURSIVE chain AS (
  SELECT referrer_id AS root_id, referred_id, 1 AS depth
  FROM referrals
  WHERE referrer_id = 'U-40218'

  UNION ALL

  SELECT c.root_id, r.referred_id, c.depth + 1
  FROM referrals r
  JOIN chain c ON r.referrer_id = c.referred_id
  WHERE c.depth < 20
)
SELECT root_id, referred_id, depth FROM chain;

Two halves joined by UNION ALL. The anchor above the UNION runs once and seeds the set with direct referrals. The recursive half below joins the edge table back to whatever the previous iteration produced, and it repeats until an iteration produces nothing.

Three things this needs to be production-safe.

The depth cap is not decoration. Referral data with a cycle, someone eventually referring back into their own upline through a data error, makes the recursion generate rows indefinitely. PostgreSQL will keep going until it exhausts memory or disk. WHERE c.depth < 20 bounds it. Better still, carry the visited path and exclude nodes already seen:

SELECT c.root_id, r.referred_id, c.depth + 1, c.path || r.referred_id
FROM referrals r
JOIN chain c ON r.referrer_id = c.referred_id
WHERE NOT r.referred_id = ANY(c.path)

UNION instead of UNION ALL also stops cycles by deduplicating, at the cost of a distinct operation on every iteration.

Support varies. PostgreSQL requires the RECURSIVE keyword, MySQL 8.0 supports it with WITH RECURSIVE, and MySQL 5.7 has nothing, which is why older codebases use stored procedures with loops. SQL Server uses plain WITH and enforces a default recursion limit of 100, which you override with OPTION (MAXRECURSION n).

On cost: each iteration is a join against the edge table, so a wide, deep graph does many passes. An index on referrals(referrer_id) is essential, and without one this degrades badly. For a genuinely large graph with heavy traversal, pulling the edges once into an in-memory graph library beats repeated recursive queries. For a bounded hierarchy read a few times a day, the recursive CTE keeps the logic where the data is and is the simpler system.

SELECT COALESCE(defaulted::text, 'NULL') AS label,
       COUNT(*)                          AS rows,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM microfinance_repayments
GROUP BY 1
ORDER BY rows DESC;

The window function over the grouped rows gives the percentage without a second pass or a subquery.

Three things this should tell you.

How rare the positive class is. At 0.4%, accuracy is a useless metric, since predicting “never defaults” scores 99.6%. Precision, recall and PR-AUC are the ones to report, and the answer should say so.

Whether any class has too few examples to learn from at all. Two hundred positives across forty features is not enough, whatever the percentage says. The absolute count matters more than the ratio.

Whether the label column has nulls. Rows with an unknown label are not negatives, and quietly treating them as such is a common and damaging mistake. Explicitly counting them, as above, forces the question.

Then cut it two more ways. By time, because a label rate that jumps in March usually means a definition or a policy changed, and training across that boundary teaches the model two different things at once. By segment, because a segment with no positives cannot contribute anything to learning the positive class, and if that segment is large it will dominate the loss with easy negatives.

SELECT DATE_TRUNC('month', disbursed_on) AS month,
       AVG(CASE WHEN defaulted THEN 1.0 ELSE 0 END) AS default_rate
FROM microfinance_repayments GROUP BY 1 ORDER BY 1;

One caution on the standard responses to imbalance. Resampling or class weights change the model’s output distribution, so predicted probabilities are no longer calibrated against reality. If a business process consumes the probability rather than the class, that matters, and it needs recalibrating afterwards.

SELECT CORR(hours_studied, exam_percentile)      AS pearson,
       COUNT(*)                                  AS n,
       COVAR_SAMP(hours_studied, exam_percentile) AS covariance
FROM coaching_batch_results;

PostgreSQL and Oracle have CORR, COVAR_SAMP and the related regression aggregates. MySQL and SQL Server do not, so you compute it from components:

SELECT (COUNT(*) * SUM(x*y) - SUM(x) * SUM(y)) /
       (SQRT(COUNT(*) * SUM(x*x) - POWER(SUM(x), 2)) *
        SQRT(COUNT(*) * SUM(y*y) - POWER(SUM(y), 2))) AS pearson
FROM (SELECT hours_studied AS x, exam_percentile AS y
      FROM coaching_batch_results
      WHERE hours_studied IS NOT NULL AND exam_percentile IS NOT NULL) t;

That formula is numerically fragile on large values, because the sums of squares get big and the subtraction loses precision. On a warehouse with a native aggregate, use the native one.

Four things the coefficient does not tell you, and this list is the actual content of the question.

It only sees straight lines. A relationship that rises then falls can return a correlation near zero while being strongly predictive. Always look at the shape, or at least bucket one variable and check the mean of the other per bucket, which you can do in SQL.

It is dominated by extreme points. A dozen rows far from the cloud can move the coefficient from 0.1 to 0.7. Compute it with and without the tails and see whether the story holds.

Nulls are dropped pairwise, so a correlation over 4,000 complete pairs out of 90,000 rows describes a self-selected subset. Report n alongside the coefficient, always.

Aggregation inflates it. Correlating batch-level averages instead of student-level rows typically produces a much higher number, because averaging removes individual variation. This is the ecological fallacy and it is easy to walk into when your table is already summarised.

For a matrix over twenty columns, generate the SQL rather than writing 190 expressions, or pull the columns and use a dataframe. High correlation between two features is a reason to look, not an automatic reason to drop one; whether collinearity matters depends entirely on the model.

Eyeballing the split is not a test. At 20,000 users a 50.9/49.1 split is unremarkable; at 2 crore it is essentially impossible by chance, and the difference between those two conclusions is the whole point of running the check.

Compute the chi-square statistic in SQL:

WITH counts AS (
  SELECT variant, COUNT(DISTINCT user_id) AS n
  FROM experiment_assignments
  WHERE experiment_key = 'search_ranking_b'
  GROUP BY variant
),
totals AS (SELECT SUM(n) AS total FROM counts)
SELECT c.variant, c.n,
       t.total * 0.5                                        AS expected,
       POWER(c.n - t.total * 0.5, 2) / (t.total * 0.5)      AS chi_sq_term
FROM counts c CROSS JOIN totals t;

Sum the chi_sq_term values and compare against the critical value for one degree of freedom, which is 3.84 at the 5% level for a two-variant test. Above that, treat it as a sample ratio mismatch.

Why SRM is treated as fatal rather than as noise: the randomisation is the only thing that licenses causal comparison between the arms. If users are not landing in the arms at the intended ratio, some mechanism is selecting who goes where, and that mechanism is almost certainly correlated with the outcome. Common causes are a bug in the bucketing hash, one variant failing to load and never logging its assignment, bot traffic hitting one arm, or a redirect that drops slow connections disproportionately.

Reweighting does not fix it. You can equalise the counts arithmetically and the selection bias remains, because the missing users are missing for a reason related to their behaviour. The correct response is to find the cause, fix it, and rerun. An experiment with an unexplained SRM should not be shipped on.

Run the check at more than one point. Assignment is the obvious one. Also compare the arms on exposure counts, and on a pre-experiment metric that the variant cannot have influenced. Segmenting the mismatch by platform, app version, region or day usually localises the bug immediately, because a mismatch concentrated on one Android build is a very specific clue.

PostgreSQL uses arrow operators, with a doubled arrow for text output:

SELECT event_id,
       props ->> 'listing_id'                    AS listing_id,
       (props ->> 'bid_amount')::numeric         AS bid_amount,
       props -> 'device' ->> 'os'                AS os
FROM auction_events
WHERE props ->> 'action' = 'bid_placed';

-> returns JSON, ->> returns text. Forgetting the difference and comparing a JSON value to a string literal is the first thing that goes wrong. MySQL uses JSON_EXTRACT(props, '$.bid_amount') with ->> as shorthand for the unquoted form, and SQL Server uses JSON_VALUE(props, '$.bid_amount').

The cast is not optional. Extraction gives you text, so props ->> 'bid_amount' > '9000' compares strings, and string comparison puts ‘900’ above ‘9000’. Cast first, then compare.

The silent failure is the missing key. A typo in the path returns NULL for every row, exactly as it would if the key genuinely were absent. Nothing errors. A filter on that expression matches nothing and looks like a legitimately empty result. Confirm the key exists before trusting the output:

SELECT jsonb_object_keys(props) AS key, COUNT(*)
FROM auction_events GROUP BY 1 ORDER BY 2 DESC;

Mixed types under one key are the other real problem. Event schemas drift, and a key that held a number in March holds a string in June. The cast then fails at runtime on a subset of rows, midway through a long query. PostgreSQL 16 and later offer safe conversion helpers; before that, filter with a regex or a type check on the JSON value before casting.

On performance: JSON extraction is computed per row and a plain B-tree index on the table does not help. PostgreSQL’s jsonb supports GIN indexes for containment queries, and you can index a specific expression such as ((props ->> 'listing_id')). MySQL requires a generated column to index a JSON path.

The judgement to voice: if a key is queried in most analyses, promote it to a real typed column in the transform layer. JSON is right for the sparse, evolving tail of properties and wrong for the fields everyone filters on daily.

Filtering without unnesting is both cheaper and clearer:

-- profiles that list a specific tag
SELECT profile_id FROM freelance_profiles
WHERE 'pytorch' = ANY(skill_tags);

-- profiles sharing any tag with a given set
SELECT profile_id FROM freelance_profiles
WHERE skill_tags && ARRAY['pytorch','jax'];

-- profiles containing all of them
SELECT profile_id FROM freelance_profiles
WHERE skill_tags @> ARRAY['pytorch','jax'];

These are PostgreSQL array operators. A GIN index on skill_tags makes containment and overlap fast, which unnesting cannot benefit from in the same way.

To count tag frequency you do need one row per element:

SELECT tag, COUNT(*) AS profiles
FROM freelance_profiles, UNNEST(skill_tags) AS tag
GROUP BY tag ORDER BY profiles DESC;

Unnesting multiplies rows by array length, so a profile with nine tags becomes nine rows. That is fine for a frequency count and dangerous if you then join to something else or sum a numeric column from the profile, because every value gets counted nine times. Unnest late, aggregate immediately, and do not carry the exploded rows further than necessary.

The three-state problem catches people: a NULL array, an empty array, and an array containing NULL are all different. UNNEST on a NULL array in PostgreSQL produces no rows, so the profile disappears from the result entirely, which silently drops profiles with no tags from any analysis built on the unnested form. Use LEFT JOIN LATERAL UNNEST(...) if those rows must survive. 'x' = ANY(NULL) returns NULL, not false, so it does not match, and NOT ('x' = ANY(tags)) on a NULL array also does not match, which surprises people writing exclusion filters.

Portability is limited. Arrays are a PostgreSQL and BigQuery strength; MySQL and SQL Server have no array type, so the equivalent is a junction table or a JSON array with its own extraction functions.

For feature engineering, arrays usually need flattening into indicator columns anyway. Unnest, count per profile per tag, and pivot the top tags into columns, keeping the long tail in an other bucket.

Exact distinct counting requires remembering every value seen, so memory grows with cardinality and large counts spill to disk. HyperLogLog stores a small fixed-size sketch instead, giving a relative error typically around 1–2% depending on the precision setting.

-- PostgreSQL with the postgres_hll or datasketches extension, BigQuery, Presto:
SELECT APPROX_COUNT_DISTINCT(device_id) FROM podcast_streams;
-- SQL Server 2019+ has APPROX_COUNT_DISTINCT natively

Names vary: APPROX_COUNT_DISTINCT in SQL Server and BigQuery, approx_distinct in Presto and Trino, HLL_CARDINALITY in Redshift. MySQL and stock PostgreSQL have nothing built in.

Where it fits, and where it does not, is the actual question.

Use it for exploratory work where you are deciding whether a cut is worth pursuing, for dashboards where a 1% error in unique listeners changes no decision, and for monitoring where you care about the shape of the trend. Use it especially when the alternative is not running the query at all.

Do not use it where the number is the product. Billing a partner per unique device, reporting a regulated figure, or deduplicating records all need exactness. Also avoid it wherever two systems get reconciled against each other, because a persistent 1% gap will consume days of investigation before someone remembers the approximation.

Two properties worth knowing beyond the error rate.

The error is relative and probabilistic, not a hard bound. Most estimates fall well within the stated standard error, and a given query can be further off. Quoting it as a guarantee is wrong.

Sketches are mergeable, which is the underrated advantage. You can store a sketch per day and union them to get an exact-sized estimate for the month, something exact distinct counts cannot do, since daily distinct counts are not additive. That property makes precomputed sketches genuinely useful in a warehouse, not just a shortcut.

Label the metric as approximate wherever it is displayed. A number that quietly differs from the exact one, with nothing on the dashboard saying so, is how trust in the whole reporting layer erodes.