Coding questions · Snapchat

Compute CTR and metrics with pandas

This question evaluates proficiency in time-series and event-level data manipulation with pandas, including CTR computation, event denoising and deduplication, sessionization, cohort analysis, temporal joins, and performance-aware vectorized operations within the Data Manipulation (SQL/Python) domain.

Using pandas only, compute banner and story metrics. Assume today is 2025-09-01 and 'last 7 days' means 2025-08-26 to 2025-09-01 inclusive. You are given two DataFrames: events and users. events columns: event_id (int), user_id (int), ts (UTC datetime), event_type (string in {'banner_impression','banner_click','story_view','story_post','back_navigation'}), banner_id (nullable int), story_id (nullable int), group_id (nullable int), dwell_ms (nullable int). users columns: user_id (int), is_bot (bool), signup_dt (date). A tiny sample of events (chronologically):

1, 10, 2025-08-31T12:00:00Z, banner_impression, 7, null, null, null
2, 10, 2025-08-31T12:00:01Z, banner_click, 7, null, null, 200
3, 10, 2025-08-31T12:00:02Z, back_navigation, null, null, null, null
4, 11, 2025-08-31T13:00:00Z, banner_impression, 7, null, null, null
5, 11, 2025-08-31T13:00:05Z, banner_click, 7, null, null, 1200
6, 12, 2025-08-29T09:00:00Z, story_post, null, 101, 5, null

Tasks:

Define accidental clicks as banner_click events with dwell_ms < 500 OR followed by a back_navigation by the same user within 2 seconds; exclude these from CTR.
Compute daily per-banner CTR over the last 7 days, excluding bots (users.is_bot = True) and accidental clicks. Output: date, banner_id, impressions, valid_clicks, ctr.
Compute user-level 7-day CTR (same exclusions) and the distribution by signup cohort (signup_dt week).
For the Group Story feature, compute per-user change in average session duration between pre-period 2025-08-18..2025-08-24 and post-period 2025-08-25..2025-09-01. Define sessions as gaps of >30 minutes between any two events by the same user. Output: user_id, pre_avg_minutes, post_avg_minutes, delta_minutes, stories_posted_change.
Performance: write vectorized pandas code (groupby, rolling/window, merge_asof) without Python loops; ensure stable dtypes and avoid SettingWithCopy warnings. Mention any indices you would set and how you would test correctness on the sample.

Daily per-banner CTR (exclude bots and accidental clicks)

You are given two tables: events and users.

Compute daily per-banner CTR for the date range 2025-08-26 to 2025-09-01 (inclusive).

Rules:

Only include events from non-bot users (users.is_bot = FALSE).
Impressions are event_type = 'banner_impression'.
Clicks are event_type = 'banner_click', but exclude accidental clicks.
Define an accidental click as a banner_click event that satisfies either condition:
- dwell_ms < 500, OR
- it is followed by a back_navigation event by the same user within 2 seconds.

Output columns:

event_date (DATE)
banner_id
impressions
valid_clicks
ctr = valid_clicks / impressions (0 when impressions = 0)

Order results by event_date, banner_id.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
is_bot	BOOLEAN	NOT NULL
signup_dt	DATE	NOT NULL

Sample data:

is_bot	signup_dt	user_id
false	2025-08-10	10
false	2025-08-28	11
false	2025-08-15	12
true	2025-08-05	13

Table `events`

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
ts	TIMESTAMPTZ	NOT NULL
event_type	VARCHAR(40)	NOT NULL
banner_id	INT
story_id	INT
group_id	INT
dwell_ms	INT

Sample data:

