A query needs the top-3 highest-paid employees per department, with ties sharing a rank and NO gaps in the sequence. A candidate writes:
SELECT * FROM (
SELECT emp_id, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rk
FROM emp) t
WHERE rk <= 3;
What is the defect relative to the stated requirement?
- A. Nothing; RANK() correctly gives gapless top-3 with shared ranks for ties
- B. RANK() leaves gaps after ties (e.g. 1,1,3), so a tie at rank 1 drops rank 2 rows; DENSE_RANK() is required for gapless ranking ✓
- C. The WHERE rk <= 3 is illegal because you cannot filter a window result even inside a subquery
- D. ROW_NUMBER() should be used because RANK() cannot break ties at all
Correct answer: B. RANK() skips numbers after ties (1,1,3,...), so 'no gaps' plus 'ties share a rank' requires DENSE_RANK(); the subquery wrapper itself is correct.
You must compute a month-to-date running total of daily revenue that RESETS at the start of each calendar month. Which window specification is correct?
- A. SUM(rev) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- B. SUM(rev) OVER (PARTITION BY DATE_TRUNC('month', d) ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ✓
- C. SUM(rev) OVER (ORDER BY d RANGE BETWEEN INTERVAL '1 month' PRECEDING AND CURRENT ROW)
- D. SUM(rev) OVER (PARTITION BY d ORDER BY DATE_TRUNC('month', d))
Correct answer: B. Partitioning by the truncated month restarts the running sum each month, and UNBOUNDED PRECEDING..CURRENT ROW accumulates within that partition.
On a fact table with 200M rows and a dimension with 5K rows, an analyst rewrites `WHERE status NOT IN (SELECT status FROM excluded)` where the subquery column is nullable and contains one NULL. What happens?
- A. The query returns rows where status differs from the listed values, ignoring the NULL
- B. The query returns zero rows because NOT IN with any NULL in the subquery makes the predicate never TRUE ✓
- C. The optimizer silently converts NOT IN to NOT EXISTS, so the NULL is harmless
- D. It errors out at parse time due to the NULL in the subquery
Correct answer: B. NOT IN evaluates to UNKNOWN when the list contains NULL (status <> NULL is UNKNOWN), so no row qualifies and the result is empty.
An A/B test targets a baseline conversion of 4% with a minimum detectable effect of a 0.4 percentage-point absolute lift, alpha=0.05 (two-sided), power=0.8. Traffic gives you enough users to detect only a 0.8pp lift in the planned 2 weeks. What is the correct trade-off reasoning?
- A. Halving the MDE from 0.8pp to 0.4pp roughly quadruples the required sample size, so you must extend runtime ~4x or accept the larger MDE ✓
- B. Halving the MDE doubles the required sample size, so a 1-week extension suffices
- C. Lowering alpha to 0.10 will let you detect the 0.4pp effect with the same sample
- D. Increasing power to 0.9 reduces the required sample, letting you keep 2 weeks
Correct answer: A. Required n scales with 1/MDE^2, so cutting the detectable effect in half multiplies sample size (and thus runtime) by about four.
Aggregated across a site, Variant B has a higher overall conversion than A. Split by device, A beats B on both mobile AND desktop. Which condition MOST directly produces this reversal?
- A. A coding bug in the metric definition; segment and aggregate must always agree
- B. Sample-ratio mismatch inflating B's denominator only in aggregate
- C. Unequal device mix between arms acting as a confounder — B got proportionally more of the higher-converting device ✓
- D. A Type I error caused by peeking at the results early
Correct answer: C. This is Simpson's paradox: a confounding variable (device mix) differing across arms reverses the aggregate direction versus every subgroup.
Comparing conversion counts between control and treatment (each user is 'converted' or 'not'), with ~50k users per arm, which test is the standard, correct choice?
- A. Paired t-test on the per-user binary outcomes
- B. Two-proportion z-test / chi-squared test of independence on the 2x2 contingency table ✓
- C. One-way ANOVA across the two arms
- D. Wilcoxon signed-rank test on conversion rates
Correct answer: B. Two independent groups with a binary outcome call for a two-proportion z-test (equivalently a chi-squared test on the 2x2 table); a paired test is wrong because subjects are not matched.
A p-value of 0.03 from an A/B test is reported. Which interpretation is CORRECT?
- A. There is a 3% probability that the null hypothesis is true
- B. If the null were true, there is a 3% chance of observing data at least as extreme as this ✓
- C. The treatment effect is practically significant and worth shipping
- D. There is a 97% probability the treatment is better than control
Correct answer: B. A p-value is P(data at least this extreme | null true); it is not the probability the null is true nor a statement about practical significance.
An OLS demand model shows a very high R^2, but the coefficient on 'price' flips sign and its standard error explodes when 'discounted_price' (nearly collinear with price) is added. VIF for both exceeds 30. What is the most appropriate fix given you want to keep both correlated predictors?
- A. Drop the observation with the highest leverage to stabilize the estimates
- B. Apply Ridge (L2) regularization, which shrinks correlated coefficients and stabilizes estimates under multicollinearity ✓
- C. Switch to a higher-order polynomial to increase R^2 further
- D. Use a chi-squared test to decide which predictor to drop
Correct answer: B. Ridge regression penalizes coefficient magnitude and handles multicollinearity by shrinking correlated coefficients, stabilizing estimates without forcing a variable out.
You must compute per-driver 'acceptance rate' from a trips table with statuses: requested, accepted, cancelled_by_rider_before_accept, cancelled_by_driver, completed. Which denominator gives the correct acceptance rate?
- A. accepted / count(all rows) including cancelled_before_accept
- B. accepted / (requests that were actually offered to the driver, i.e. excluding rider-cancelled-before-accept) ✓
- C. accepted / completed
- D. completed / accepted
Correct answer: B. Acceptance rate is accepted divided by requests the driver could act on; trips cancelled by the rider before the driver could accept were never offered and must be excluded from the denominator.
A gaps-and-islands problem: find consecutive-day login streaks per user. The classic trick subtracts a ROW_NUMBER() from the date. Why does `login_date - ROW_NUMBER() OVER (PARTITION BY user ORDER BY login_date)` identify a streak?
- A. It returns the streak length directly
- B. Within a run of consecutive dates, date and row number increment in lockstep, so their difference is constant and can be used as a GROUP BY key for each island ✓
- C. It only works if there are no duplicate dates and the dates have gaps
- D. It ranks the streaks so the longest gets rank 1
Correct answer: B. For consecutive dates both the date and the row number increase by 1 each step, so their difference stays constant within an island and changes at each gap, serving as a grouping key.