Quick Overview

This question evaluates proficiency in SQL-based panel data construction, time-series aggregation and rolling-window metrics together with applied causal inference in Python, including two-way fixed-effects difference-in-differences, clustered standard errors, lead tests, and count-model re-specification; it targets Data Manipulation (SQL/Python) skills relevant for Data Scientist roles. It is commonly asked because it combines practical implementation and conceptual understanding—testing temporal joins, treatment timing, avoidance of lookahead/immortal-time biases and late-adopter issues, interpretation of interaction coefficients and confidence intervals, and the ability to reason about identification and model choice.

Build panel in SQL; run causal regression

Company: Airbnb

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Assume today is 2025-09-01 (UTC). Schema and small samples: users(user_id INT, country STRING, signup_date DATE, platform STRING) Sample: user_id | country | signup_date | platform 1 | US | 2025-08-25 | ios 2 | US | 2025-08-20 | android 3 | CA | 2025-07-10 | web 4 | US | 2025-08-31 | ios 5 | GB | 2025-06-15 | android exposures(user_id INT, ts TIMESTAMP, treatment INT) Sample: user_id | ts | treatment 1 | 2025-08-30T10:00:00Z | 1 1 | 2025-09-01T09:00:00Z | 1 2 | 2025-08-29T12:00:00Z | 0 3 | 2025-08-28T08:00:00Z | 1 5 | 2025-08-26T14:00:00Z | 0 orders(order_id INT, user_id INT, ts TIMESTAMP, amount DECIMAL(10,2)) Sample: order_id | user_id | ts | amount 10 | 1 | 2025-08-30T11:00:00Z | 25.00 11 | 2 | 2025-08-29T13:00:00Z | 9.99 12 | 3 | 2025-08-31T09:00:00Z | 12.00 13 | 1 | 2025-09-01T10:15:00Z | 5.00 14 | 4 | 2025-09-01T16:00:00Z | 20.00 geo_rollout(country STRING, launch_ts TIMESTAMP) Sample: country | launch_ts US | 2025-08-29T00:00:00Z CA | 2025-08-27T00:00:00Z GB | 2025-08-31T00:00:00Z Tasks: SQL A: Build a user-day panel for dates d in [2025-08-25, 2025-09-01]. For each user_id and date d, output: date, user_id, country, treated (1 if EXISTS exposure with ts <= d 23:59:59Z and treatment=1), post (1 if date(d) >= date(launch_ts for user’s country)), revenue_d (sum of order amounts with ts on d), active_d (1 if revenue_d > 0 OR EXISTS exposure on d), and signup_age_days. Ensure no duplicate user-days and fill missing user-days with zeros where appropriate. SQL B: Using the panel, compute for US and GB the 7-day rolling revenue per active user on each day d in [2025-08-26, 2025-09-01], where active user means active_d=1 within the 7-day window. Clarify how you handle users with zero activity and avoid lookahead bias. Python: From the panel, estimate a two-way fixed-effects DiD: revenue_d ~ post * treated + user FE + day FE, clustering SEs at the user level. (1) Interpret the interaction coefficient economically; (2) test for pre-trends using leads; (3) address late adopters/immortal time bias; (4) re-estimate with a count model (Poisson with exposure offset) and compare; (5) report a 95% CI and a practical recommendation.

Overview: This question evaluates proficiency in SQL-based panel data construction, time-series aggregation and rolling-window metrics together with applied causal inference in Python, including two-way fixed-effects difference-in-differences, clustered standard errors, lead tests, and count-model re-specification; it targets Data Manipulation (SQL/Python) skills relevant for Data Scientist roles. It is commonly asked because it combines practical implementation and conceptual understanding—testing temporal joins, treatment timing, avoidance of lookahead/immortal-time biases and late-adopter issues, interpretation of interaction coefficients and confidence intervals, and the ability to reason about identification and model choice.

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

Build a User-Day Panel with Treatment, Post, Activity, and Revenue Flags

