Quick Overview

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.

Analyze User Purchase Behavior in Online Marketplace Data

Company: Uber

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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().

Overview: 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.

First Purchase per User

For each user who made at least one purchase, return the first product they purchased. Use the `user_events` table and consider only rows where `event_type = 'purchase'`. Return these columns: - `user_id` - `first_product_id`: the `product_id` from the user's earliest purchase event - `first_purchase_timestamp`: the earliest purchase timestamp formatted as `YYYY-MM-DD HH24:MI:SS` Order the result by `user_id`.

Tables

user_events(user_id INTEGER, event_type VARCHAR(20), event_timestamp TIMESTAMP, product_id INTEGER)

Hints

  1. Filter to purchase events before ranking.
  2. Use ROW_NUMBER() partitioned by user_id and ordered by event_timestamp.

Two purchases on the same day per user

Count the number of distinct users who made at least two purchases on the same calendar date.

Tables

user_events(user_id INTEGER, event_type VARCHAR(20), event_timestamp TIMESTAMP, product_id INTEGER)

Hints

  1. Group by user_id and purchase date.
  2. Use HAVING COUNT(*) >= 2 to keep dates with at least two purchases.

Top products by purchases

Find the top 3 products by total number of purchases, breaking ties by product_id in ascending order.

Tables

user_events(user_id INTEGER, event_type VARCHAR(20), event_timestamp TIMESTAMP, product_id INTEGER)

Hints

  1. Filter to rows where event_type = 'purchase'.
  2. Group by product_id and order by the purchase count descending, then product_id ascending.

7-day rolling average of daily purchases

Calculate the 7-day rolling average of daily purchases overall, using a window of the current date and the previous 6 calendar days.

Tables

user_events(user_id INTEGER, event_type VARCHAR(20), event_timestamp TIMESTAMP, product_id INTEGER)

Hints

  1. First aggregate purchases to get a daily count per purchase_date.
  2. Then apply a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to compute the 7-row rolling average.

Daily active users (DAU)

Compute daily active users (count of unique user_id per calendar date) across all event types.

Tables

user_events(user_id INTEGER, event_type VARCHAR(20), event_timestamp TIMESTAMP, product_id INTEGER)

Hints

  1. Convert event_timestamp to a DATE to group by calendar day.
  2. Use COUNT(DISTINCT user_id) to get daily active users.

Community answers

Answer by SS

-- Write your SQL query hereSELECT product_id , count(*) as purchase_countfrom user_eventswhere event_type = 'purchase'group by 1order by 2 desc limit 3

Loading coding console...