Quick Overview

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.

Compute CTR and metrics with pandas

Company: Snapchat

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

Read the full Snapchat Data Scientist interview experience this question came from

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: 1) Only include events from **non-bot** users (`users.is_bot = FALSE`). 2) Impressions are `event_type = 'banner_impression'`. 3) Clicks are `event_type = 'banner_click'`, but exclude **accidental clicks**. 4) 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`.

Tables

users(user_id INT, is_bot BOOLEAN, signup_dt DATE)

events(event_id INT, user_id INT, ts TIMESTAMPTZ, event_type VARCHAR(40), banner_id INT, story_id INT, group_id INT, dwell_ms INT)

Hints

  1. Use LEAD() to check whether a click is followed by back_navigation within 2 seconds.
  2. 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`.

Tables

users(user_id INT, is_bot BOOLEAN, signup_dt DATE)

events(event_id INT, user_id INT, ts TIMESTAMPTZ, event_type VARCHAR(40), banner_id INT, story_id INT, group_id INT, dwell_ms INT)

Hints

  1. Compute user-level impressions and valid clicks first, then compute user CTR.
  2. 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`.

Tables

users(user_id INT, is_bot BOOLEAN, signup_dt DATE)

events(event_id INT, user_id INT, ts TIMESTAMPTZ, event_type VARCHAR(40), banner_id INT, story_id INT, group_id INT, dwell_ms INT)

Hints

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

Loading coding console...