00:00:00

Description

Editorial

Top Reordered Products by Customer

MEDIUM20 pts

A commerce analytics team wants a repeat-purchase signal at the customer level.

Table: customers

customer_id: unique identifier of a customer
name: display name

Table: orders

order_id: unique identifier of an order event
order_date: calendar date of the order
customer_id: customer who placed the order
product_id: product included in the order row

Table: products

product_id: unique identifier of a product
product_name: display name of the product
price: unit price metadata

Business rules:

A customer can place many orders across different dates.
For a given customer and date, the same product does not appear more than once.

Task: For every customer who has ordered at least once, return the product or products they ordered most frequently. If multiple products share the highest count for that customer, include all of them.

Output requirements:

Return columns in this exact order:
- customer_id
- product_id
- product_name
Sort by:
1. customer_id in ascending order
2. product_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

customers:
| customer_id | name  |
|-------------|-------|
| 1           | Alice |
| 2           | Bob   |
| 3           | Tom   |
| 4           | Jerry |
| 5           | John  |

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           | 3          |
| 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          | Monitor      | 600   |
| 4          | SSD          | 450   |

Output

[
  {"customer_id":1,"product_id":2,"product_name":"Mouse"},
  {"customer_id":2,"product_id":1,"product_name":"Keyboard"},
  {"customer_id":2,"product_id":2,"product_name":"Mouse"},
  {"customer_id":2,"product_id":3,"product_name":"Monitor"},
  {"customer_id":3,"product_id":3,"product_name":"Monitor"},
  {"customer_id":4,"product_id":1,"product_name":"Keyboard"}
]

Explanation

Customer 1 reordered product 2 most often, customer 2 has a three-way tie, customer 3 has a single winner, customer 4 has one order only, and customer 5 is excluded due to zero orders.

Example

Input

customers:
| customer_id | name     |
|-------------|----------|
| 10          | Nora     |
| 11          | Rahul    |
| 12          | Maya     |
| 13          | NoOrders |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 1001     | 2024-01-01  | 10          | 101        |
| 1002     | 2024-01-05  | 10          | 102        |
| 1003     | 2024-01-12  | 10          | 101        |
| 1004     | 2024-01-16  | 10          | 102        |
| 1005     | 2024-01-03  | 11          | 103        |
| 1006     | 2024-01-08  | 11          | 103        |
| 1007     | 2024-01-09  | 11          | 104        |
| 1008     | 2024-01-10  | 12          | 104        |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 101        | Notebook     | 15    |
| 102        | Pen          | 3     |
| 103        | Bag          | 40    |
| 104        | Bottle       | 12    |

Output

[
  {"customer_id":10,"product_id":101,"product_name":"Notebook"},
  {"customer_id":10,"product_id":102,"product_name":"Pen"},
  {"customer_id":11,"product_id":103,"product_name":"Bag"},
  {"customer_id":12,"product_id":104,"product_name":"Bottle"}
]

Explanation

Customer 10 has a tie between products 101 and 102. Customer 11's winner is product 103. Customer 12 ordered only one product. Customer 13 has no orders and is omitted.

Example

Input

customers:
| customer_id | name |
|-------------|------|
| 21          | Ari  |
| 22          | Bee  |
| 23          | Cia  |
| 24          | Deo  |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 2001     | 2023-05-01  | 21          | 201        |
| 2002     | 2023-05-02  | 21          | 201        |
| 2003     | 2023-05-03  | 21          | 201        |
| 2004     | 2023-05-04  | 22          | 202        |
| 2005     | 2023-05-05  | 22          | 203        |
| 2006     | 2023-05-06  | 22          | 202        |
| 2007     | 2023-05-07  | 22          | 203        |
| 2008     | 2023-05-08  | 23          | 203        |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 201        | USB-C Cable  | 9     |
| 202        | Webcam       | 99    |
| 203        | Desk Lamp    | 45    |

Output

[
  {"customer_id":21,"product_id":201,"product_name":"USB-C Cable"},
  {"customer_id":22,"product_id":202,"product_name":"Webcam"},
  {"customer_id":22,"product_id":203,"product_name":"Desk Lamp"},
  {"customer_id":23,"product_id":203,"product_name":"Desk Lamp"}
]

Explanation

Customer 21 has a single clear winner. Customer 22 has a tie between two products. Customer 23 has only one product. Customer 24 has no activity.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in customers <= 300000
1 <= number of rows in products <= 300000
0 <= number of rows in orders <= 600000
customer_id is unique in customers
product_id is unique in products
order_id is unique in orders
orders.customer_id references customers.customer_id
orders.product_id references products.product_id
(customer_id, product_id, order_date) is unique in orders
1 <= price <= 1000000
order_date is a valid date in YYYY-MM-DD format
Return exactly three columns: customer_id, product_id, product_name
Sort by customer_id ASC, product_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

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":3},{"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":"Monitor","price":600},{"product_id":4,"product_name":"SSD","price":450}]

customers =

[{"customer_id":1,"name":"Alice"},{"customer_id":2,"name":"Bob"},{"customer_id":3,"name":"Tom"},{"customer_id":4,"name":"Jerry"},{"customer_id":5,"name":"John"}]

Loading problem...

101

