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.
Quick Answer: 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.