Quick Overview

This question evaluates SQL-based data manipulation and experimental analytics skills, including attribution of earliest exposures, censoring crossovers, session-based bot qualification, time-windowed conversion and revenue calculations, and exclusion of outage periods in PostgreSQL.

Write SQL for noisy A/B launch metrics

Company: Chime

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

PostgreSQL. Today is 2025-09-01. You’re given the following schema and toy samples: users(user_id INT, country TEXT, signup_date DATE, marketing_channel TEXT) +---------+---------+-------------+------------------+ | user_id | country | signup_date | marketing_channel| +---------+---------+-------------+------------------+ | 1 | US | 2025-08-12 | email | | 2 | CA | 2025-08-13 | paid | | 3 | US | 2025-08-11 | organic | | 4 | GB | 2025-08-17 | paid | +---------+---------+-------------+------------------+ exposures(user_id INT, experiment_id TEXT, variant TEXT, exposure_ts TIMESTAMP, source TEXT) +---------+--------------------+---------+---------------------+--------+ | user_id | experiment_id | variant | exposure_ts | source | +---------+--------------------+---------+---------------------+--------+ | 1 | tracker_vlaunch_v1 | control | 2025-08-12 10:00 | web | | 2 | tracker_vlaunch_v1 | B | 2025-08-13 09:35 | email | | 2 | tracker_vlaunch_v1 | control | 2025-08-14 10:18 | web | <- crossover | 3 | tracker_vlaunch_v1 | B | 2025-08-11 08:02 | paid | | 4 | tracker_vlaunch_v1 | B | 2025-08-17 15:10 | web | +---------+--------------------+---------+---------------------+--------+ sessions(user_id INT, session_start TIMESTAMP, device TEXT, is_bot BOOLEAN) +---------+---------------------+---------+--------+ | user_id | session_start | device | is_bot | +---------+---------------------+---------+--------+ | 1 | 2025-08-12 10:00 | ios | false | | 2 | 2025-08-13 09:30 | web | false | | 2 | 2025-08-14 10:10 | web | true | | 3 | 2025-08-11 08:00 | android | false | | 4 | 2025-08-17 15:00 | web | false | +---------+---------------------+---------+--------+ orders(order_id INT, user_id INT, order_ts TIMESTAMP, revenue NUMERIC(10,2), refunded BOOLEAN) +----------+---------+---------------------+---------+----------+ | order_id | user_id | order_ts | revenue | refunded | +----------+---------+---------------------+---------+----------+ | 501 | 1 | 2025-08-20 12:00 | 99.00 | false | | 502 | 2 | 2025-08-28 11:05 | 59.00 | true | | 503 | 3 | 2025-08-13 09:00 | 49.00 | false | | 504 | 4 | 2025-08-30 13:00 | 129.00 | false | +----------+---------+---------------------+---------+----------+ Task A — core metrics (CTEs allowed): For experiment_id = 'tracker_vlaunch_v1' and exposures between 2025-08-01 and 2025-08-24, write a single query that: - Assigns each user to the earliest exposure variant and censors any later crossovers. - Qualifies users as non-bot if they have at least one session with is_bot = false on or before their earliest exposure. - Computes, per variant, by country in {US, CA} only: users_exposed, qualified_users, converters_14d (users with an order within 14 days of earliest exposure and order_ts < '2025-09-01'), conv_rate_14d, revenue_14d (sum of non-refunded revenue within the same 14-day window), refund_rate_14d (refunded orders / all orders in window). - Excludes orders whose order_ts falls in the iOS outage window ['2025-08-10', '2025-08-12') if the user’s earliest qualifying device was ios. Task B — SRM diagnostic: Write a second query that tests for sample ratio mismatch across variants among qualified users using a chi-square goodness-of-fit test vs. expected 50/50. Output observed_count_control, observed_count_B, expected_each, chi2_stat, and an approximate p_value (you may use built-in ln(), exp(), and power() but no UDFs). Edge cases to handle: users with no sessions; users with only bot sessions; users with orders before exposure; multiple orders within 14 days; users outside US/CA; and the crossover illustrated for user_id=2.

Overview: This question evaluates SQL-based data manipulation and experimental analytics skills, including attribution of earliest exposures, censoring crossovers, session-based bot qualification, time-windowed conversion and revenue calculations, and exclusion of outage periods in PostgreSQL.

Noisy A/B launch metrics — core metrics

