Compute User Retention and Analyze Event Data

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 Onsite rounds at Snapchat.

Q: What role is this question designed for?

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

Question

user_events

+---------+---------------------+------------+-------+
| user_id | event_time          | event_type | page  |
+---------+---------------------+------------+-------+
| 101     | 2023-04-01 10:00:00 | click      | home  |
| 101     | 2023-04-02 11:00:00 | purchase   | cart  |
| 102     | 2023-04-01 12:30:00 | click      | home  |
| 103     | 2023-04-03 09:15:00 | impression | deal  |
| 102     | 2023-04-04 14:20:00 | purchase   | cart  |
+---------+---------------------+------------+-------+

##### Scenario

Data team maintains a user_events fact table tracking every page view, click and purchase.  Leadership wants ad-hoc SQL insights.

##### Question

Write a query to compute 7-day user retention (inclusive) for users who first visited on '2023-04-01'. Return the top 3 pages by distinct purchasing users last month. Find the median number of daily events per active user in April 2023. In Python, given a Pandas DataFrame identical to user_events, produce an hourly time-series of clicks per page for the past 24 hours.

##### Hints

Window functions, CTEs, DATE_DIFF and groupby should all be considered.

PracHub · Accepted Answer

This question evaluates a candidate's ability to perform SQL and Python data manipulation for event analytics, including cohort retention, aggregation of distinct purchasing users, median-of-daily-events calculations, and hourly time-series grouping.

Quick Overview

Quick Overview