Identify First Daily Order for Each Merchant

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.

First order per merchant per day

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.

Table Orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
merchant_id	VARCHAR(10)	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL

Sample data:

amount	merchant_id	order_date	order_id
120.5	M01	2023-05-01	101
75	M02	2023-05-01	102
90	M01	2023-05-02	103
250	M03	2023-05-02	104
110	M01	2023-05-03	105

Expected output

One row per merchant per calendar date, containing the first order_id of that day for that merchant. Since there is no order_time column, earliest is determined by the smallest order_id within each merchant_id and order_date.

merchant_id	order_date	order_id
M01	2023-05-01	101
M02	2023-05-01	102
M01	2023-05-02	103
M03	2023-05-02	104
M01	2023-05-03	105

Hints

1. Use a window function with ROW_NUMBER() partitioned by merchant_id and order_date.
2. Order the window by order_id to simulate the earliest order when no order_time is available.
3. Filter to rows where ROW_NUMBER() = 1.