cswork / libraryOpen practice workspace

Coding questions · Stripe

Design metrics and write SQL for a case

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.

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.

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-07-151
US2025-07-202
CA2025-07-253
US2025-08-014
IN2025-08-055
US2025-08-106
US2025-08-097
US2025-07-168
US2025-08-029

Table events

columntypeconstraints
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_nameVARCHAR(32)NOT NULL
product_idINT
deviceVARCHAR(16)NOT NULL

Sample data:

deviceevent_nameevent_timeproduct_iduser_id
iOSpage_view2025-08-11 09:00:001011
iOSadd_to_cart2025-08-12 10:00:001011
iOSpurchase2025-08-15 12:00:001011
Webpage_view2025-08-18 14:00:001022
Webpurchase2025-08-19 16:00:001022
Webunsubscribe2025-08-20 08:00:00NULL2
Androidpage_view2025-08-20 11:30:001013
iOSpage_view2025-08-21 09:15:001014
iOSpurchase2025-08-28 17:45:001014
Webunsubscribe2025-08-22 08:05:00NULL5
Androidadd_to_cart2025-08-23 19:20:001026
Androidpurchase2025-08-15 10:00:001027
iOSpurchase2025-08-22 10:00:001018
Webpurchase2025-09-10 12:00:001019

Table purchases

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
amountDECIMAL(10,2)NOT NULL
product_idINTNOT NULL

Sample data:

amountorder_idorder_timeproduct_iduser_id
199.9950012025-08-15 12:00:001011
49.9950022025-08-19 16:00:001022
12950032025-08-28 17:45:001014
5950042025-08-25 20:10:001026
2050052025-08-15 10:00:001027
3550062025-08-22 10:00:001018
6050072025-09-10 12:00:001019

Table marketing_contacts

columntypeconstraints
contact_idINTPRIMARY KEY
user_idINTNOT NULL
contact_timeTIMESTAMPNOT NULL
channelVARCHAR(16)NOT NULL
campaignVARCHAR(32)NOT NULL

Sample data:

campaignchannelcontact_idcontact_timeuser_id
P_launchemail90012025-08-11 08:00:001
P_launchpush90022025-08-18 09:00:002
P_launchemail90032025-08-20 09:00:004
P_launchsms90042025-08-23 09:00:006

Table products

columntypeconstraints
product_idINTPRIMARY KEY
categoryVARCHAR(32)NOT NULL
launched_atDATENOT NULL

Sample data:

categorylaunched_atproduct_id
Elec2025-07-01101
Apparel2025-08-01102

Expected output

One row with the primary 14-day post-contact conversion rate and two guardrail rates (3-day unsubscribe rate and 1-day engagement rate) for all users' first 'P_launch' contacts in August 2025.

engaged_1d_rateprimary_conversion_14dunsubscribe_3d_rate
0.7510.25

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-07-151
US2025-07-202
CA2025-07-253
US2025-08-014
IN2025-08-055
US2025-08-106
US2025-08-097
US2025-07-168
US2025-08-029

Table events

columntypeconstraints
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_nameVARCHAR(32)NOT NULL
product_idINT
deviceVARCHAR(16)NOT NULL

Sample data:

deviceevent_nameevent_timeproduct_iduser_id
iOSpage_view2025-08-11 09:00:001011
iOSadd_to_cart2025-08-12 10:00:001011
iOSpurchase2025-08-15 12:00:001011
Webpage_view2025-08-18 14:00:001022
Webpurchase2025-08-19 16:00:001022
Webunsubscribe2025-08-20 08:00:00NULL2
Androidpage_view2025-08-20 11:30:001013
iOSpage_view2025-08-21 09:15:001014
iOSpurchase2025-08-28 17:45:001014
Webunsubscribe2025-08-22 08:05:00NULL5
Androidadd_to_cart2025-08-23 19:20:001026
Androidpurchase2025-08-15 10:00:001027
iOSpurchase2025-08-22 10:00:001018
Webpurchase2025-09-10 12:00:001019

Table purchases

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
amountDECIMAL(10,2)NOT NULL
product_idINTNOT NULL

Sample data:

amountorder_idorder_timeproduct_iduser_id
199.9950012025-08-15 12:00:001011
49.9950022025-08-19 16:00:001022
12950032025-08-28 17:45:001014
5950042025-08-25 20:10:001026
2050052025-08-15 10:00:001027
3550062025-08-22 10:00:001018
6050072025-09-10 12:00:001019

Table marketing_contacts

