How would you approach a medium difficulty SQL exercise combined with probability questions in an interview?
Use the provided experiment exposure and conversion-event data. Write a PostgreSQL query that calculates the probability of conversion for each non-null variant in experiment 1 during January 2026, then ranks variants by that probability.
Output
- One row per variant, with
variant, exposed_exposures, converted_exposures, conversion_probability, and variant_rank.
- Include only January exposures belonging to experiment 1 and exclude null variants. Count an exposure as converted when it has at least one conversion event.
- Sort by
variant_rank ascending, then variant ascending.
Output details
- Restrict results to experiment name
Spotlight Feed in addition to experiment ID 1.
- Count each distinct
exposure_id only once in both exposure and conversion totals.
- Treat an exposure as converted only when its event type is exactly
conversion.
- Round
conversion_probability to 4 decimal places.
- Rank probabilities in descending order, assigning equal probabilities the same rank.
- Interpret January 2026 as exposures on or after
2026-01-01 and before 2026-02-01.
