Write SQL/Python for CTR analytics

Part A — SQL (use the schema and sample data below): Compute 48-hour unique CTR for campaign_id=100 by variant, deduplicating to the earliest send per (user_id, campaign_id) on/after 2025-08-30, and excluding internal accounts user_id <= 2. Define CTR as distinct users with ≥1 click in [send_time, send_time + 48h] divided by distinct users sent (after deduplication). Return columns: campaign_id, variant, sends, unique_clickers_48h, ctr_48h. Then, also return a single row with the absolute lift (test_ctr − control_ctr). Optionally, include counts needed to compute a 95% CI for the lift using the normal approximation so it can be calculated downstream.

Schema:

users(user_id INT, signup_dt DATE, locale STRING)
email_sends(send_id INT, user_id INT, campaign_id INT, send_time TIMESTAMP, variant STRING) -- variant in ('control','test')
email_events(event_id INT, send_id INT, event_type STRING, event_time TIMESTAMP) -- event_type in ('open','click','unsubscribe')

Sample data:

users
+---------+------------+--------+
| user_id | signup_dt | locale |
+---------+------------+--------+
| 1 | 2025-08-20 | US |
| 2 | 2025-08-22 | US |
| 3 | 2025-08-25 | CA |
| 4 | 2025-08-27 | US |
| 5 | 2025-08-28 | GB |

email_sends
+---------+---------+------------+---------------------+----------+
| send_id | user_id | campaign_id| send_time | variant |
+---------+---------+------------+---------------------+----------+
| 10 | 1 | 100 | 2025-08-30 09:00:00 | control |
| 11 | 2 | 100 | 2025-08-30 09:00:00 | control |
| 12 | 3 | 100 | 2025-08-30 09:00:00 | test |
| 13 | 1 | 100 | 2025-08-31 09:00:00 | test | -- resend
| 14 | 4 | 101 | 2025-08-31 10:00:00 | control |
| 15 | 5 | 100 | 2025-08-30 09:00:00 | test |

email_events
+----------+---------+------------+---------------------+
| event_id | send_id | event_type | event_time |
+----------+---------+------------+---------------------+
| 1000 | 10 | open | 2025-08-30 09:05:00 |
| 1001 | 10 | click | 2025-08-30 09:06:00 |
| 1002 | 11 | open | 2025-08-30 09:10:00 |
| 1003 | 12 | open | 2025-08-30 09:07:00 |
| 1004 | 12 | unsubscribe| 2025-08-30 10:00:00 |
| 1005 | 13 | click | 2025-08-31 09:12:00 |
| 1006 | 15 | click | 2025-08-31 09:00:00 |
| 1007 | 14 | click | 2025-08-31 10:05:00 |

Part B — Python (pandas/sklearn): Given a DataFrame df with columns y_true (0/1), y_prob (predicted probability), and weight (non-negative), write code to: (1) plot the precision-recall curve; (2) find the threshold that maximizes weighted F1 using 'weight' as sample weights; (3) compute precision@top1% of the population by scoring, breaking ties deterministically. Then, explain why a model can have ROC-AUC=0.86 but PR-AUC=0.18 at 1% prevalence, and which metric you would optimize for a marketing CTR use case.

48-hour Unique CTR by Variant with Lift for a Campaign

You are analyzing an A/B email campaign for Uber. Three tables describe the experiment:

• users — user_id (PK), signup_dt, locale.
• email_sends — one row per email sent: send_id (PK), user_id, campaign_id, send_time (timestamp), variant ('control' or 'test').
• email_events — engagement events on a send: event_id (PK), send_id (FK to email_sends), event_type (e.g. 'open', 'click', 'unsubscribe'), event_time (timestamp).

Write one PostgreSQL query that computes the 48-hour unique click-through rate (CTR) for campaign_id = 100 by variant, and the absolute lift between test and control.

Rules