00:00:00

Description

Editorial

Top Reordered Products by Customer

MEDIUM20 pts

A commerce analytics team wants a repeat-purchase signal at the customer level.

Table: customers

customer_id: unique identifier of a customer
name: display name

Table: orders

order_id: unique identifier of an order event
order_date: calendar date of the order
customer_id: customer who placed the order
product_id: product included in the order row

Table: products

product_id: unique identifier of a product
product_name: display name of the product
price: unit price metadata

Business rules:

A customer can place many orders across different dates.
For a given customer and date, the same product does not appear more than once.

Output requirements:

Return columns in this exact order:
- customer_id
- product_id
- product_name
Sort by:
1. customer_id in ascending order
2. product_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

customers:
| customer_id | name  |
|-------------|-------|
| 1           | Alice |
| 2           | Bob   |
| 3           | Tom   |
| 4           | Jerry |
| 5           | John  |

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           | 3          |
| 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          | Monitor      | 600   |
| 4          | SSD          | 450   |

Output

[
  {"customer_id":1,"product_id":2,"product_name":"Mouse"},
  {"customer_id":2,"product_id":1,"product_name":"Keyboard"},
  {"customer_id":2,"product_id":2,"product_name":"Mouse"},
  {"customer_id":2,"product_id":3,"product_name":"Monitor"},
  {"customer_id":3,"product_id":3,"product_name":"Monitor"},
  {"customer_id":4,"product_id":1,"product_name":"Keyboard"}
]

Explanation

Customer 1 reordered product 2 most often, customer 2 has a three-way tie, customer 3 has a single winner, customer 4 has one order only, and customer 5 is excluded due to zero orders.

Example

Input

customers:
| customer_id | name     |
|-------------|----------|
| 10          | Nora     |
| 11          | Rahul    |
| 12          | Maya     |
| 13          | NoOrders |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 1001     | 2024-01-01  | 10          | 101        |
| 1002     | 2024-01-05  | 10          | 102        |
| 1003     | 2024-01-12  | 10          | 101        |
| 1004     | 2024-01-16  | 10          | 102        |
| 1005     | 2024-01-03  | 11          | 103        |
| 1006     | 2024-01-08  | 11          | 103        |
| 1007     | 2024-01-09  | 11          | 104        |
| 1008     | 2024-01-10  | 12          | 104        |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 101        | Notebook     | 15    |
| 102        | Pen          | 3     |
| 103        | Bag          | 40    |
| 104        | Bottle       | 12    |

Output

[
  {"customer_id":10,"product_id":101,"product_name":"Notebook"},
  {"customer_id":10,"product_id":102,"product_name":"Pen"},
  {"customer_id":11,"product_id":103,"product_name":"Bag"},
  {"customer_id":12,"product_id":104,"product_name":"Bottle"}
]

Explanation

Customer 10 has a tie between products 101 and 102. Customer 11's winner is product 103. Customer 12 ordered only one product. Customer 13 has no orders and is omitted.

Example

Input

customers:
| customer_id | name |
|-------------|------|
| 21          | Ari  |
| 22          | Bee  |
| 23          | Cia  |
| 24          | Deo  |

orders:
| order_id | order_date  | customer_id | product_id |
|----------|-------------|-------------|------------|
| 2001     | 2023-05-01  | 21          | 201        |
| 2002     | 2023-05-02  | 21          | 201        |
| 2003     | 2023-05-03  | 21          | 201        |
| 2004     | 2023-05-04  | 22          | 202        |
| 2005     | 2023-05-05  | 22          | 203        |
| 2006     | 2023-05-06  | 22          | 202        |
| 2007     | 2023-05-07  | 22          | 203        |
| 2008     | 2023-05-08  | 23          | 203        |

products:
| product_id | product_name | price |
|------------|--------------|-------|
| 201        | USB-C Cable  | 9     |
| 202        | Webcam       | 99    |
| 203        | Desk Lamp    | 45    |

Output

[
  {"customer_id":21,"product_id":201,"product_name":"USB-C Cable"},
  {"customer_id":22,"product_id":202,"product_name":"Webcam"},
  {"customer_id":22,"product_id":203,"product_name":"Desk Lamp"},
  {"customer_id":23,"product_id":203,"product_name":"Desk Lamp"}
]

Explanation

Customer 21 has a single clear winner. Customer 22 has a tie between two products. Customer 23 has only one product. Customer 24 has no activity.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in customers <= 300000
1 <= number of rows in products <= 300000
0 <= number of rows in orders <= 600000
customer_id is unique in customers
product_id is unique in products
order_id is unique in orders
orders.customer_id references customers.customer_id
orders.product_id references products.product_id
(customer_id, product_id, order_date) is unique in orders
1 <= price <= 1000000
order_date is a valid date in YYYY-MM-DD format
Return exactly three columns: customer_id, product_id, product_name
Sort by customer_id ASC, product_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

products =

customers =

[{"customer_id":1,"name":"Alice"},{"customer_id":2,"name":"Bob"},{"customer_id":3,"name":"Tom"},{"customer_id":4,"name":"Jerry"},{"customer_id":5,"name":"John"}]

Top Reordered Products by Customer

Hints

Top Reordered Products by Customer

Hints