Analyze Recent Orders Dataset with Python/pandas
Company: Roblox
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
orders
| order_id | user_id | price | created_at |
|----------|---------|-------|------------|
| 1 | 101 | 20.5 | 2024-01-01 |
| 2 | 101 | 35.0 | 2024-01-03 |
| 3 | 102 | 15.0 | 2024-01-02 |
| 4 | 103 | 50.0 | 2024-01-04 |
| 5 | 102 | 25.0 | 2024-01-05 |
##### Scenario
E-commerce analytics team needs quick Python insights on recent orders dataset.
##### Question
Using Python/pandas: a) For every user, return the order_id with the maximum price. b) Compute the overall average order price. c) For each calendar day, report total orders and average price.
##### Hints
Think groupby, idxmax, agg, reset_index.
Overview: This question evaluates proficiency in data manipulation and aggregation using Python/pandas, including extracting per-user maximums, computing overall summary statistics, and producing daily aggregates from time-stamped records.
Max-priced order per user
For each user, return the order_id and price of their highest-priced order. Break ties by earliest created_at, then smallest order_id.
Tables
orders(order_id INTEGER, user_id INTEGER, price DECIMAL(10,2), created_at DATE)
Hints
- Use ROW_NUMBER() partitioned by user_id
- Order by price DESC and tie-break by date then order_id
Overall average order price
Compute the average price across all orders in the table.
Tables
orders(order_id INTEGER, user_id INTEGER, price DECIMAL(10,2), created_at DATE)
Hints
- Use the AVG aggregate over the price column
Daily totals and average price
For each calendar day, report the total number of orders and the average order price. Order results by date ascending.
Tables
orders(order_id INTEGER, user_id INTEGER, price DECIMAL(10,2), created_at DATE)
Hints
- Group by created_at to aggregate per day
- COUNT(*) for totals and AVG(price) for average