Find daily first-order merchants with SQL
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency with SQL window functions, set-based data manipulation, timestamp-based grouping and query optimization within the Data Manipulation (SQL/Python) domain for a Data Scientist role, emphasizing practical application rather than purely conceptual understanding.
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.
Tables
orders(order_id INT, merchant_id INT, user_id INT, order_ts TIMESTAMP, status VARCHAR(20))
Hints
- Filter to status = 'completed' before applying window functions.
- Use DENSE_RANK() or MIN(order_ts) as a window function partitioned by the order date (CAST(order_ts AS DATE)).
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`.
Tables
orders(order_id INT, merchant_id INT, user_id INT, order_ts TIMESTAMP, status VARCHAR(20))
Hints
- Filter to completed orders before applying the window function.
- Partition the window function by both merchant_id and the order date (CAST(order_ts AS DATE)).
Community answers
Answer by vineetmalviya03
i ran the right solution in compiler but it still shows the error :: WRONG ANSWER - Your code executed but produced incorrect results.