Compute First Order Proportions by Day and Category
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
ORDERS
+----------+------------+----------+-------------+
| order_id | date | category | customer_id |
+----------+------------+----------+-------------+
| 100 | 2025-01-01 | Books | 1 |
| 101 | 2025-01-02 | Books | 1 |
| 102 | 2025-01-01 | Music | 2 |
| 103 | 2025-01-03 | Music | 3 |
| 104 | 2025-01-03 | Books | 2 |
+----------+------------+----------+-------------+
##### Scenario
E-commerce platform analyzing customer purchasing behavior over time.
##### Question
For each calendar day and product category, compute the proportion of orders that are the customer's first order in that category. For each calendar day, compute the proportion of all orders that are a customer’s first order across any category.
##### Hints
Use window functions or self-joins to flag first orders, then aggregate by day and category.
Quick Answer: This question evaluates a candidate's ability to perform event-level data manipulation and customer-level deduplication using SQL or Python, focusing on calculating the proportion of orders that represent a customer's first purchase by day and category.