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…