Coding questions · Roblox
Analyze Recent Orders Dataset with Python/pandas
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.
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.
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | order_id | price | user_id |
|---|---|---|---|
| 2024-01-01 | 1 | 20.5 | 101 |
| 2024-01-03 | 2 | 35 | 101 |
| 2024-01-02 | 3 | 15 | 102 |
| 2024-01-04 | 4 | 50 | 103 |
| 2024-01-05 | 5 | 25 | 102 |
Expected output
One row per user with the order having the maximum price (ties broken by earliest date, then smallest order_id).
| order_id | price | user_id |
|---|---|---|
| 2 | 35 | 101 |
| 5 | 25 | 102 |
| 4 | 50 | 103 |
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | order_id | price | user_id |
|---|---|---|---|
| 2024-01-01 | 1 | 20.5 | 101 |
| 2024-01-03 | 2 | 35 | 101 |
| 2024-01-02 | 3 | 15 | 102 |
| 2024-01-04 | 4 | 50 | 103 |
| 2024-01-05 | 5 | 25 | 102 |
Expected output
Single-row result with the overall average order price across all orders.
| avg_price |
|---|
| 29.1 |
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.
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | order_id | price | user_id |
|---|---|---|---|
| 2024-01-01 | 1 | 20.5 | 101 |
| 2024-01-03 | 2 | 35 | 101 |
| 2024-01-02 | 3 | 15 | 102 |
| 2024-01-04 | 4 | 50 | 103 |
| 2024-01-05 | 5 | 25 | 102 |
Expected output
One row per day with total orders and average price for that day.
| avg_price | created_date | total_orders |
|---|---|---|
| 20.5 | 2024-01-01 | 1 |
| 15 | 2024-01-02 | 1 |
| 35 | 2024-01-03 | 1 |
| 50 | 2024-01-04 | 1 |
| 25 | 2024-01-05 | 1 |
Hints
- Group by created_at to aggregate per day
- COUNT(*) for totals and AVG(price) for average