Drill 6 of 12 in Marketing questions
0

Interview Question Bank · COUNT(DISTINCT) · FILTER · CASE guard

6. A/B test conversion with a sample-size guard

MediumMarketingQuestion #174

Concept: COUNT(DISTINCT) · FILTER · CASE guardshow

Experiment logs often record the same user more than once, so count users with COUNT(DISTINCT user_id), and converters the same way with a FILTER. A rate from a handful of users is noise, so a common guard is CASE WHEN users >= threshold THEN rate END: the row stays in the report, but its rate is NULL until the sample is big enough.

Example (different data):

SELECT menu_version,
  COUNT(DISTINCT diner_id) AS diners,
  CASE WHEN COUNT(DISTINCT diner_id) >= 30
    THEN ROUND(100.0 * COUNT(DISTINCT diner_id) FILTER (WHERE ordered_dessert) / COUNT(DISTINCT diner_id), 1)
  END AS dessert_pct
FROM menu_trials
GROUP BY menu_version;

Your task

Larkspur Roasters is running two site experiments. The analytics team reports conversion per variant but refuses to publish a rate from fewer than 5 users.

For each experiment and variant, return experiment, variant, users (distinct users exposed), converters (distinct users with a converted_at) and conversion_pct (converters as a % of users, rounded to 1 decimal, or NULL when the variant has fewer than 5 users). Sort by experiment, then variant.

Starting Postgres in your browser…

Checking your access…