columntypeconstraints
contact_idINTPRIMARY KEY
user_idINTNOT NULL
contact_timeTIMESTAMPNOT NULL
channelVARCHAR(16)NOT NULL
campaignVARCHAR(32)NOT NULL

Sample data:

campaignchannelcontact_idcontact_timeuser_id
P_launchemail90012025-08-11 08:00:001
P_launchpush90022025-08-18 09:00:002
P_launchemail90032025-08-20 09:00:004
P_launchsms90042025-08-23 09:00:006

Table products

columntypeconstraints
product_idINTPRIMARY KEY
categoryVARCHAR(32)NOT NULL
launched_atDATENOT NULL

Sample data:

categorylaunched_atproduct_id
Elec2025-07-01101
Apparel2025-08-01102

Expected output

For each contact_week and country, the number of contacted users, the 14-day post-contact purchase conversion rate, and the average revenue per contacted user.

avg_revenue_per_contacted_usercontact_weekcontacted_usersconversion_14dcountry
199.992025-08-1111US
79.332025-08-1831US

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-07-151
US2025-07-202
CA2025-07-253
US2025-08-014
IN2025-08-055
US2025-08-106
US2025-08-097
US2025-07-168
US2025-08-029

Table events

columntypeconstraints
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_nameVARCHAR(32)NOT NULL
product_idINT
deviceVARCHAR(16)NOT NULL

Sample data:

deviceevent_nameevent_timeproduct_iduser_id
iOSpage_view2025-08-11 09:00:001011
iOSadd_to_cart2025-08-12 10:00:001011
iOSpurchase2025-08-15 12:00:001011
Webpage_view2025-08-18 14:00:001022
Webpurchase2025-08-19 16:00:001022
Webunsubscribe2025-08-20 08:00:00NULL2
Androidpage_view2025-08-20 11:30:001013
iOSpage_view2025-08-21 09:15:001014
iOSpurchase2025-08-28 17:45:001014
Webunsubscribe2025-08-22 08:05:00NULL5
Androidadd_to_cart2025-08-23 19:20:001026
Androidpurchase2025-08-15 10:00:001027
iOSpurchase2025-08-22 10:00:001018
Webpurchase2025-09-10 12:00:001019

Table purchases

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
amountDECIMAL(10,2)NOT NULL
product_idINTNOT NULL

Sample data:

amountorder_idorder_timeproduct_iduser_id
199.9950012025-08-15 12:00:001011
49.9950022025-08-19 16:00:001022
12950032025-08-28 17:45:001014
5950042025-08-25 20:10:001026
2050052025-08-15 10:00:001027
3550062025-08-22 10:00:001018
6050072025-09-10 12:00:001019

Table marketing_contacts

columntypeconstraints
contact_idINTPRIMARY KEY
user_idINTNOT NULL
contact_timeTIMESTAMPNOT NULL
channelVARCHAR(16)NOT NULL
campaignVARCHAR(32)NOT NULL

Sample data:

campaignchannelcontact_idcontact_timeuser_id
P_launchemail90012025-08-11 08:00:001
P_launchpush90022025-08-18 09:00:002
P_launchemail90032025-08-20 09:00:004
P_launchsms90042025-08-23 09:00:006

Table products

columntypeconstraints
product_idINTPRIMARY KEY
categoryVARCHAR(32)NOT NULL
launched_atDATENOT NULL

Sample data:

categorylaunched_atproduct_id
Elec2025-07-01101
Apparel2025-08-01102

Expected output

For each contact_week and country, the number of matched control users and their 14-day baseline purchase conversion rate using matched non-contacted users.

baseline_conversion_14dcontact_weekcountrymatched_control_users
12025-08-11US1
0.33332025-08-18US3

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-07-151
US2025-07-202
CA2025-07-253
US2025-08-014
IN2025-08-055
US2025-08-106
US2025-08-097
US2025-07-168
US2025-08-029

Table events

columntypeconstraints
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_nameVARCHAR(32)NOT NULL
product_idINT
deviceVARCHAR(16)NOT NULL

Sample data:

deviceevent_nameevent_timeproduct_iduser_id
iOSpage_view2025-08-11 09:00:001011
iOSadd_to_cart2025-08-12 10:00:001011
iOSpurchase2025-08-15 12:00:001011
Webpage_view2025-08-18 14:00:001022
Webpurchase2025-08-19 16:00:001022
Webunsubscribe2025-08-20 08:00:00NULL2
Androidpage_view2025-08-20 11:30:001013
iOSpage_view2025-08-21 09:15:001014
iOSpurchase2025-08-28 17:45:001014
Webunsubscribe2025-08-22 08:05:00NULL5
Androidadd_to_cart2025-08-23 19:20:001026
Androidpurchase2025-08-15 10:00:001027
iOSpurchase2025-08-22 10:00:001018
Webpurchase2025-09-10 12:00:001019

