Top Reordered Products by Customer

Medium

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: customer_id in ascending order product_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

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 2

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 3

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.

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

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 |

[ {"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"} ]

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 |

[ {"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"} ]

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 |

[ {"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"} ]