00:00:00

Description

Editorial

Latest Order Day Per Product

MEDIUM20 pts

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:
1. product_name
2. product_id
3. order_id
4. order_date
Sort the final result by:
1. product_name in ascending order
2. product_id in ascending order
3. order_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":1,"order_date":"2020-07-31","customer_id":1,"product_id":1},{"order_id":2,"order_date":"2020-07-30","customer_id":2,"product_id":2},{"order_id":3,"order_date":"2020-08-29","customer_id":3,"product_id":3},{"order_id":4,"order_date":"2020-07-29","customer_id":4,"product_id":1},{"order_id":5,"order_date":"2020-06-10","customer_id":1,"product_id":2},{"order_id":6,"order_date":"2020-08-01","customer_id":2,"product_id":1},{"order_id":7,"order_date":"2020-08-01","customer_id":3,"product_id":1},{"order_id":8,"order_date":"2020-08-03","customer_id":1,"product_id":2},{"order_id":9,"order_date":"2020-08-07","customer_id":2,"product_id":3},{"order_id":10,"order_date":"2020-07-15","customer_id":1,"product_id":2}]

products =

[{"product_id":1,"product_name":"keyboard","price":120},{"product_id":2,"product_name":"mouse","price":80},{"product_id":3,"product_name":"screen","price":600},{"product_id":4,"product_name":"hard disk","price":450}]

customers =

[{"customer_id":1,"name":"Winston"},{"customer_id":2,"name":"Jonathan"},{"customer_id":3,"name":"Annabelle"},{"customer_id":4,"name":"Marwan"},{"customer_id":5,"name":"Khaled"}]

Loading problem...

101

00:00:00

Description

Editorial

Latest Order Day Per Product

MEDIUM20 pts

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:
1. product_name
2. product_id
3. order_id
4. order_date
Sort the final result by:
1. product_name in ascending order
2. product_id in ascending order
3. order_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

products =

customers =

[{"customer_id":1,"name":"Winston"},{"customer_id":2,"name":"Jonathan"},{"customer_id":3,"name":"Annabelle"},{"customer_id":4,"name":"Marwan"},{"customer_id":5,"name":"Khaled"}]

Latest Order Day Per Product

Hints

Latest Order Day Per Product

Hints