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.
Quick Answer: 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.