Quick Overview

This question evaluates the ability to define time‑bounded, attributable conversion metrics and to implement joins, aggregations, and diagnostic checks in SQL to measure marketing outreach impact.

Design metrics and write SQL for a case

Company: Stripe

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Case: Measure the impact of outreach on subsequent purchases and diagnose anomalies. Define your primary metric and write SQL. Schema and tiny samples below. users(user_id INT, signup_date DATE, country STRING) +---------+-------------+---------+ | user_id | signup_date | country | +---------+-------------+---------+ | 1 | 2025-07-15 | US | | 2 | 2025-07-20 | US | | 3 | 2025-07-25 | CA | | 4 | 2025-08-01 | US | | 5 | 2025-08-05 | IN | | 6 | 2025-08-10 | US | +---------+-------------+---------+ events(user_id INT, event_time TIMESTAMP, event_name STRING, product_id INT, device STRING) +---------+---------------------+-------------+------------+--------+ | user_id | event_time | event_name | product_id | device | +---------+---------------------+-------------+------------+--------+ | 1 | 2025-08-11 09:00:00 | page_view | 101 | iOS | | 1 | 2025-08-12 10:00:00 | add_to_cart | 101 | iOS | | 1 | 2025-08-15 12:00:00 | purchase | 101 | iOS | | 2 | 2025-08-18 14:00:00 | page_view | 102 | Web | | 2 | 2025-08-19 16:00:00 | purchase | 102 | Web | | 3 | 2025-08-20 11:30:00 | page_view | 101 | Android| | 4 | 2025-08-21 09:15:00 | page_view | 101 | iOS | | 4 | 2025-08-28 17:45:00 | purchase | 101 | iOS | | 5 | 2025-08-22 08:05:00 | unsubscribe | NULL | Web | | 6 | 2025-08-23 19:20:00 | add_to_cart | 102 | Android| +---------+---------------------+-------------+------------+--------+ purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT) +----------+---------+---------------------+--------+------------+ | order_id | user_id | order_time | amount | product_id | +----------+---------+---------------------+--------+------------+ | 5001 | 1 | 2025-08-15 12:00:00 | 199.99 | 101 | | 5002 | 2 | 2025-08-19 16:00:00 | 49.99 | 102 | | 5003 | 4 | 2025-08-28 17:45:00 | 129.00 | 101 | | 5004 | 6 | 2025-08-25 20:10:00 | 59.00 | 102 | +----------+---------+---------------------+--------+------------+ marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel STRING, campaign STRING) +------------+---------+---------------------+---------+-----------+ | contact_id | user_id | contact_time | channel | campaign | +------------+---------+---------------------+---------+-----------+ | 9001 | 1 | 2025-08-11 08:00:00 | email | P_launch | | 9002 | 2 | 2025-08-18 09:00:00 | push | P_launch | | 9003 | 4 | 2025-08-21 09:00:00 | email | P_launch | | 9004 | 6 | 2025-08-23 09:00:00 | sms | P_launch | +------------+---------+---------------------+---------+-----------+ products(product_id INT, category STRING, launched_at DATE) +------------+----------+-------------+ | product_id | category | launched_at | +------------+----------+-------------+ | 101 | Elec | 2025-07-01 | | 102 | Apparel | 2025-08-01 | +------------+----------+-------------+ Tasks: A) Define a primary success metric for the campaign that is attributable, time‑bounded, and robust to activity spikes (e.g., 14‑day post‑contact purchase conversion among first contacts), plus two guardrails (e.g., unsubscribe rate within 3 days, latency‑sensitive engagement). Write the precise metric formulas. B) Write SQL to compute, for each contact_week and country, the 14‑day post‑contact purchase conversion rate and average revenue per contacted user. Only use the first contact per user; exclude purchases that occur before contact_time. C) Produce SQL to generate a matched baseline: for each contacted user, pair to one non‑contacted user in the same signup_week and country (deterministic tie‑break by smallest user_id) and compute the same 14‑day purchase rate for matches. D) On 2025‑08‑20, US contacted‑user conversion drops by 20% vs its prior 7‑day average. Write SQL to produce a breakdown table by device and product_id for 2025‑08‑20 contacts with: count_contacted, 14‑day conversion, and delta vs the prior 7‑day average for the same slice; return the top‑3 slices contributing most to the drop (hint: approximate contribution = exposure × delta). Be precise about windowing and joins. E) Briefly describe one check you would add to ensure your metrics are not biased by users who unsubscribed immediately after contact.

