Aggregate User Activity, Fit Regression, Interpret Coefficients

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Airbnb.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Airbnb during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview