Find daily first-order merchants with SQL

Given the table below, write a single SQL query using window functions to: A) For each calendar date (UTC), return all merchant_id(s) whose order is the first completed order of that date across all merchants. Include date, order_id, merchant_id, order_ts. If multiple orders share the exact earliest timestamp on a date, return all ties. B) Then (in a separate query), for each merchant and date, return that merchant's first completed order for that date. Constraints: ignore rows where status <> 'completed'; avoid per-row correlated subqueries; be efficient on 100M+ rows. Schema:

orders(order_id INT, merchant_id INT, user_id INT, order_ts TIMESTAMP, status VARCHAR)
Sample data:
order_id | merchant_id | user_id | order_ts (UTC) | status
1 | 10 | 100 | 2025-02-01 00:00:05 | completed
2 | 11 | 101 | 2025-02-01 00:00:05 | completed
3 | 10 | 102 | 2025-02-01 03:12:00 | completed
4 | 12 | 103 | 2025-02-02 00:00:01 | cancelled
5 | 11 | 104 | 2025-02-02 00:00:01 | completed
6 | 10 | 105 | 2025-02-02 00:00:01 | completed
7 | 12 | 106 | 2025-02-02 09:00:00 | completed

Follow-up: briefly justify your partitioning/sorting choices and any indexes you would add.

Daily first completed orders across all merchants

You are given an orders table. Using a single SQL query with window functions (no per-row correlated subqueries), return for each calendar date (UTC) all merchant_id values whose order is the first completed order of that date across all merchants.

Requirements:

• Only consider rows where status = 'completed'.
• Use the calendar date in UTC derived from order_ts (i.e., CAST(order_ts AS DATE) or equivalent) as the grouping date.
• For each date, find the earliest order_ts among completed orders.
• If multiple orders share this earliest timestamp on the same date, return all ties.
• Output columns: order_date (DATE), order_id, merchant_id, order_ts.
• Aim for an efficient solution that can scale to 100M+ rows and avoids correlated subqueries.
• Then briefly explain (in words) which partitioning and sorting keys your window function uses, and what index(es) you would add to support this query on a large table.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
merchant_id	INT	NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

merchant_id	order_id	order_ts	status	user_id
10	1	2025-02-01 00:00:05	completed	100
11	2	2025-02-01 00:00:05	completed	101
10	3	2025-02-01 03:12:00	completed	102
12	4	2025-02-02 00:00:01	cancelled	103
11	5	2025-02-02 00:00:01	completed	104
10	6	2025-02-02 00:00:01	completed	105
12	7	2025-02-02 09:00:00	completed	106

Expected output

For each UTC calendar date, return all completed orders whose order_ts is the minimum (earliest) completed timestamp on that date. Because ties are included, there can be multiple rows per date. On the sample data, 2025-02-01 has two completed orders at the earliest timestamp (order_id 1 and 2 at 00:00:05), and 2025-02-02 has two completed orders at the earliest timestamp (order_id 5 and 6 at 00:00:01), so four rows are returned in total.

Partitioning/sorting justification: use a window function partitioned by CAST(order_ts AS DATE) (the order_date) and ordered by order_ts ascending, so the earliest timestamp per date gets rank 1. DENSE_RANK (or an equivalent min-timestamp comparison) ensures ties at the same earliest timestamp are all included.

Indexing suggestion: on a large table, create a composite index such as CREATE INDEX idx_orders_status_ts ON orders(status, order_ts); this allows the database to quickly filter completed orders and then read them in timestamp order, which is ideal for the window computation.

merchant_id	order_date	order_id	order_ts
10	2025-02-01	1	2025-02-01 00:00:05
11	2025-02-01	2	2025-02-01 00:00:05
11	2025-02-02	5	2025-02-02 00:00:01
10	2025-02-02	6	2025-02-02 00:00:01

Hints

1. Filter to status = 'completed' before applying window functions.
2. Use DENSE_RANK() or MIN(order_ts) as a window function partitioned by the order date (CAST(order_ts AS DATE)).
3. Keep only rows with the smallest rank or where order_ts equals the per-date minimum to include ties.

Daily first completed order per merchant

Using the same orders table, write a single SQL query with window functions (no per-row correlated subqueries) to return, for each merchant and each calendar date (UTC), that merchant's first completed order for that date.

Requirements:

• Only consider rows where status = 'completed'.
• Use the calendar date in UTC derived from order_ts (i.e., CAST(order_ts AS DATE)) as the grouping date.
• For each merchant_id and date, identify the earliest order_ts among that merchant's completed orders for the date.
• If multiple orders for the same merchant and date share the exact earliest timestamp, break ties deterministically using the smallest order_id.
• Output columns: order_date (DATE), merchant_id, order_id, order_ts.
• Aim for an efficient solution that can scale to 100M+ rows and avoids correlated subqueries.
• Then briefly explain (in words) which partitioning and sorting keys your window function uses, and what index(es) you would add.

Render order_date as YYYY-MM-DD and order_ts as YYYY-MM-DD HH24:MI:SS.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
merchant_id	INT	NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

merchant_id	order_id	order_ts	status	user_id
10	1	2025-02-01 00:00:05	completed	100
11	2	2025-02-01 00:00:05	completed	101
10	3	2025-02-01 03:12:00	completed	102
12	4	2025-02-02 00:00:01	cancelled	103
11	5	2025-02-02 00:00:01	completed	104
10	6	2025-02-02 00:00:01	completed	105
12	7	2025-02-02 09:00:00	completed	106

Expected output

For each merchant_id and UTC calendar date, return exactly one row representing that merchant's earliest completed order on that date. If multiple orders for a given merchant and date share the same earliest timestamp, choose the one with the smallest order_id.

On the sample data, the first completed orders per merchant per date are:

• Merchant 10: 2025-02-01 -> order_id 1 at 00:00:05; 2025-02-02 -> order_id 6 at 00:00:01.
• Merchant 11: 2025-02-01 -> order_id 2 at 00:00:05; 2025-02-02 -> order_id 5 at 00:00:01.
• Merchant 12: 2025-02-02 -> order_id 7 at 09:00:00 (order_id 4 is cancelled and ignored). So five rows are returned in total.

Partitioning/sorting justification: use a window function partitioned by (merchant_id, CAST(order_ts AS DATE)) and ordered by order_ts ascending, then by order_id ascending to break ties; ROW_NUMBER() = 1 picks the single first order per merchant per day.

Indexing suggestion: on a large table, a composite index like CREATE INDEX idx_orders_status_merchant_ts ON orders(status, merchant_id, order_ts); lets the database efficiently filter to completed orders and scan them ordered by merchant and timestamp, which is ideal for the window function.

merchant_id	order_date	order_id	order_ts
10	2025-02-01	1	2025-02-01 00:00:05
10	2025-02-02	6	2025-02-02 00:00:01
11	2025-02-01	2	2025-02-01 00:00:05
11	2025-02-02	5	2025-02-02 00:00:01
12	2025-02-02	7	2025-02-02 09:00:00

Hints

1. Filter to completed orders before applying the window function.
2. Partition the window function by both merchant_id and the order date (CAST(order_ts AS DATE)).
3. Use ROW_NUMBER() ordered by order_ts (and order_id as a tie-breaker) to pick the first completed order per merchant per day.
4. Use TO_CHAR for date/timestamp display in PostgreSQL.