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.

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.

How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Snapchat.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Snapchat during technical interviews.

Compute CTR and metrics with pandas | Snapchat Interview Question

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.

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.

Compute CTR and metrics with pandas

Quick Overview

Compute CTR and metrics with pandas

Write your answer

Compute CTR and metrics with pandas

Quick Overview

Compute CTR and metrics with pandas

Write your answer