Quick Overview

This question evaluates a candidate's ability to perform event-level data manipulation and analytics in Python and SQL, covering sessionization, time-zone–consistent timestamp handling, deduplication, funnel construction, segment-level aggregation, conversion-rate computation, and statistical confidence intervals such as the Wilson CI.

Compute browsing metrics in Python from logs

Company: Airbnb

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Given event logs, write idiomatic Pandas to compute segment-level metrics and a funnel. Data schema: events(event_id, ts_utc, guest_id, device in {desktop, mobile}, traffic_source in {direct, seo, sem, email, partner}, event_type in {page_view, add_to_cart, checkout_start, order_completed}, page_url, user_agent), guests(guest_id, signup_dt_utc). Definitions: sessionize per guest with a 30-minute inactivity timeout; drop bot traffic where user_agent ILIKE '%bot%' OR per-guest valid events<2; treat a conversion as an order_completed event. Use the window last 7 days relative to “today” where today=2025-09-01, i.e., 2025-08-26 through 2025-09-01 inclusive. Tasks: 1) Create sessions with session_id and session_start/end, ensuring time zone consistency (UTC) and stable sessionization across devices. 2) For each segment (traffic_source, device), compute: unique guests, sessions, session-level conversion rate (sessions with ≥1 order_completed / sessions), guest-level conversion rate (guests with ≥1 order_completed / guests), average sessions-to-first-order per guest, and median time from first page_view to first order. 3) Build a de-duplicated guest funnel across the window: page_view → add_to_cart → checkout_start → order_completed; report step-through rates and absolute drop-offs, counting each guest once at their highest attained step. 4) Exclude guests with only bot traffic and segments with <100 sessions; output the top 5 segments by lift vs. sitewide guest-level conversion (report lift and 95% Wilson CI for the segment conversion). 5) Clearly state and implement how you handle guests who order without prior events in the window (carry their first event from the previous 24h if available; otherwise treat as direct single-step). Provide Pandas code (vectorized; no per-row Python loops) and explain how it scales if moved to PySpark.

Overview: This question evaluates a candidate's ability to perform event-level data manipulation and analytics in Python and SQL, covering sessionization, time-zone–consistent timestamp handling, deduplication, funnel construction, segment-level aggregation, conversion-rate computation, and statistical confidence intervals such as the Wilson CI.

Sessionize guest events with a 30-minute timeout (exclude bots and low-activity non-converters)

You are given UTC event logs. Analysis window (inclusive): 2025-05-26 through 2025-06-01. Use timestamps in UTC. Rules: 1) Exclude bot events where user_agent ILIKE '%bot%'. 2) Exclude guests who have fewer than 2 non-bot events in the analysis window AND have 0 order_completed events in the analysis window. 3) Sessionize remaining non-bot events per guest using a 30-minute inactivity timeout (a new session starts when the time since the previous event for that guest is > 30 minutes). Sessionization must be stable across devices (i.e., partition only by guest_id, not by device). 4) Consider events from 2025-05-25 through 2025-06-01 for sessionization so sessions may start before the analysis window, but output only sessions that contain at least one event in the analysis window. Task: Return one row per session with: guest_id, session_id, session_start_utc, session_end_utc, event_count, has_order_completed (0/1), session_traffic_source, session_device. Format the two timestamp columns as `YYYY-MM-DD HH24:MI:SS`. Define session_id as '<guest_id>-<session_number>' where session_number starts at 1 for each guest in time order.

Tables

guests(guest_id INT, signup_dt_utc TIMESTAMP)

events(event_id INT, ts_utc TIMESTAMP, guest_id INT, device VARCHAR(10), traffic_source VARCHAR(10), event_type VARCHAR(20), page_url VARCHAR(200), user_agent VARCHAR(200))

Hints

  1. Filter bot events before computing guest-level eligibility.
  2. Use `LAG` per guest to detect gaps greater than 30 minutes, then a running sum to assign session numbers.

Segment-level session and guest conversion metrics (including sessions-to-first-order and median time-to-order)

Using the same tables and the same filtering rules as Question 1 (exclude bot events; exclude guests with <2 non-bot events in the analysis window and 0 orders), compute segment-level metrics for the analysis window 2025-05-26 through 2025-06-01. Definitions: - Sessions are per guest with a 30-minute inactivity timeout, sessionized across devices. - Attribute each session to (traffic_source, device) of the first event in the session. - Conversion is an order_completed event in the analysis window. - Guest-level conversion rate = (guests with ≥1 order_completed in the window) / (guests with ≥1 session in the segment in the window). - Session-level conversion rate = (sessions with ≥1 order_completed in the window) / (sessions in the segment in the window). - sessions_to_first_order per guest = the session_number (starting at 1) of the session that contains that guest’s first order_completed in the window. - time_to_first_order_minutes per guest = minutes between the guest’s first order_completed in the window and the earliest page_view in the previous 24 hours (including in-window page_views). If no such page_view exists, the value is NULL. Task: For each segment (traffic_source, device), return: - unique_guests - sessions - session_conversion_rate - guest_conversion_rate - avg_sessions_to_first_order (average over converting guests in the segment) - median_minutes_pv_to_first_order (median over converting guests in the segment, ignoring NULL times) Order results by sessions DESC, then traffic_source, then device. Use PostgreSQL numeric casts for rate calculations that are rounded to a fixed number of decimal places.

