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.
Overview: 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.
You are given an ORDERS table for an e-commerce platform:
- order_id: unique identifier of the order
- date: calendar date of the order
- category: product category of the order
- customer_id: unique identifier of the customer
Task:
1) For each calendar day and product category, compute the proportion of orders that are the customer's first order in that category.
2) For each calendar day, compute the proportion of all orders that are a customer’s first order across any category.
Return both metrics in a single result set. Use category = 'ALL' for the per-day overall proportion rows and label metrics using a column named metric (e.g., 'first_in_category_rate' and 'first_overall_rate').
Tables
ORDERS(order_id INTEGER, date DATE, category VARCHAR(50), customer_id INTEGER)
Hints
- Use ROW_NUMBER() over partitions by (customer_id, category) to flag a customer’s first order in each category.
- Use ROW_NUMBER() over partitions by customer_id (without category) to flag each customer’s first order overall.