Identify First Daily Order for Each Merchant
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Orders
+----------+-------------+---------+------------+
| order_id | merchant_id | amount | order_date |
+----------+-------------+---------+------------+
| 101 | M01 | 120.50 | 2023-05-01 |
| 102 | M02 | 75.00 | 2023-05-01 |
| 103 | M01 | 90.00 | 2023-05-02 |
| 104 | M03 | 250.00 | 2023-05-02 |
| 105 | M01 | 110.00 | 2023-05-03 |
+----------+-------------+---------+------------+
##### Scenario
E-commerce platform wants to know which order was each merchant’s first transaction on any given day.
##### Question
Using SQL, return every merchant_id together with the order_id that represents that merchant’s first order for each calendar date.
##### Hints
Partition by merchant_id and order_date, order by order_time or order_id; use ROW_NUMBER() = 1.
Overview: This question evaluates proficiency in data manipulation with SQL and related tools, focusing on identifying the first transaction per merchant for each calendar date and handling ordering and deduplication within grouped transactional records.
Using SQL, return every merchant_id together with the order_id that represents that merchant’s first order for each calendar date. Assume that in the absence of an order_time column, the earliest order is the one with the smallest order_id within each merchant_id and order_date.
Tables
Orders(order_id INTEGER, merchant_id VARCHAR(10), amount DECIMAL(10,2), order_date DATE)
Hints
- Use a window function with ROW_NUMBER() partitioned by merchant_id and order_date.
- Order the window by order_id to simulate the earliest order when no order_time is available.