Data & SQL: real interview questions

Give the clean answer (window functions), mention the classic answers (LIMIT/OFFSET, subquery), and handle ties + NULL explicitly — that's what separates candidates.

Modern answer: DENSE_RANK() OVER (ORDER BY salary DESC), pick rank 2 — DENSE_RANK handles ties correctly (two people at the top salary means rank 2 is the true second-highest value, not the third row). Classic alternatives: SELECT MAX(salary) WHERE salary < (SELECT MAX(salary)) — clean and index-friendly; or ORDER BY salary DESC LIMIT 1 OFFSET 1 — simple but wrong under ties. Say the edge cases out loud: what should happen with duplicates (value vs row semantics), and return NULL gracefully if there's only one salary. Interviewers report they're testing whether you ask 'second-highest value or second row?' — asking that question is passing.

No jargon, no 'null hypothesis' vocabulary. Use the 'if nothing were really going on, how surprising is this result?' framing, and warn what it does NOT mean.

Imagine the change we made actually did nothing. A p-value answers: if that were true, how often would we still see a result this big just from random luck? p = 0.03 means results like ours would show up by chance only about 3 times in 100 — so luck is an unlikely explanation, and we start believing the change did something. What it doesn't mean: it's not the probability we're right, and it says nothing about whether the effect is big enough to matter — a tiny, meaningless lift can have a great p-value with enough data. That's why I always pair it with the effect size: 'how sure are we' and 'how much does it matter' are different questions.

Diagnose it as a many-to-many join (duplicate keys on both sides multiply), show how to detect it, and how to fix (dedupe/aggregate before joining).

That's a fan-out: the join key isn't unique on one (or both) sides, so rows multiply — 3 duplicates joined to 4 duplicates yields 12 rows. Detect it by profiling: SELECT key, COUNT(*) FROM each table GROUP BY key HAVING COUNT(*) > 1. Fix depends on intent: dedupe the dimension side (pick the latest record per key with ROW_NUMBER), pre-aggregate the many side before joining, or accept the grain change deliberately and adjust downstream aggregations. The deeper habit interviewers are probing: always know the grain (one row per what?) of both tables before joining, and sanity-check row counts after every join — silent fan-out is the number-one source of inflated metrics.

Don't be the 'no' police. Separate 'no effect' from 'not enough evidence', quantify what you CAN say (confidence interval), and offer decision options with risks.

First I'd reframe: not-significant doesn't mean 'no effect' — it means we can't distinguish the effect from noise at this sample size. I'd show the confidence interval: if it's [-0.2%, +2.5%], the data is consistent with anything from harmless to quite positive; that's a very different conversation than a tight interval around zero. Then options: (1) ship anyway if the downside bound is acceptable and the strategic case is strong — data isn't the only input; (2) run longer or on more traffic if the decision is high-stakes and reversible later is expensive; (3) ship behind a holdback so we keep measuring after launch. My job is to make the uncertainty legible, not to block — but I'll insist we not describe a non-significant result as 'proven'.

The key insight is WHY it's missing (random vs systematic) changes everything. Then options: drop, impute, model, or add a missingness flag — each with a caveat.

First question: why is it missing? If it's random (a logging blip), dropping or simple imputation is fine. If it's systematic — income missing more often for certain users, sensors failing under load — the missingness itself is signal, and dropping those rows biases everything downstream. Practical toolkit: drop when rare and random; impute median/mode for robustness (never mean on skewed data); model-based imputation when relationships matter; and almost always add an 'is_missing' indicator column so the model can learn from the gap itself. For metrics/dashboards, I prefer showing completeness alongside the number. The interview answer they want: 'it depends on the mechanism, and here's how I'd find out' — then check whether missingness correlates with the outcome.

One line: GROUP BY collapses rows, windows keep them. Then 2-3 concrete cases: running totals, rank-within-group, deduping with ROW_NUMBER.

GROUP BY collapses rows into one per group; a window function computes across the group while keeping every row. So: when I need detail rows AND an aggregate together — each order alongside the customer's running total (SUM OVER ordered by date), each employee with their salary's rank within department (DENSE_RANK PARTITION BY dept), or the classic dedupe: ROW_NUMBER() PARTITION BY user ORDER BY updated_at DESC, keep row 1 for latest-record-per-user. Also period-over-period: LAG(revenue) for last month's value on the same row — no self-join. Rule of thumb I use: if I'm tempted to join a table to an aggregated copy of itself, a window function does it cleaner and usually faster.