Aggregate User Activity, Fit Regression, Interpret Coefficients
Company: Airbnb
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
user_metrics
+----------+------------+---------+--------+-----------+
| user_id | activity_dt| variant | clicks | purchases |
+----------+------------+---------+--------+-----------+
| 101 | 2023-05-01 | A | 12 | 1 |
| 102 | 2023-05-01 | B | 4 | 0 |
| 103 | 2023-05-02 | A | 6 | 1 |
| 104 | 2023-05-02 | B | 9 | 2 |
| 105 | 2023-05-03 | A | 3 | 0 |
+----------+------------+---------+--------+-----------+
##### Scenario
Given relational event data, you must write SQL and Python to build a modeling dataset and run a regression.
##### Question
Write SQL to aggregate daily user activity into features.
2) In Python, fit a linear (or logistic) regression and interpret coefficients.
##### Hints
Use window functions for rolling metrics; in Python rely on pandas and statsmodels or sklearn.
Overview: This question evaluates skills in relational data aggregation and feature engineering using SQL alongside fitting and interpreting regression models in Python, targeting competencies in transforming event-level user metrics into a modeling dataset and interpreting coefficient estimates.
Using the user_metrics table, write a single SQL query that produces one row per user per activity date with features suitable for a regression model. For each user_id and activity_dt, include: (1) base features purchase_flag (1 if purchases > 0 else 0), conv_rate (purchases / clicks with zero-clicks handled), and is_variant_B (1 if variant = 'B' else 0); and (2) 7-day rolling variant-level features based on daily totals for that variant: variant_7d_clicks, variant_7d_purchases, and variant_7d_conv_rate = variant_7d_purchases / variant_7d_clicks. Assume a 7-day rolling window implemented as the current row plus the previous 6 rows for that variant, ordered by activity_dt.
Tables
user_metrics(user_id INTEGER, activity_dt DATE, variant VARCHAR(1), clicks INTEGER, purchases INTEGER)
Hints
- First aggregate to daily per-variant totals, then apply a window SUM over the previous 6 rows plus current row for each variant.
- Join the rolling variant aggregates back to the user-level rows and use CASE expressions to build flags and protect against division by zero.