Experiment Impact

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

The data science team is auditing how each experiment fared inside the variant buckets it ran in: for every experiment and variant pairing in experiments, take the average outcome and the participant count, ignoring rows where the outcome was never recorded. Within each variant, ordered from the highest average outcome down, give each experiment a competition standing where tied experiments share a slot and a slot equals one plus the number of experiments in that variant with a strictly higher average, plus a tier standing where ties again share a number but the numbers stay consecutive with no gaps so the next lower average is always the next number up. Return the exp_name, variant, average outcome, participant count, competition standing, and tier standing.

Which experiments moved the needle? Settle the standings inside every variant.

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

Expected output

Worked solution and explanation

What this really tests

This is the RANK versus DENSE_RANK distinction made literal, hidden inside experiment analysis. Almost everyone nails the easy half: collapse to (exp_name, variant), average the outcome, count the rows, drop the NULLs. The real probe is that the prompt asks for two standings that disagree only on ties. The competition standing is one plus the number of strictly higher averages, so it skips after a tie. The tier standing compresses ties and stays consecutive. Candidates who treat them as the same number, or who compute the competition standing with DENSE_RANK, silently diverge on exactly the variants that contain a tie, which is precisely where the grader looks.

Build it up

Step 1: Aggregate at the (exp_name, variant) grain

Collapse to the metric grain first. Group experiments by (exp_name, variant) and place WHERE outcome IS NOT NULL before the grouping so a missing outcome lands in neither AVG(outcome) nor COUNT(*). The common slip is filtering after aggregation, which still lets NULL rows inflate the participant count.

Step 2: Express the competition standing as a counting subquery

Now read the competition standing definition literally: one plus the number of experiments in the same variant with a strictly higher average. That sentence is a correlated scalar subquery, not a guess about which window function to use. Count the rows of exp_avgs in the same variant whose avg_out is strictly greater, add one, and you have it. The correlation key is variant alone, so an experiment competes only inside its own bucket.

Step 3: Add the tier standing with DENSE_RANK

The tier standing is the compressed cousin. DENSE_RANK() OVER (PARTITION BY variant ORDER BY avg_out DESC) gives ties the same number while keeping the numbers consecutive, so a tier equals one plus the count of distinct higher averages. Partition by variant alone here too: partitioning by (exp_name, variant) would make every group size one and hand every row a tier of 1.

The solution

WITH exp_avgs AS (
  SELECT exp_name, variant,
         AVG(outcome) AS avg_out,
         COUNT(*) AS participants
  FROM experiments
  WHERE outcome IS NOT NULL
  GROUP BY exp_name, variant
)
SELECT e1.exp_name, e1.variant, e1.avg_out, e1.participants,
       1 + (SELECT COUNT(*) FROM exp_avgs e2
            WHERE e2.variant = e1.variant
              AND e2.avg_out > e1.avg_out) AS rnk,
       DENSE_RANK() OVER (PARTITION BY e1.variant ORDER BY e1.avg_out DESC) AS tier
FROM exp_avgs e1
ORDER BY e1.variant, rnk, e1.exp_name

Competition standing (rnk)

Averages 90, 80, 80, 70 give 1, 2, 2, 4. The 4 says three experiments scored at least as high as the last one, which is one plus the count strictly above it. Gaps appear after a tie.

Tier standing (tier)

The same averages give 1, 2, 2, 3. Ties still share, but the numbers stay consecutive, so a tier counts how many different averages sit above this one, plus one. No gaps.

Common follow-up questions

• How would you return only the top three experiments per variant, and does it matter whether you filter on the competition standing or the tier? (Tests wrapping the query in an outer SELECT with WHERE rnk <= 3 or tier <= 3 and knowing which one the business means.)
• If the business wanted exactly one winner per variant even under a tie, what changes? (Tests knowing `ROW_NUMBER` breaks ties arbitrarily versus the share-a-slot behavior of the other two.)
• How would you show each experiment's gap to the leading average in its variant? (Tests a second window or a correlated subquery comparing across variant scopes.)