1. Deduplicate sends: for each (user_id, campaign_id) keep only the earliest send (break ties by smallest send_id), and only consider sends with send_time >= '2025-08-30 00:00:00'.
2. Scope: only campaign_id = 100.
3. Exclude internal accounts: drop sends where user_id <= 2.
4. 48-hour click window: a deduplicated send counts as clicked if it has at least one email_events row with event_type = 'click' whose event_time falls in [send_time, send_time + 48 hours] (inclusive).
5. CTR per variant = (distinct users with ≥1 click in the 48h window) / (distinct users sent, after dedup). If a variant has zero sends, its CTR is 0.

Output: return exactly three rows, in this order:

• one row for variant = 'control',
• one row for variant = 'test',
• one row for variant = 'lift' (the absolute difference test_ctr − control_ctr).

Columns (in order): campaign_id, variant, sends, unique_clickers_48h, ctr_48h.

For the two variant rows, sends and unique_clickers_48h are the counts described above and ctr_48h is the CTR (numeric, rounded to 4 decimals). For the 'lift' row, set campaign_id = 100, variant = 'lift', sends = NULL, unique_clickers_48h = NULL, and ctr_48h = test_ctr − control_ctr. Sort the result so the rows appear in the order control, test, lift.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
signup_dt	DATE	NOT NULL
locale	VARCHAR(10)	NOT NULL

Sample data:

locale	signup_dt	user_id
US	2025-08-20	1
US	2025-08-22	2
CA	2025-08-25	3
US	2025-08-27	4
GB	2025-08-28	5

Table email_sends

column	type	constraints
send_id	INT	PRIMARY KEY
user_id	INT	NOT NULL
campaign_id	INT	NOT NULL
send_time	TIMESTAMP	NOT NULL
variant	VARCHAR(20)	NOT NULL

Sample data:

campaign_id	send_id	send_time	user_id	variant
100	10	2025-08-30 09:00:00	1	control
100	11	2025-08-30 09:00:00	2	control
100	12	2025-08-30 09:00:00	3	test
100	13	2025-08-31 09:00:00	1	test
101	14	2025-08-31 10:00:00	4	control
100	15	2025-08-30 09:00:00	5	test

Table email_events

column	type	constraints
event_id	INT	PRIMARY KEY
send_id	INT	NOT NULL
event_type	VARCHAR(20)	NOT NULL
event_time	TIMESTAMP	NOT NULL

Sample data:

event_id	event_time	event_type	send_id
1000	2025-08-30 09:05:00	open	10
1001	2025-08-30 09:06:00	click	10
1002	2025-08-30 09:10:00	open	11
1003	2025-08-30 09:07:00	open	12
1004	2025-08-30 10:00:00	unsubscribe	12
1005	2025-08-31 09:12:00	click	13
1006	2025-08-31 09:00:00	click	15
1007	2025-08-31 10:05:00	click	14

Expected output

Three rows for campaign 100: control (0 sends, 0 clickers, CTR 0.0), test (2 sends — users 3 and 5 — 1 clicker — user 5 — CTR 0.5), and lift = 0.5 − 0.0 = 0.5.

campaign_id	ctr_48h	sends	unique_clickers_48h	variant
100	0.0	0	0	control
100	0.5000	2	1	test
100	0.5000	NULL	NULL	lift

Hints

1. Deduplicate with ROW_NUMBER() OVER (PARTITION BY user_id, campaign_id ORDER BY send_time, send_id) and keep rn = 1, applying the campaign / date / internal-user filters first.
2. Build a fixed 2-row variant scaffold and LEFT JOIN to it so a variant with zero sends still produces a row (with CTR 0).
3. PostgreSQL rejects an expression in ORDER BY after UNION ALL — give each branch a literal sort_order column, wrap the union in a CTE/subquery, then ORDER BY that column. Use INTERVAL '48 hours' (not '48' HOUR) for the click window.