Using the schema below, build a **user-day panel** for the inclusive date range **2025-08-25 through 2025-09-01**. Emit one row for each `(user_id, d)` pair where the calendar date `d` is on or after that user's `signup_date` (so `signup_age_days` is never negative). Each user therefore contributes rows only for the dates in the window that fall on/after their signup. **Input tables** - `users(user_id, country, signup_date, platform)` — one row per user. - `exposures(user_id, ts, treatment)` — experiment exposures; `treatment` is 1 (treated) or 0 (control). - `orders(order_id, user_id, ts, amount)` — completed orders with their revenue `amount`. - `geo_rollout(country, launch_ts)` — the feature launch timestamp per country (a country may be absent). **For each `(user_id, d)` output exactly these columns, in this order:** - `d` (DATE) — the calendar date. - `user_id` (INT). - `country` (the user's country). - `treated` (INT) — `1` if the user has at least one exposure with `treatment = 1` whose exposure date `DATE(ts)` is on or before `d`; otherwise `0`. (Once a user is first treated, they stay treated on every later day.) - `post` (INT) — `1` if the user's country appears in `geo_rollout` and `d >= DATE(launch_ts)` for that country; otherwise `0`. A user whose country is not in `geo_rollout` always has `post = 0`. - `revenue_d` (NUMERIC) — the sum of `orders.amount` for that user on date `d` (i.e. orders whose `DATE(ts) = d`); `0.00` if the user had no orders that day. - `active_d` (INT) — `1` if `revenue_d > 0` **OR** the user has any exposure (treated or control) with `DATE(ts) = d`; otherwise `0`. - `signup_age_days` (INT) — the number of days between `d` and the user's signup, computed as `d - signup_date`. There must be **no duplicate `(user_id, d)` rows**, and every in-window day on/after the user's signup must be present (with `revenue_d = 0.00` and `active_d = 0` on days with no orders and no exposures). **Order the result by `d` ascending, then by `user_id` ascending.**

Tables

users(user_id INT, country VARCHAR(2), signup_date DATE, platform VARCHAR(10))

exposures(user_id INT, ts TIMESTAMP, treatment INT)

orders(order_id INT, user_id INT, ts TIMESTAMP, amount DECIMAL(10,2))

geo_rollout(country VARCHAR(2), launch_ts TIMESTAMP)

Hints

  1. Generate the date window with `generate_series(...)::date` and cross join it to `users`, then filter `d >= signup_date`.
  2. `treated` is sticky: take each user's earliest `treatment = 1` exposure date and flag every day on or after it.

Compute 7-Day Rolling Revenue per Active User from a User-Day Panel

Assume you already built the user-day panel from SQL A and stored it in a table user_day_panel(d, user_id, country, treated, post, revenue_d, active_d, signup_age_days). Using only this panel table, compute for countries US and GB the 7-day rolling revenue per active user for each date d in [2025-08-26, 2025-09-01] inclusive. Define the 7-day window for a given date d as the dates from (d - 6 days) through d, inclusive. For each country and date d: - revenue_7d: sum of revenue_d over all user-day rows in that country whose date is in the 7-day window. - active_users_7d: number of distinct users in that country who have active_d = 1 on at least one day within the 7-day window. - rev_per_active_7d: revenue_7d divided by active_users_7d, rounded to 2 decimal places. If active_users_7d = 0, return NULL for rev_per_active_7d. Do not use any data from dates after d when computing the metric (avoid lookahead). Return the columns: d, country, revenue_7d, active_users_7d, rev_per_active_7d.

Tables

user_day_panel(d DATE, user_id INT, country VARCHAR(2), treated INT, post INT, revenue_d DECIMAL(10,2), active_d INT, signup_age_days INT)

Hints

  1. First aggregate daily revenue by (d, country) and use a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to compute revenue_7d.
  2. To count distinct active users in the 7-day window, use a separate active_days CTE and a correlated subquery (or join) bounded between d-6 and d to avoid lookahead.

Loading coding console...