Table purchases

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
amountDECIMAL(10,2)NOT NULL
product_idINTNOT NULL

Sample data:

amountorder_idorder_timeproduct_iduser_id
199.9950012025-08-15 12:00:001011
49.9950022025-08-19 16:00:001022
12950032025-08-28 17:45:001014
5950042025-08-25 20:10:001026
2050052025-08-15 10:00:001027
3550062025-08-22 10:00:001018
6050072025-09-10 12:00:001019

Table marketing_contacts

columntypeconstraints
contact_idINTPRIMARY KEY
user_idINTNOT NULL
contact_timeTIMESTAMPNOT NULL
channelVARCHAR(16)NOT NULL
campaignVARCHAR(32)NOT NULL

Sample data:

campaignchannelcontact_idcontact_timeuser_id
P_launchemail90012025-08-11 08:00:001
P_launchpush90022025-08-18 09:00:002
P_launchemail90032025-08-20 09:00:004
P_launchsms90042025-08-23 09:00:006

Table products

columntypeconstraints
product_idINTPRIMARY KEY
categoryVARCHAR(32)NOT NULL
launched_atDATENOT NULL

Sample data:

categorylaunched_atproduct_id
Elec2025-07-01101
Apparel2025-08-01102

Expected output

Top device/product slices for US users contacted on 2025-08-20, with their 14-day conversion, prior 7-day conversion, delta, and approximate contribution to the change. With this sample data there is only one such slice.

approximate_contributioncontact_dateconversion_today_14dcount_contactedcountrydelta_conversion_14ddeviceprior7_conversion_14dproduct_id
12025-08-2011US1iOS0101

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-07-151
US2025-07-202
CA2025-07-253
US2025-08-014
IN2025-08-055
US2025-08-106
US2025-08-097
US2025-07-168
US2025-08-029

Table events

columntypeconstraints
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_nameVARCHAR(32)NOT NULL
product_idINT
deviceVARCHAR(16)NOT NULL

Sample data:

deviceevent_nameevent_timeproduct_iduser_id
iOSpage_view2025-08-11 09:00:001011
iOSadd_to_cart2025-08-12 10:00:001011
iOSpurchase2025-08-15 12:00:001011
Webpage_view2025-08-18 14:00:001022
Webpurchase2025-08-19 16:00:001022
Webunsubscribe2025-08-20 08:00:00NULL2
Androidpage_view2025-08-20 11:30:001013
iOSpage_view2025-08-21 09:15:001014
iOSpurchase2025-08-28 17:45:001014
Webunsubscribe2025-08-22 08:05:00NULL5
Androidadd_to_cart2025-08-23 19:20:001026
Androidpurchase2025-08-15 10:00:001027
iOSpurchase2025-08-22 10:00:001018
Webpurchase2025-09-10 12:00:001019

Table purchases

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
amountDECIMAL(10,2)NOT NULL
product_idINTNOT NULL

Sample data:

amountorder_idorder_timeproduct_iduser_id
199.9950012025-08-15 12:00:001011
49.9950022025-08-19 16:00:001022
12950032025-08-28 17:45:001014
5950042025-08-25 20:10:001026
2050052025-08-15 10:00:001027
3550062025-08-22 10:00:001018
6050072025-09-10 12:00:001019

Table marketing_contacts

columntypeconstraints
contact_idINTPRIMARY KEY
user_idINTNOT NULL
contact_timeTIMESTAMPNOT NULL
channelVARCHAR(16)NOT NULL
campaignVARCHAR(32)NOT NULL

Sample data:

campaignchannelcontact_idcontact_timeuser_id
P_launchemail90012025-08-11 08:00:001
P_launchpush90022025-08-18 09:00:002
P_launchemail90032025-08-20 09:00:004
P_launchsms90042025-08-23 09:00:006

Table products

columntypeconstraints
product_idINTPRIMARY KEY
categoryVARCHAR(32)NOT NULL
launched_atDATENOT NULL

Sample data:

categorylaunched_atproduct_id
Elec2025-07-01101
Apparel2025-08-01102

Expected output

Two rows showing the number of contacted users who did and did not unsubscribe within 3 days, and their respective 14-day purchase conversion rates.

conversion_14dunsub_within_3dusers_in_segment
1Y1
1N3

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.