banner_id	dwell_ms	event_id	event_type	group_id	story_id	ts	user_id
NULL	NULL	101	story_view	5	201	2025-08-20T10:00:00Z	10
NULL	NULL	102	story_post	5	201	2025-08-20T10:05:00Z	10
NULL	NULL	103	story_view	5	202	2025-08-20T10:40:00Z	10
8	NULL	104	banner_impression	NULL	NULL	2025-08-27T12:00:00Z	10
8	700	105	banner_click	NULL	NULL	2025-08-27T12:00:05Z	10
NULL	NULL	106	story_post	5	203	2025-08-27T12:10:00Z	10
NULL	NULL	107	story_view	5	203	2025-08-27T13:00:00Z	10
7	NULL	108	banner_impression	NULL	NULL	2025-08-31T12:00:00Z	10
7	200	109	banner_click	NULL	NULL	2025-08-31T12:00:01Z	10
NULL	NULL	110	back_navigation	NULL	NULL	2025-08-31T12:00:02Z	10
NULL	NULL	201	story_view	5	301	2025-08-23T09:00:00Z	11
NULL	NULL	202	back_navigation	NULL	NULL	2025-08-23T09:20:00Z	11
8	NULL	203	banner_impression	NULL	NULL	2025-08-27T12:05:00Z	11
7	NULL	204	banner_impression	NULL	NULL	2025-08-31T13:00:00Z	11
7	1200	205	banner_click	NULL	NULL	2025-08-31T13:00:05Z	11
NULL	NULL	206	story_post	5	302	2025-08-31T13:15:00Z	11
NULL	NULL	207	story_view	5	302	2025-08-31T13:50:00Z	11
7	NULL	208	banner_impression	NULL	NULL	2025-09-01T08:00:00Z	11
7	400	209	banner_click	NULL	NULL	2025-09-01T08:00:02Z	11
NULL	NULL	301	story_post	5	401	2025-08-19T15:00:00Z	12
NULL	NULL	302	story_post	5	402	2025-08-19T15:25:00Z	12
7	NULL	303	banner_impression	NULL	NULL	2025-08-30T10:00:00Z	12
7	800	304	banner_click	NULL	NULL	2025-08-30T10:00:03Z	12
NULL	NULL	305	back_navigation	NULL	NULL	2025-08-30T10:00:04Z	12
7	NULL	306	banner_impression	NULL	NULL	2025-08-30T10:20:00Z	12
NULL	NULL	307	story_post	5	403	2025-08-30T10:45:00Z	12
7	NULL	401	banner_impression	NULL	NULL	2025-08-29T14:00:00Z	13
7	900	402	banner_click	NULL	NULL	2025-08-29T14:00:10Z	13
NULL	NULL	403	story_post	5	501	2025-08-29T14:20:00Z	13

Expected output

One row per (date, banner_id) in 2025-08-26..2025-09-01 where there is at least one non-bot banner impression. valid_clicks excludes accidental clicks. ctr is valid_clicks/impressions.

banner_id	ctr	event_date	impressions	valid_clicks
8	0.5	2025-08-27	2	1
7	0	2025-08-30	2	0
7	0.5	2025-08-31	2	1
7	0	2025-09-01	1	0

Hints

Use LEAD() to check whether a click is followed by back_navigation within 2 seconds.
Compute impressions and valid clicks separately, then join on (date, banner_id).

7-day user CTR and signup-week cohort distribution

Using the same events and users tables, compute 7-day user-level CTR and then aggregate it by signup cohort week.

Date range: 2025-08-26 to 2025-09-01 (inclusive).

Use the same rules as in Question 1:

Exclude bot users (is_bot = FALSE).
Accidental clicks are clicks with dwell_ms < 500 OR followed by back_navigation within 2 seconds.

Definitions:

user impressions = count of banner_impression
user valid clicks = count of non-accidental banner_click
user CTR = valid_clicks / impressions (NULL if impressions = 0)
cohort week = DATE_TRUNC('week', signup_dt)::date

Output one row per cohort week:

cohort_week_start
user_count (number of non-bot users who had at least one impression in the 7-day window)
total_impressions
total_valid_clicks
avg_user_ctr (average of user CTRs)
p50_user_ctr (median user CTR within the cohort)

Order by cohort_week_start.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
is_bot	BOOLEAN	NOT NULL
signup_dt	DATE	NOT NULL

Sample data:

is_bot	signup_dt	user_id
false	2025-08-10	10
false	2025-08-28	11
false	2025-08-15	12
true	2025-08-05	13

Table `events`

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
ts	TIMESTAMPTZ	NOT NULL
event_type	VARCHAR(40)	NOT NULL
banner_id	INT
story_id	INT
group_id	INT
dwell_ms	INT

Sample data:

