Satisfaction by Platform

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Walmart

Problem

Give us the average experiment outcome score for the 25-34 age cohort on each platform, with platforms told apart by their labels exactly as our event logs captured them, a missing label included. Our scoring rubric credits only the whole points of each logged outcome, so drop each score's fraction before averaging, and report the average rounded to the nearest whole number.

Satisfaction scores, platform by platform.

Tables

experiments

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

Strip the growth framing and this is a filtered group-average across two tables with one conversion hidden in the order of operations. The score and the label live in experiments, the cohort lives in users.age_bucket, so the join exists only to carry the cohort onto the rows you average. Everyone gets the join. What separates candidates is where the whole-point conversion goes: it has to hit each score before AVG, not the average after it. Put it last and 'Android' reports 40 and 'WEB' reports 49, where the rubric says 39 and 48.

Build it in four moves

Step 1: Bring age_bucket next to the score

Join experiments to users on user_id. An inner join is right: an event only matters if its user can be placed in an age bucket, and users with no matching row have no cohort to belong to.

Step 2: Keep only the 25-34 cohort

Filter u.age_bucket = '25-34' in the WHERE. It runs before the grouping, so it shrinks the set you aggregate, which is also the cheaper plan.

Step 3: Drop each score's fraction before averaging

CAST(e.outcome AS INTEGER) truncates toward zero, so 48.6 becomes 48. A NULL outcome stays NULL through the cast, and AVG skips it, so missing scores never dilute a platform. Every score on this data is positive, which means FLOOR would land on the same values.

Step 4: Average per raw label, then round

Group on e.platform exactly as stored and take ROUND(AVG(...)) over the whole points. For 'android' the scores 11 and 44 average to 27.5, which rounds to 28.

SELECT e.platform, ROUND(AVG(CAST(e.outcome AS INTEGER))) AS avg_outcome_score
FROM experiments e
INNER JOIN users u ON e.user_id = u.user_id
WHERE u.age_bucket = '25-34'
GROUP BY e.platform

Round the raw average

ROUND(AVG(e.outcome)) averages 'Android' to 39.6 and rounds up to 40, and averages 'WEB' to 48.6 and rounds up to 49. The fraction the rubric never awarded pushes both over the line.

Average the whole points

ROUND(AVG(CAST(e.outcome AS INTEGER))) averages 39 and 48 for those two labels. The 0.6 is gone before the mean is taken, so it cannot round anything up.

Common follow-up questions

• Some events have a `NULL` outcome. How would the averages change if the business wanted those counted as zero points? (Tests whether the candidate knows `AVG` skips NULLs and how a default changes the denominator.)
• If product later decides 'android' and 'Android' are the same platform, where would you normalize, and what breaks if you do it in the wrong place? (Probes case-normalization tradeoffs without breaking the output contract.)
• What happens to the whole-point rule if a score can be negative, and would `FLOOR` still match the cast? (Tests whether the candidate knows the cast truncates toward zero while `FLOOR` rounds down.)