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.
A LEFT JOIN unexpectedly drops rows from the left table. The most common cause is:
- A. Using SELECT * instead of naming columns
- B. Placing a filter on the right table in the WHERE clause instead of the ON clause ✓
- C. Missing an ORDER BY
- D. The right table having a primary key
Correct answer: B. A WHERE condition on the right table's column filters out the NULL non-matches, effectively turning the LEFT JOIN into an INNER JOIN; the predicate belongs in ON.
You run an A/B test and get p = 0.03 at alpha = 0.05. Which interpretation is correct?
- A. There is a 3% chance the null hypothesis is true
- B. If the null were true, there is a 3% chance of data this extreme or more ✓
- C. The effect is 3% in size
- D. There is a 97% chance the alternative is true
Correct answer: B. A p-value is the probability of observing data at least as extreme as seen, assuming the null hypothesis is true.
Simpson's paradox occurs when:
- A. A trend present in aggregated data reverses within subgroups ✓
- B. Two variables are perfectly correlated
- C. A sample is too small to be significant
- D. Data contains duplicate rows
Correct answer: A. Simpson's paradox is when an association seen in combined data reverses or disappears once the data is split by a confounding subgroup.
In SQL, SELECT COUNT(*) vs COUNT(col) can differ because:
- A. COUNT(*) is always slower
- B. COUNT(col) ignores rows where col is NULL while COUNT(*) counts all rows ✓
- C. COUNT(*) ignores NULLs
- D. They are always identical
Correct answer: B. COUNT(*) counts every row, but COUNT(col) skips rows where that column is NULL, so results diverge when NULLs exist.
A running total window function returns wrong values on tied dates. The likely fix in the OVER clause is to change the frame specification because:
- A. ORDER BY on a non-unique column with default RANGE frame includes all peer rows in the cumulative sum ✓
- B. ROWS is never allowed with SUM
- C. PARTITION BY is forbidden with ORDER BY
- D. Window functions cannot compute running totals
Correct answer: A. With the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, tied ORDER BY values are treated as peers and summed together; using ROWS frames each row individually.
You must deduplicate keeping the most recent record per customer in SQL. The most reliable single-query approach is:
- A. SELECT DISTINCT customer_id
- B. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) then keep rn=1 ✓
- C. GROUP BY customer_id with SELECT *
- D. DELETE all duplicates blindly
Correct answer: B. ROW_NUMBER partitioned by customer and ordered by recency lets you filter rn = 1 to keep exactly the latest row per customer.
Regarding NULL handling, which expression correctly returns rows where email is NOT set?
- A. WHERE email = NULL
- B. WHERE email != NULL
- C. WHERE email IS NULL ✓
- D. WHERE email <> ''
Correct answer: C. NULL comparisons with = or != yield UNKNOWN, so you must use the IS NULL predicate to test for absence of a value.
When building a cohort retention analysis, why is anchoring each user to their signup-month cohort essential?
- A. It reduces query cost
- B. It allows measuring retention relative to a common starting point across users who joined at different times ✓
- C. It removes the need for a date dimension
- D. It converts the data to wide format automatically
Correct answer: B. Cohorting by signup period normalizes users' timelines so retention can be compared at equivalent tenure (month 1, month 2, etc.) despite different join dates.
A dashboard's total revenue differs from the source system because a fan-out join inflated rows. The root cause is typically:
- A. Joining a fact table to a dimension at a one-to-many grain and then summing the measure ✓
- B. Using COUNT instead of SUM
- C. A missing WHERE clause on dates
- D. Too few columns selected
Correct answer: A. Joining a measure at one grain to a table at a finer grain duplicates the measure across matches, so summing double-counts revenue.
You have imbalanced classes (2% churn). Which single metric is most misleading to report as model quality?
- A. Precision
- B. Recall
- C. Overall accuracy ✓
- D. F1 score
Correct answer: C. With 2% positives, a model predicting 'no churn' for everyone scores 98% accuracy while catching zero churners, making accuracy deceptive.
A query with COUNT(DISTINCT user_id) over a billion-row table is extremely slow. Which approximate technique is commonly used in analytics warehouses to speed this up with bounded error?
- A. Bitmap bloom join
- B. HyperLogLog (APPROX_COUNT_DISTINCT) ✓
- C. Nested-loop dedup
- D. Full table B-tree scan
Correct answer: B. HyperLogLog estimates cardinality with small memory and bounded error, powering APPROX_COUNT_DISTINCT functions.
You observe Simpson's Paradox: a treatment appears beneficial in every subgroup but harmful in the aggregate. What is the usual cause?
- A. A random sampling error
- B. A confounding variable unevenly distributed across subgroups ✓
- C. Too small a sample size
- D. A data type mismatch
Correct answer: B. Simpson's Paradox arises when a lurking/confounding variable's uneven distribution reverses the aggregate trend versus subgroups.
In SQL, what is the key semantic difference between COUNT(col) and COUNT(*) when col has NULLs, inside a GROUP BY with a LEFT JOIN producing unmatched rows?
- A. They are always identical
- B. COUNT(*) counts all rows including unmatched NULL rows; COUNT(col) skips NULLs, yielding a lower count ✓
- C. COUNT(col) counts NULLs as zero
- D. COUNT(*) ignores NULLs
Correct answer: B. COUNT(*) counts every row while COUNT(col) ignores NULLs, so unmatched LEFT JOIN rows inflate only COUNT(*).
When computing a 7-day rolling average that must reset at each store, which windowing specification is correct?
- A. OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
- B. OVER (PARTITION BY store ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) ✓
- C. OVER (PARTITION BY date ORDER BY store)
- D. OVER (ORDER BY store RANGE UNBOUNDED PRECEDING)
Correct answer: B. Partitioning by store isolates each store's series, and the 6-preceding-to-current frame yields a 7-row rolling window.
A correlation coefficient between ice cream sales and drowning deaths is 0.9. What is the most rigorous analyst conclusion?
- A. Ice cream causes drownings
- B. There is likely a confounder such as temperature/season driving both ✓
- C. The correlation is a data error
- D. One variable must be reverse-causing the other
Correct answer: B. High correlation without a causal mechanism typically signals a common confounder (hot weather increases both).
In a star schema, why are fact tables typically kept 'narrow and long' rather than storing descriptive attributes directly?
- A. To enforce primary keys
- B. To reduce storage redundancy and let dimensions be updated independently ✓
- C. Because SQL cannot join wide tables
- D. To avoid using surrogate keys
Correct answer: B. Keeping descriptive attributes in dimension tables avoids duplicating them across millions of fact rows and eases updates.
You need the median of a large column in standard SQL that lacks a MEDIAN function. Which approach is correct?
- A. AVG(column)
- B. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) ✓
- C. MAX(column)/2
- D. COUNT(column)/2
Correct answer: B. PERCENTILE_CONT(0.5) computes the continuous 50th percentile, which is the median.
When a dashboard's daily active users metric suddenly drops 40% but revenue is flat, what is the FIRST thing a rigorous analyst should check?
- A. Immediately alert executives to churn
- B. Whether the tracking/logging pipeline or event definition changed (data quality) ✓
- C. Rebuild the entire ETL
- D. Change the chart type
Correct answer: B. A large metric shift inconsistent with correlated metrics usually signals an instrumentation or pipeline issue, checked before business conclusions.
In hypothesis testing, running 20 independent A/B comparisons each at alpha=0.05 inflates which error, and what corrects it?
- A. Type II error; increase sample size
- B. Family-wise Type I error; a Bonferroni or similar correction ✓
- C. Sampling bias; stratification
- D. Measurement error; log transform
Correct answer: B. Multiple comparisons inflate the family-wise false-positive (Type I) rate, mitigated by corrections like Bonferroni.
A cohort retention curve is flattening after week 4 rather than dropping to zero. What does this asymptote most meaningfully indicate?
- A. The data is corrupted
- B. A stable core of retained ('sticky') users representing product-market fit ✓
- C. Seasonality
- D. That the cohort was too small
Correct answer: B. A flattening retention curve indicates a durable base of loyal users who keep returning, a positive product-market-fit signal.