# Experiment Impact > Which experiments moved the needle? Settle the standings inside every variant. Canonical URL: 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. ## Tables ### experiments - exp_id INTEGER - exp_name TEXT - variant TEXT - user_id INTEGER - outcome REAL - created TEXT - platform TEXT Sample rows (10 of 200): | exp_id | exp_name | variant | user_id | outcome | created | platform | | --- | --- | --- | --- | --- | --- | --- | | 1053 | checkout_v3 | variant_a | 100 | 11.3 | 2026-02-02 | android | | 1106 | onboard_flow | variant_b | 197 | 22.6 | 2026-03-03 | web | | 1159 | pricing_tier | variant_c | 294 | 33.9 | 2026-04-04 | iOS | | 1212 | search_rank | holdout | 391 | 45.2 | 2026-05-05 | Android | | 1265 | rec_model_b | control | 488 | 56.5 | 2026-06-06 | NULL | | 1318 | notif_freq | variant_a | 585 | NULL | 2026-07-07 | WEB | | 1371 | home_layout | variant_b | 682 | 79.1 | 2026-08-08 | NULL | | 1424 | new_feed_algo | variant_c | 779 | 90.4 | 2026-09-09 | ios | | 1477 | checkout_v3 | holdout | 876 | 1.7 | 2026-01-10 | android | | 1530 | onboard_flow | control | 973 | 13 | 2026-02-11 | web | ## Expected output First 5 of 40 rows: | exp_name | variant | avg_out | participants | rnk | tier | | --- | --- | --- | --- | --- | --- | | notif_freq | control | 91 | 2 | 1 | 1 | | search_rank | holdout | 71.2 | 4 | 1 | 1 | | onboard_flow | variant_a | 93.8 | 2 | 1 | 1 | | checkout_v3 | variant_b | 77.43333333333332 | 6 | 1 | 1 | | new_feed_algo | variant_c | 92.4 | 4 | 1 | 1 | ## 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 **Two standings, one aggregation** ```sql 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 ``` > **Trick to solving** > > The phrase 'one plus the number of experiments with a strictly higher average' is not a hint toward a window function, it is the subquery itself. Write the correlated COUNT, scope it to the same variant, add one, and the competition standing falls out with no ambiguity about which ranking function to reach for. The tier standing is then the only place `DENSE_RANK` belongs. > **Common pitfall** > > The trap is collapsing both standings into one number, or computing the competition standing with `DENSE_RANK`. Two experiments tied at slot 2 should be followed by slot 4, not slot 3. The averages match, the participant counts match, and the query looks right, but the competition column silently diverges on exactly the variants that contain a tie. **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. > **Interviewers watch for** > > The tell of a senior candidate is that they pause on the tie wording and state out loud that the two standings differ only on ties, naming gaps-after-tie versus consecutive. They also confirm both standings are scoped to variant alone before typing anything. > **Performance insight** > > At three million rows the cost is the aggregation, not the standings. The GROUP BY collapses to roughly sixty names times four variants, a few hundred rows, before either standing is computed. The correlated subquery then runs against that tiny materialized set, and the `DENSE_RANK` sorts a few hundred rows. A composite index on (variant, `exp_name`, outcome) lets the engine stream the aggregate without a separate sort. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/experiment_impact) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.