You are given four PostgreSQL tables describing users, experiment exposures, sessions, and orders. Using only SQL (CTEs allowed), compute clean A/B test metrics for a single experiment. Tables: - users(user_id INT, country TEXT, signup_date DATE, marketing_channel TEXT) - exposures(user_id INT, experiment_id TEXT, variant TEXT, exposure_ts TIMESTAMP, source TEXT) - sessions(user_id INT, session_start TIMESTAMP, device TEXT, is_bot BOOLEAN) - orders(order_id INT, user_id INT, order_ts TIMESTAMP, revenue NUMERIC(10,2), refunded BOOLEAN) Use the sample data provided below. For experiment_id = 'tracker_vlaunch_v1' and exposures with exposure_ts between '2025-08-01' (inclusive) and '2025-08-25' (exclusive), write a single SQL query that: 1. Assigns each user to a single variant based on their earliest exposure in this experiment within the date range, and ignores any later crossovers to a different variant. 2. Defines a user as **qualified (non-bot)** if they have at least one session with is_bot = false and session_start <= their earliest exposure time. Users with no sessions, or only bot sessions before exposure, are **not qualified**. 3. Determines the user’s **earliest qualifying device** as the device from their earliest non-bot session at or before their earliest exposure. 4. For users in countries **US** or **CA** only, computes the following metrics **per variant and country**: - users_exposed: number of users assigned to that variant in that country (regardless of qualification). - qualified_users: number of users_exposed who are qualified. - converters_14d: number of qualified_users who have at least one order within 14 days of their earliest exposure, with order_ts < '2025-09-01'. (Use the window [first_exposure_ts, min(first_exposure_ts + 14 days, '2025-09-01')).) - conv_rate_14d: converters_14d / qualified_users. - revenue_14d: sum of revenue from **non-refunded** orders in the same 14-day window for qualified users. - refund_rate_14d: (number of refunded orders in the 14-day window for qualified users) / (total number of orders in that window for qualified users). If there are no orders in the window, refund_rate_14d should be NULL. 5. **Excludes** any orders from the metrics whose order_ts falls in the iOS outage window ['2025-08-10', '2025-08-12') **if and only if** the user’s earliest qualifying device is 'ios'. (For non-iOS or unqualified users, this outage filter does not apply.) Handle these edge cases correctly: - Users with no sessions. - Users with only bot sessions. - Users with orders before their exposure (these should not count toward 14-day metrics). - Users with multiple orders within the 14-day window. - Users outside US/CA (they should not appear in the final result). - Users who experience a variant crossover (e.g., exposed to 'B' then 'control'); only the earliest exposure variant counts. Return one row per (variant, country) pair that has at least one exposed user in US or CA, with all the metrics listed above.

Tables

users(user_id INT, country VARCHAR(2), signup_date DATE, marketing_channel VARCHAR(50))

exposures(user_id INT, experiment_id VARCHAR(100), variant VARCHAR(20), exposure_ts TIMESTAMP, source VARCHAR(20))

sessions(user_id INT, session_start TIMESTAMP, device VARCHAR(20), is_bot BOOLEAN)

orders(order_id INT, user_id INT, order_ts TIMESTAMP, revenue NUMERIC(10,2), refunded BOOLEAN)

Hints

  1. First, find each user’s earliest exposure to the experiment in the date range and assign them to that variant using a window function.
  2. Compute qualification and earliest qualifying device from sessions before joining to orders; then filter orders to the 14-day window and apply the iOS outage exclusion before aggregating.

Noisy A/B launch metrics — SRM diagnostic

Using the same four PostgreSQL tables and experiment setting as in the core metrics task, write a query to test for **sample ratio mismatch (SRM)** between variants among qualified users. Use only data for experiment_id = 'tracker_vlaunch_v1' with exposure_ts between '2025-08-01' (inclusive) and '2025-08-25' (exclusive), and the same qualification rule: - A user is **qualified** if they have at least one session with is_bot = false and session_start <= their earliest exposure time. - The user’s assigned variant is based on their earliest exposure within that date range; later crossovers are ignored. Restrict to users in countries **US** or **CA**. Assume the experiment is designed as a 50/50 split between 'control' and 'B'. Among qualified users in US and CA, perform a **chi-square goodness-of-fit test** for the null hypothesis that the variant split is exactly 50/50. Your query should output a **single row** with the following columns: - observed_count_control: number of qualified users assigned to variant 'control'. - observed_count_B: number of qualified users assigned to variant 'B'. - expected_each: expected count per variant under a 50/50 split, i.e., total_qualified / 2. - chi2_stat: the chi-square test statistic for 2 categories (control vs B) and 1 degree of freedom. - p_value: an **approximate** p-value for the chi-square statistic. You may use built-in functions ln(), exp(), power(), and sqrt(), but no user-defined functions or external libraries. A reasonable approximation is sufficient; for example, you may treat the chi-square(1) statistic as the square of a standard normal variable and approximate the normal tail probability using a logistic function. Use the sample data from the tables below to ensure your query produces sensible values.

Tables

users(user_id INT, country VARCHAR(2), signup_date DATE, marketing_channel VARCHAR(50))

exposures(user_id INT, experiment_id VARCHAR(100), variant VARCHAR(20), exposure_ts TIMESTAMP, source VARCHAR(20))

sessions(user_id INT, session_start TIMESTAMP, device VARCHAR(20), is_bot BOOLEAN)

orders(order_id INT, user_id INT, order_ts TIMESTAMP, revenue NUMERIC(10,2), refunded BOOLEAN)

Hints

  1. Reuse the logic from the core task to assign each user to their earliest exposure variant and determine which users are qualified.
  2. Aggregate qualified users in US and CA to get the observed counts per variant, then compute the chi-square statistic and approximate the p-value using a simple function of sqrt(chi2_stat).

Loading coding console...