Analyze User Purchase Behavior in Online Marketplace 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 Technical Screen rounds at Uber.

Q: What role is this question designed for?

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

Question

user_events

+----------+------------+---------------------+-------------+
| user_id  | event_type | event_timestamp     | product_id  |
+----------+------------+---------------------+-------------+
| 101      | view       | 2024-01-02 10:00:00 | 55          |
| 101      | purchase   | 2024-01-02 10:05:00 | 55          |
| 102      | purchase   | 2024-01-03 09:30:00 | 77          |
| 101      | purchase   | 2024-02-01 12:00:00 | 88          |
| 103      | view       | 2024-02-02 08:00:00 | 23          |

##### Scenario

Online marketplace wants to understand user purchase behavior stored in user_events table.

##### Question

SQL: For each user, return the first product_id they purchased and the purchase timestamp. SQL: Count the number of distinct users who made at least two purchases on the same day. SQL: Find the top 3 products by total number of purchases. SQL: Calculate the 7-day rolling average of daily purchases overall. Pandas: Given the same data in DataFrame df, compute daily active users (unique user_id per date).

##### Hints

Use window functions, GROUP BY, DISTINCT, rolling(), and groupby().

PracHub · Accepted Answer

This question evaluates data manipulation and analytical skills in SQL and Python, focusing on time-based aggregation, deduplication and ranking of events, calculation of rolling averages, and computation of daily active user metrics.

Quick Overview

Quick Overview