Overview: This question evaluates the ability to define time‑bounded, attributable conversion metrics and to implement joins, aggregations, and diagnostic checks in SQL to measure marketing outreach impact.

Read the full Stripe Data Scientist interview experience this question came from

Define and compute primary and guardrail metrics for a marketing campaign

Using the tables below, compute three campaign-level metrics for marketing campaign 'P_launch', considering only each user's first contact for this campaign between 2025-08-01 and 2025-08-31 (inclusive). Define and compute: 1) primary_conversion_14d: the fraction of contacted users who make at least one purchase within 14 days after their first contact_time. A purchase qualifies if purchases.order_time >= contact_time AND purchases.order_time < contact_time + INTERVAL '14 day'. 2) unsubscribe_3d_rate: the fraction of contacted users who have at least one 'unsubscribe' event in the events table within 3 days after their first contact_time (events.event_time >= contact_time AND events.event_time < contact_time + INTERVAL '3 day' AND events.event_name = 'unsubscribe'). 3) engaged_1d_rate: the fraction of contacted users who generate at least one event of any type within 1 day after their first contact_time (events.event_time >= contact_time AND events.event_time < contact_time + INTERVAL '1 day'). Only the first contact per user for campaign 'P_launch' should be used. Return a single row with columns: primary_conversion_14d, unsubscribe_3d_rate, engaged_1d_rate.

Tables

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

events(user_id INT, event_time TIMESTAMP, event_name VARCHAR(32), product_id INT, device VARCHAR(16))

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT)

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel VARCHAR(16), campaign VARCHAR(32))

products(product_id INT, category VARCHAR(32), launched_at DATE)

Hints

  1. First compute each user's first contact_time for campaign 'P_launch' in August 2025 in a CTE.
  2. For each metric, use time-bounded joins or EXISTS/COUNT(DISTINCT) over events or purchases within the required window after contact_time.

Weekly 14-day conversion and ARPU by country for first contacts

Using only each user's first contact in campaign 'P_launch', compute, for every combination of contact_week and country, the 14-day post-contact purchase conversion rate and average revenue per contacted user. Definitions: - contact_week is DATE_TRUNC('week', contact_time)::DATE (ISO weeks starting on Monday). - contacted_users: number of distinct users whose first contact_time falls in that contact_week. - conversion_14d: among those users, the fraction who make at least one purchase in the purchases table with order_time >= contact_time AND order_time < contact_time + INTERVAL '14 day'. Purchases before contact_time must be ignored. - avg_revenue_per_contacted_user: the sum of amount from qualifying purchases in the 14-day window divided by contacted_users. Ignore contacts from campaigns other than 'P_launch'. Return one row per (contact_week, country) with columns: contact_week, country, contacted_users, conversion_14d, avg_revenue_per_contacted_user.

Tables

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

events(user_id INT, event_time TIMESTAMP, event_name VARCHAR(32), product_id INT, device VARCHAR(16))

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT)

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel VARCHAR(16), campaign VARCHAR(32))

products(product_id INT, category VARCHAR(32), launched_at DATE)

Hints

  1. Reuse a CTE that identifies each user's first contact_time for campaign 'P_launch'.
  2. Join first contacts to purchases with a 14-day time condition, then aggregate by DATE_TRUNC('week', contact_time) and country.

Matched baseline 14-day conversion using non-contacted users

Using only each user's first contact in campaign 'P_launch', construct a matched control cohort and compute a baseline 14-day purchase conversion rate. Matching rules: - For each contacted user (treated_user), find one different user (control_user) who does NOT appear in marketing_contacts at all. - The control_user must have the same signup_week and country as the treated_user, where signup_week is DATE_TRUNC('week', signup_date)::DATE. - If multiple candidates exist, choose the control_user with the smallest user_id (deterministic tie-break). Metric definition: - Define contact_week for the treated user as DATE_TRUNC('week', contact_time)::DATE. - For each treated/control pair, evaluate the control_user's purchases in the 14 days after the treated_user's contact_time: purchases.order_time >= treated_contact_time AND purchases.order_time < treated_contact_time + INTERVAL '14 day'. - A control_user is counted as converted if they have at least one qualifying purchase in that window. Compute, for each (contact_week, country), the baseline 14-day purchase conversion rate among matched control users: - matched_control_users: number of distinct matched control users for that treated contact_week and country. - baseline_conversion_14d: fraction of matched control users who convert in the treated users' 14-day windows. Return columns: contact_week, country, matched_control_users, baseline_conversion_14d.

