Katkat
Back

Aggregate User Activity, Fit Regression, Interpret Coefficients

AirbnbData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.