Tables

guests(guest_id INT, signup_dt_utc TIMESTAMP)

events(event_id INT, ts_utc TIMESTAMP, guest_id INT, device VARCHAR(10), traffic_source VARCHAR(10), event_type VARCHAR(20), page_url VARCHAR(200), user_agent VARCHAR(200))

Hints

  1. Build sessions in a CTE and then attribute each session to the first event’s traffic_source and device using ARRAY_AGG(... ORDER BY ...)[1].
  2. Compute first_order_ts per guest in-window, then compute sessions_to_first_order by taking the MIN(session_number) among in-window orders.

Guest funnel (strict PV → ATC → CS → OC) and orders without page views

Using the same filtering rules as Question 1 (exclude bot events; exclude guests with <2 non-bot events in the analysis window and 0 orders), build a strict, de-duplicated guest funnel for the analysis window 2025-05-26 through 2025-06-01. Carry-in rule (to handle orders with no prior in-window activity): for page_view and add_to_cart only, also consider events in the 24 hours before the window start (i.e., from 2025-05-25 00:00:00 through 2025-05-25 23:59:59). Strict funnel definition: - Step 1 (page_view): guest has a page_view (in the window or in the 24-hour carry-in period). - Step 2 (add_to_cart): guest has BOTH page_view and add_to_cart (page_view/add_to_cart can be in carry-in or window). - Step 3 (checkout_start): guest has page_view, add_to_cart, AND checkout_start (checkout_start must be in the window). - Step 4 (order_completed): guest has page_view, add_to_cart, checkout_start, AND order_completed (order_completed must be in the window). Task: Return rows for each step with: - step_name - guests_reached - step_through_rate_to_next (NULL for the last step) - dropoff_abs_to_next (NULL for the last step) Also include an extra row with step_name='order_completed_without_page_view' and guests_reached equal to the number of guests who have an order_completed in the window but NO page_view in the carry-in+window. Use PostgreSQL numeric casts for rate calculations that are rounded to a fixed number of decimal places.

Tables

guests(guest_id INT, signup_dt_utc TIMESTAMP)

events(event_id INT, ts_utc TIMESTAMP, guest_id INT, device VARCHAR(10), traffic_source VARCHAR(10), event_type VARCHAR(20), page_url VARCHAR(200), user_agent VARCHAR(200))

Hints

  1. Compute per-guest boolean flags for each funnel event type using MAX(CASE WHEN ... THEN 1 ELSE 0 END).
  2. Use FILTER or CASE expressions in COUNT() to produce strict funnel step counts.

Top segments by lift vs. sitewide guest conversion, with Wilson 95% CI (apply min session threshold)

Using the same sessionization and filtering rules as Question 2 (analysis window 2025-05-26 through 2025-06-01), produce the top segments by lift in guest-level conversion rate versus the sitewide guest-level conversion rate. Definitions: - Segment = (traffic_source, device), based on the first event in each session. - Segment guest conversion rate = converting_guests / unique_guests, where converting_guests are guests with ≥1 order_completed in the window. - Sitewide guest conversion rate = (distinct guests with ≥1 order_completed in the window) / (distinct guests with ≥1 session in the window), after applying the same bot and guest-exclusion rules. - Lift = segment_guest_conversion_rate / sitewide_guest_conversion_rate. - For this exercise, exclude segments with fewer than 2 sessions in the window. Task: Return up to the top 5 segments ordered by lift DESC, and include: - traffic_source, device - sessions - unique_guests - converting_guests - segment_guest_conversion_rate - sitewide_guest_conversion_rate - lift_vs_sitewide - wilson_low_95, wilson_high_95 (95% Wilson score interval for the segment guest conversion rate, using z=1.96) Round rates, lift, and CI bounds to 4 decimals. Use PostgreSQL numeric casts for rate calculations that are rounded to a fixed number of decimal places.

Tables

guests(guest_id INT, signup_dt_utc TIMESTAMP)

events(event_id INT, ts_utc TIMESTAMP, guest_id INT, device VARCHAR(10), traffic_source VARCHAR(10), event_type VARCHAR(20), page_url VARCHAR(200), user_agent VARCHAR(200))

Hints

  1. Compute sitewide conversion once (after filtering) and CROSS JOIN it into your segment query to compute lift.
  2. Implement the Wilson interval formula using numeric arithmetic and z=1.96.

Loading coding console...