banner_id	dwell_ms	event_id	event_type	group_id	story_id	ts	user_id
NULL	NULL	101	story_view	5	201	2025-08-20T10:00:00Z	10
NULL	NULL	102	story_post	5	201	2025-08-20T10:05:00Z	10
NULL	NULL	103	story_view	5	202	2025-08-20T10:40:00Z	10
8	NULL	104	banner_impression	NULL	NULL	2025-08-27T12:00:00Z	10
8	700	105	banner_click	NULL	NULL	2025-08-27T12:00:05Z	10
NULL	NULL	106	story_post	5	203	2025-08-27T12:10:00Z	10
NULL	NULL	107	story_view	5	203	2025-08-27T13:00:00Z	10
7	NULL	108	banner_impression	NULL	NULL	2025-08-31T12:00:00Z	10
7	200	109	banner_click	NULL	NULL	2025-08-31T12:00:01Z	10
NULL	NULL	110	back_navigation	NULL	NULL	2025-08-31T12:00:02Z	10
NULL	NULL	201	story_view	5	301	2025-08-23T09:00:00Z	11
NULL	NULL	202	back_navigation	NULL	NULL	2025-08-23T09:20:00Z	11
8	NULL	203	banner_impression	NULL	NULL	2025-08-27T12:05:00Z	11
7	NULL	204	banner_impression	NULL	NULL	2025-08-31T13:00:00Z	11
7	1200	205	banner_click	NULL	NULL	2025-08-31T13:00:05Z	11
NULL	NULL	206	story_post	5	302	2025-08-31T13:15:00Z	11
NULL	NULL	207	story_view	5	302	2025-08-31T13:50:00Z	11
7	NULL	208	banner_impression	NULL	NULL	2025-09-01T08:00:00Z	11
7	400	209	banner_click	NULL	NULL	2025-09-01T08:00:02Z	11
NULL	NULL	301	story_post	5	401	2025-08-19T15:00:00Z	12
NULL	NULL	302	story_post	5	402	2025-08-19T15:25:00Z	12
7	NULL	303	banner_impression	NULL	NULL	2025-08-30T10:00:00Z	12
7	800	304	banner_click	NULL	NULL	2025-08-30T10:00:03Z	12
NULL	NULL	305	back_navigation	NULL	NULL	2025-08-30T10:00:04Z	12
7	NULL	306	banner_impression	NULL	NULL	2025-08-30T10:20:00Z	12
NULL	NULL	307	story_post	5	403	2025-08-30T10:45:00Z	12
7	NULL	401	banner_impression	NULL	NULL	2025-08-29T14:00:00Z	13
7	900	402	banner_click	NULL	NULL	2025-08-29T14:00:10Z	13
NULL	NULL	403	story_post	5	501	2025-08-29T14:20:00Z	13

Expected output

One row per signup-week cohort with aggregated 7-day user CTR metrics for non-bot users who had at least one impression in the window.

avg_user_ctr	cohort_week_start	p50_user_ctr	total_impressions	total_valid_clicks	user_count
0.5	2025-08-04	0.5	2	1	1
0	2025-08-11	0	2	0	1
0.3333333333	2025-08-25	0.3333333333	3	1	1

Hints

Compute user-level impressions and valid clicks first, then compute user CTR.
Use DATE_TRUNC('week', signup_dt) for cohorts and PERCENTILE_CONT for the median.

Sessionization and pre/post change in average session duration (Group Story feature)

Using the same tables, compute per-user change in average session duration between:

Pre-period: 2025-08-18 to 2025-08-24 (inclusive)
Post-period: 2025-08-25 to 2025-09-01 (inclusive)

Session definition:

A user's events belong to the same session until there is a gap of more than 30 minutes between consecutive events (any event type counts).
Session duration = (max(ts) - min(ts)) in minutes.
Assign a session to pre/post based on session_start::date.

Also compute stories_posted_change for the Group Story feature:

Count event_type = 'story_post' with group_id IS NOT NULL in the pre-period and post-period.
stories_posted_change = post_count - pre_count.

Exclude bot users.

Output columns:

user_id
pre_avg_minutes
post_avg_minutes
delta_minutes = post_avg_minutes - pre_avg_minutes
stories_posted_change

