Katkat
Back

Identify First Daily Order for Each Merchant

AmazonData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.