Chime·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Chime data scientist interview with a heavy SQL/stats focus. Two-part question covering experiment analysis and SRM detection, all in PostgreSQL. The schema had enough gotchas baked in that you couldn't just wing it.

Questions Asked (2)

Given a multi-table schema for an A/B experiment (users, exposures, sessions, orders), write a single PostgreSQL query using CTEs that: assigns each user to their earliest exposure variant and drops crossovers, filters to non-bot qualified users, and computes per-variant and per-country (US/CA only) metrics including 14-day converters, conversion rate, revenue from non-refunded orders, and refund rate. Also exclude orders in a specific outage window for iOS users.

A/B Testing & ExperimentationProduct Analytics & MetricsData Modeling

Author's notes

This took me way longer to structure than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining the CTE pipeline: first identify each user's earliest exposure and flag crossovers, then filter to qualified non-bot users, and finally join with sessions and orders to compute metrics. Emphasize that you'll handle the outage exclusion and country filtering within the order-level CTE before aggregation. Conclude by describing how you'll group by variant and country to produce the final metrics.

Pro tip: Mention that you would validate the query by checking for data quality issues like duplicate exposures and ensuring the outage window is applied only to iOS users, as this shows attention to detail and business context.

1. Identify earliest exposure and drop crossovers

Use a CTE to rank exposures per user by timestamp and select the earliest variant. Then exclude users who appear in multiple variants to remove crossovers.

2. Filter to qualified non-bot users

Join with a users table to filter out bots and apply any qualification criteria (e.g., account age, activity). Ensure only eligible users remain.

3. Join sessions and orders with outage exclusion

Left join sessions and orders, applying the outage window exclusion for iOS users. Ensure orders are non-refunded for revenue calculations.

4. Compute per-variant and per-country metrics

Aggregate by variant and country (US/CA only) to calculate 14-day converters, conversion rate, revenue, and refund rate. Use conditional aggregation for each metric.

Key Points to Mention

Use of CTEs for readability and modularity, with clear naming conventions.
Handling crossovers by selecting the earliest exposure and excluding users with multiple variants.
Defining 14-day converters as users who converted within 14 days of exposure, using date arithmetic.
Calculating conversion rate as converters divided by total qualified users per variant and country.
Revenue from non-refunded orders: sum order amounts where refund status is false.
Refund rate: number of refunded orders divided by total orders, ensuring correct denominator.
Excluding orders during the outage window for iOS users by filtering on order timestamp and user platform.
Filtering to US/CA only and grouping by variant and country for final output.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Write a second SQL query that performs a chi-square goodness-of-fit test for sample ratio mismatch across variants among qualified users, assuming a 50/50 expected split. Output observed counts per variant, expected count, the chi-square statistic, and an approximate p-value using only built-in functions like ln(), exp(), and power().

A/B Testing & ExperimentationProduct Analytics & Metrics

Author's notes

The chi-square part is fine if you remember the formula.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate qualified users by variant to get observed counts. Then compute the expected count as half of the total qualified users, and calculate the chi-square statistic using the formula Σ((observed - expected)^2 / expected). Finally, approximate the p-value using the chi-square CDF with 1 degree of freedom, which can be computed via the complementary error function or an approximation using built-in functions like exp and power.

Pro tip: Mention that for a 50/50 split, the chi-square statistic simplifies to (n1 - n2)^2 / (n1 + n2), which is easier to compute and avoids rounding issues. Also, note that the p-value approximation using erfc might not be exact but is sufficient for large samples.

1. Aggregate observed counts

Write a subquery or CTE that counts qualified users per variant, ensuring you filter for qualified users only.

2. Compute expected count

Calculate the total number of qualified users and divide by 2 to get the expected count for each variant under the null hypothesis of a 50/50 split.

3. Calculate chi-square statistic

For each variant, compute (observed - expected)^2 / expected and sum these values across variants to get the chi-square statistic.

4. Approximate p-value

Use the chi-square survival function for 1 degree of freedom: p = erfc(sqrt(chi_square / 2)). If erfc is not available, approximate it using exp and power functions, or use a known approximation formula.

5. Output results

Select the variant, observed count, expected count, chi-square statistic, and p-value in the final query.

Key Points to Mention

Chi-square goodness-of-fit test for sample ratio mismatch (SRM)
Assumption of 50/50 expected split
Use of built-in functions like ln(), exp(), power() for p-value approximation
Degrees of freedom = number of variants - 1 (here 1)
Simplified formula for two variants: (n1 - n2)^2 / (n1 + n2)
Handling of edge cases (e.g., zero expected count) and ensuring qualified users filter

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.