Latest Order Day Per Product

Medium

A commerce intelligence team needs a report that highlights each product's freshest purchase activity. The platform stores three tables: customers(customer_id, name) orders(order_id, order_date, customer_id, product_id) products(product_id, product_name, price) Business rules: order_id is unique. product_id is unique in products. A customer cannot place more than one order for the same product on the same calendar day. Task: For every product that appears in the orders table, return all orders that occurred on that product's latest order_date. If a product has never been ordered, it must not appear in the output. Output requirements: Return columns in this exact order: product_name product_id order_id order_date Sort the final result by: product_name in ascending order product_id in ascending order order_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customers:
| customer_id | name      |
|-------------|-----------|
| 1           | Winston   |
| 2           | Jonathan  |
| 3           | Annabelle |
| 4           | Marwan    |
| 5           | Khaled    |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 1        | 2020-07-31  | 1           | 1          |
| 2        | 2020-07-30  | 2           | 2          |
| 3        | 2020-08-29  | 3           | 3          |
| 4        | 2020-07-29  | 4           | 1          |
| 5        | 2020-06-10  | 1           | 2          |
| 6        | 2020-08-01  | 2           | 1          |
| 7        | 2020-08-01  | 3           | 1          |
| 8        | 2020-08-03  | 1           | 2          |
| 9        | 2020-08-07  | 2           | 3          |
| 10       | 2020-07-15  | 1           | 2          |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 1          | keyboard     | 120   |
| 2          | mouse        | 80    |
| 3          | screen       | 600   |
| 4          | hard disk    | 450   |

Output

[
  {"product_name":"keyboard","product_id":1,"order_id":6,"order_date":"2020-08-01"},
  {"product_name":"keyboard","product_id":1,"order_id":7,"order_date":"2020-08-01"},
  {"product_name":"mouse","product_id":2,"order_id":8,"order_date":"2020-08-03"},
  {"product_name":"screen","product_id":3,"order_id":3,"order_date":"2020-08-29"}
]

Explanation

Product 1 has two orders on its latest date, so both are returned. Product 4 has no orders and is excluded.

Example 2

Input

customers:
| customer_id | name |
|-------------|------|
| 10          | Asha |
| 11          | Ben  |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 101      | 2024-01-03  | 10          | 50         |
| 102      | 2024-01-03  | 11          | 50         |
| 103      | 2024-01-10  | 10          | 51         |
| 104      | 2024-01-12  | 11          | 51         |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 50         | cable        | 20    |
| 51         | charger      | 35    |

Output

[
  {"product_name":"cable","product_id":50,"order_id":101,"order_date":"2024-01-03"},
  {"product_name":"cable","product_id":50,"order_id":102,"order_date":"2024-01-03"},
  {"product_name":"charger","product_id":51,"order_id":104,"order_date":"2024-01-12"}
]

Explanation

For product 50, the latest date is shared by two orders, so both rows remain. Product 51 keeps only the order from 2024-01-12.

Example 3

Input

customers:
| customer_id | name  |
|-------------|-------|
| 31          | Iris  |
| 32          | Omar  |
| 33          | Petra |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 201      | 2023-03-10  | 31          | 900        |
| 202      | 2023-03-12  | 32          | 900        |
| 203      | 2023-03-11  | 33          | 901        |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 900        | monitor      | 240   |
| 901        | notebook     | 10    |
| 902        | stylus       | 19    |

Output

[
  {"product_name":"monitor","product_id":900,"order_id":202,"order_date":"2023-03-12"},
  {"product_name":"notebook","product_id":901,"order_id":203,"order_date":"2023-03-11"}
]

Explanation

Product 902 is not returned because it never appears in the orders table.

Constraints

1 <= number of rows in customers <= 200000
1 <= number of rows in products <= 200000
0 <= number of rows in orders <= 500000
customers.customer_id is unique
products.product_id is unique
orders.order_id is unique
orders.customer_id references customers.customer_id
orders.product_id references products.product_id
A customer cannot order the same product more than once on the same date
1 <= products.price <= 1000000
order_date is a valid date in YYYY-MM-DD format
Return exactly four columns: product_name, product_id, order_id, order_date
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required output schema

customers: | customer_id | name | |-------------|-----------| | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | orders: | order_id | order_date | customer_id | product_id | |----------|-------------|-------------|------------| | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 1 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | products: | product_id | product_name | price | |------------|--------------|-------| | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 |

[ {"product_name":"keyboard","product_id":1,"order_id":6,"order_date":"2020-08-01"}, {"product_name":"keyboard","product_id":1,"order_id":7,"order_date":"2020-08-01"}, {"product_name":"mouse","product_id":2,"order_id":8,"order_date":"2020-08-03"}, {"product_name":"screen","product_id":3,"order_id":3,"order_date":"2020-08-29"} ]

customers: | customer_id | name | |-------------|------| | 10 | Asha | | 11 | Ben | orders: | order_id | order_date | customer_id | product_id | |----------|-------------|-------------|------------| | 101 | 2024-01-03 | 10 | 50 | | 102 | 2024-01-03 | 11 | 50 | | 103 | 2024-01-10 | 10 | 51 | | 104 | 2024-01-12 | 11 | 51 | products: | product_id | product_name | price | |------------|--------------|-------| | 50 | cable | 20 | | 51 | charger | 35 |

[ {"product_name":"cable","product_id":50,"order_id":101,"order_date":"2024-01-03"}, {"product_name":"cable","product_id":50,"order_id":102,"order_date":"2024-01-03"}, {"product_name":"charger","product_id":51,"order_id":104,"order_date":"2024-01-12"} ]

customers: | customer_id | name | |-------------|-------| | 31 | Iris | | 32 | Omar | | 33 | Petra | orders: | order_id | order_date | customer_id | product_id | |----------|-------------|-------------|------------| | 201 | 2023-03-10 | 31 | 900 | | 202 | 2023-03-12 | 32 | 900 | | 203 | 2023-03-11 | 33 | 901 | products: | product_id | product_name | price | |------------|--------------|-------| | 900 | monitor | 240 | | 901 | notebook | 10 | | 902 | stylus | 19 |