Tables

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

events(user_id INT, event_time TIMESTAMP, event_name VARCHAR(32), product_id INT, device VARCHAR(16))

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT)

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel VARCHAR(16), campaign VARCHAR(32))

products(product_id INT, category VARCHAR(32), launched_at DATE)

Hints

  1. Identify non-contacted users by selecting from users where user_id is not in marketing_contacts, then match on signup_week and country.
  2. Use ROW_NUMBER() partitioned by treated_user_id and ordered by control_user_id to pick the smallest-id control per treated user.

Diagnose a conversion drop by device and product slice

On 2025-08-20, the 14-day purchase conversion rate among US users contacted in campaign 'P_launch' has dropped versus its average over the previous 7 days. You want to break down this change by device and product_id to diagnose anomalies. Definitions: - Consider only each user's first contact in campaign 'P_launch'. - Define each contact's (device, product_id) slice as the device and product_id of that user's first event in the events table with event_time >= contact_time. If multiple events occur after contact, use the earliest by event_time. - A user is counted as converted if they have at least one purchase with order_time >= contact_time AND order_time < contact_time + INTERVAL '14 day'. Tasks: 1) For US users with first contacts on 2025-08-20 (DATE(contact_time) = '2025-08-20'): - Group by slice (device, product_id). - For each slice, compute count_contacted (number of contacted users in that slice) and conversion_today_14d (their 14-day conversion rate). 2) For the same slice definitions and country='US', compute prior7_conversion_14d using all first contacts in the date range 2025-08-13 to 2025-08-19 (inclusive). For each slice, prior7_conversion_14d is the 14-day conversion rate across those 7 days combined. 3) For 2025-08-20, compute for each slice: - delta_conversion_14d = conversion_today_14d - prior7_conversion_14d. If a slice has no prior-7-day data, treat prior7_conversion_14d as 0. - approximate_contribution = count_contacted * delta_conversion_14d (an approximation of that slice's contribution to the overall change). Return the top 3 slices for DATE(contact_time) = '2025-08-20' and country='US', ordered by approximate_contribution ascending (most negative impact first), with columns: - contact_date, country, device, product_id, - count_contacted, conversion_today_14d, - prior7_conversion_14d, delta_conversion_14d, - approximate_contribution.

Tables

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

events(user_id INT, event_time TIMESTAMP, event_name VARCHAR(32), product_id INT, device VARCHAR(16))

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT)

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel VARCHAR(16), campaign VARCHAR(32))

products(product_id INT, category VARCHAR(32), launched_at DATE)

Hints

  1. First assign each contact to a (device, product_id) slice using the earliest event at or after contact_time via ROW_NUMBER().
  2. Compute day-level counts and purchasers per slice for 2025-08-13 to 2025-08-20, then derive today vs prior 7-day conversion and approximate contribution from those aggregates.

Check for bias from users who unsubscribe shortly after contact

Using each user's first 'P_launch' contact in August 2025, check whether users who unsubscribe soon after contact might bias your conversion metrics. Steps: - Consider only first contacts where contact_time is between 2025-08-01 and 2025-08-31 (inclusive). - For each contacted user, define unsub_within_3d = 'Y' if they have at least one 'unsubscribe' event in events with event_time >= contact_time AND event_time < contact_time + INTERVAL '3 day'; otherwise unsub_within_3d = 'N'. - A user is counted as converted if they have at least one purchase with order_time >= contact_time AND order_time < contact_time + INTERVAL '14 day'. For each segment (unsub_within_3d = 'Y' or 'N'), compute: - users_in_segment: number of contacted users in that segment. - conversion_14d: the 14-day post-contact purchase conversion rate for that segment. Return columns: unsub_within_3d, users_in_segment, conversion_14d.

Tables

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

events(user_id INT, event_time TIMESTAMP, event_name VARCHAR(32), product_id INT, device VARCHAR(16))

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT)

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel VARCHAR(16), campaign VARCHAR(32))

products(product_id INT, category VARCHAR(32), launched_at DATE)

Hints

  1. Use EXISTS over events with event_name = 'unsubscribe' in a 3-day window after contact_time to flag each user.
  2. Compute a per-user 14-day purchase flag and then aggregate by the unsub_within_3d segment to get conversion rates.

Loading coding console...