Order by user_id.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
is_bot	BOOLEAN	NOT NULL
signup_dt	DATE	NOT NULL

Sample data:

is_bot	signup_dt	user_id
false	2025-08-10	10
false	2025-08-28	11
false	2025-08-15	12
true	2025-08-05	13

Table `events`

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
ts	TIMESTAMPTZ	NOT NULL
event_type	VARCHAR(40)	NOT NULL
banner_id	INT
story_id	INT
group_id	INT
dwell_ms	INT

Sample data:

banner_id	dwell_ms	event_id	event_type	group_id	story_id	ts	user_id
NULL	NULL	101	story_view	5	201	2025-08-20T10:00:00Z	10
NULL	NULL	102	story_post	5	201	2025-08-20T10:05:00Z	10
NULL	NULL	103	story_view	5	202	2025-08-20T10:40:00Z	10
8	NULL	104	banner_impression	NULL	NULL	2025-08-27T12:00:00Z	10
8	700	105	banner_click	NULL	NULL	2025-08-27T12:00:05Z	10
NULL	NULL	106	story_post	5	203	2025-08-27T12:10:00Z	10
NULL	NULL	107	story_view	5	203	2025-08-27T13:00:00Z	10
7	NULL	108	banner_impression	NULL	NULL	2025-08-31T12:00:00Z	10
7	200	109	banner_click	NULL	NULL	2025-08-31T12:00:01Z	10
NULL	NULL	110	back_navigation	NULL	NULL	2025-08-31T12:00:02Z	10
NULL	NULL	201	story_view	5	301	2025-08-23T09:00:00Z	11
NULL	NULL	202	back_navigation	NULL	NULL	2025-08-23T09:20:00Z	11
8	NULL	203	banner_impression	NULL	NULL	2025-08-27T12:05:00Z	11
7	NULL	204	banner_impression	NULL	NULL	2025-08-31T13:00:00Z	11
7	1200	205	banner_click	NULL	NULL	2025-08-31T13:00:05Z	11
NULL	NULL	206	story_post	5	302	2025-08-31T13:15:00Z	11
NULL	NULL	207	story_view	5	302	2025-08-31T13:50:00Z	11
7	NULL	208	banner_impression	NULL	NULL	2025-09-01T08:00:00Z	11
7	400	209	banner_click	NULL	NULL	2025-09-01T08:00:02Z	11
NULL	NULL	301	story_post	5	401	2025-08-19T15:00:00Z	12
NULL	NULL	302	story_post	5	402	2025-08-19T15:25:00Z	12
7	NULL	303	banner_impression	NULL	NULL	2025-08-30T10:00:00Z	12
7	800	304	banner_click	NULL	NULL	2025-08-30T10:00:03Z	12
NULL	NULL	305	back_navigation	NULL	NULL	2025-08-30T10:00:04Z	12
7	NULL	306	banner_impression	NULL	NULL	2025-08-30T10:20:00Z	12
NULL	NULL	307	story_post	5	403	2025-08-30T10:45:00Z	12
7	NULL	401	banner_impression	NULL	NULL	2025-08-29T14:00:00Z	13
7	900	402	banner_click	NULL	NULL	2025-08-29T14:00:10Z	13
NULL	NULL	403	story_post	5	501	2025-08-29T14:20:00Z	13

Expected output

One row per non-bot user with average session duration in minutes for pre and post periods, the delta (post - pre), and the change in Group Story posts (post_count - pre_count).

delta_minutes	post_avg_minutes	pre_avg_minutes	stories_posted_change	user_id
0.8444444444	3.3444444444	2.5	0	10
-16.2416666667	3.7583333333	20	1	11
20	45	25	-1	12

Hints

Use LAG() to compute gaps between consecutive events; start a new session when the gap is > 30 minutes.
Aggregate sessions into pre vs post using session_start::date, then compute per-user averages with FILTER.

Daily per-banner CTR (exclude bots and accidental clicks)

Table users

Table events

Expected output

Hints

7-day user CTR and signup-week cohort distribution

Table users

Table events

Expected output

Hints

Sessionization and pre/post change in average session duration (Group Story feature)

Table users

Table events

Expected output

Hints

Table `users`

Table `events`

Table `users`

Table `events`

Table `users`

Table `events`