Latest Three Orders Per Customer

Medium

An e-commerce analytics platform tracks user profiles and purchase events. Table: customers customer_id: unique identifier for each customer name: display name of the customer Table: orders order_id: unique identifier for each order order_date: calendar date when the order was placed customer_id: reference to the customer who placed the order cost: order value for that row Business rule: Each customer can place at most one order per day. Task: For every customer who has placed at least one order, return their three most recent orders. If a customer has fewer than three orders, return all available orders for that customer. Output requirements: Return columns in this exact order: customer_name customer_id order_id order_date Sort by: customer_name in ascending order customer_id in ascending order order_date in descending 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 | cost |
|----------|-------------|-------------|------|
| 1        | 2020-07-31  | 1           | 30   |
| 2        | 2020-07-30  | 2           | 40   |
| 3        | 2020-07-31  | 3           | 70   |
| 4        | 2020-07-29  | 4           | 100  |
| 5        | 2020-06-10  | 1           | 1010 |
| 6        | 2020-08-01  | 2           | 102  |
| 7        | 2020-08-01  | 3           | 111  |
| 8        | 2020-08-03  | 1           | 99   |
| 9        | 2020-08-07  | 2           | 32   |
| 10       | 2020-07-15  | 1           | 2    |

Output

[
  {"customer_name":"Annabelle","customer_id":3,"order_id":7,"order_date":"2020-08-01"},
  {"customer_name":"Annabelle","customer_id":3,"order_id":3,"order_date":"2020-07-31"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":9,"order_date":"2020-08-07"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":6,"order_date":"2020-08-01"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":2,"order_date":"2020-07-30"},
  {"customer_name":"Marwan","customer_id":4,"order_id":4,"order_date":"2020-07-29"},
  {"customer_name":"Winston","customer_id":1,"order_id":8,"order_date":"2020-08-03"},
  {"customer_name":"Winston","customer_id":1,"order_id":1,"order_date":"2020-07-31"},
  {"customer_name":"Winston","customer_id":1,"order_id":10,"order_date":"2020-07-15"}
]

Explanation

Customer 1 has four orders, so only the three most recent dates are retained. Customers with fewer than three orders contribute all of their rows.

Example 2

Input

customers:
| customer_id | name  |
|-------------|-------|
| 10          | Asha  |
| 11          | Asha  |
| 12          | Bruno |

orders:
| order_id | order_date  | customer_id | cost |
|----------|-------------|-------------|------|
| 101      | 2024-01-03  | 10          | 80   |
| 102      | 2024-01-08  | 10          | 81   |
| 103      | 2024-01-12  | 11          | 90   |
| 104      | 2024-01-01  | 12          | 70   |

Output

[
  {"customer_name":"Asha","customer_id":10,"order_id":102,"order_date":"2024-01-08"},
  {"customer_name":"Asha","customer_id":10,"order_id":101,"order_date":"2024-01-03"},
  {"customer_name":"Asha","customer_id":11,"order_id":103,"order_date":"2024-01-12"},
  {"customer_name":"Bruno","customer_id":12,"order_id":104,"order_date":"2024-01-01"}
]

Explanation

When names are equal, customer_id is the tie-break key. Each customer is still processed independently for their top three recent orders.

Example 3

Input

customers:
| customer_id | name   |
|-------------|--------|
| 21          | Irene  |
| 22          | Jasper |

orders:
| order_id | order_date  | customer_id | cost |
|----------|-------------|-------------|------|
| 201      | 2024-05-01  | 21          | 10   |
| 202      | 2024-05-02  | 21          | 12   |
| 203      | 2024-05-03  | 21          | 14   |
| 204      | 2024-05-04  | 21          | 16   |

Output

[
  {"customer_name":"Irene","customer_id":21,"order_id":204,"order_date":"2024-05-04"},
  {"customer_name":"Irene","customer_id":21,"order_id":203,"order_date":"2024-05-03"},
  {"customer_name":"Irene","customer_id":21,"order_id":202,"order_date":"2024-05-02"}
]

Explanation

Customers without orders are absent from the result. For customer 21, the oldest order is dropped to keep only the latest three.

Constraints

1 <= number of rows in customers <= 300000
0 <= number of rows in orders <= 600000
customer_id is unique in customers
order_id is unique in orders
orders.customer_id references customers.customer_id
Each customer has at most one order per calendar date
1 <= cost <= 1000000
order_date is a valid date in YYYY-MM-DD format
Return exactly four columns: customer_name, customer_id, order_id, order_date
Sort by customer_name ASC, customer_id ASC, order_date DESC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

Loading problem...

Preparing your editor. You can read the problem while it loads.

Latest Three Orders Per Customer

Medium

An e-commerce analytics platform tracks user profiles and purchase events. Table: customers customer_id: unique identifier for each customer name: display name of the customer Table: orders order_id: unique identifier for each order order_date: calendar date when the order was placed customer_id: reference to the customer who placed the order cost: order value for that row Business rule: Each customer can place at most one order per day. Task: For every customer who has placed at least one order, return their three most recent orders. If a customer has fewer than three orders, return all available orders for that customer. Output requirements: Return columns in this exact order: customer_name customer_id order_id order_date Sort by: customer_name in ascending order customer_id in ascending order order_date in descending 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 | cost |
|----------|-------------|-------------|------|
| 1        | 2020-07-31  | 1           | 30   |
| 2        | 2020-07-30  | 2           | 40   |
| 3        | 2020-07-31  | 3           | 70   |
| 4        | 2020-07-29  | 4           | 100  |
| 5        | 2020-06-10  | 1           | 1010 |
| 6        | 2020-08-01  | 2           | 102  |
| 7        | 2020-08-01  | 3           | 111  |
| 8        | 2020-08-03  | 1           | 99   |
| 9        | 2020-08-07  | 2           | 32   |
| 10       | 2020-07-15  | 1           | 2    |

Output

[
  {"customer_name":"Annabelle","customer_id":3,"order_id":7,"order_date":"2020-08-01"},
  {"customer_name":"Annabelle","customer_id":3,"order_id":3,"order_date":"2020-07-31"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":9,"order_date":"2020-08-07"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":6,"order_date":"2020-08-01"},
  {"customer_name":"Jonathan","customer_id":2,"order_id":2,"order_date":"2020-07-30"},
  {"customer_name":"Marwan","customer_id":4,"order_id":4,"order_date":"2020-07-29"},
  {"customer_name":"Winston","customer_id":1,"order_id":8,"order_date":"2020-08-03"},
  {"customer_name":"Winston","customer_id":1,"order_id":1,"order_date":"2020-07-31"},
  {"customer_name":"Winston","customer_id":1,"order_id":10,"order_date":"2020-07-15"}
]

Explanation

Customer 1 has four orders, so only the three most recent dates are retained. Customers with fewer than three orders contribute all of their rows.

Example 2

Input

customers:
| customer_id | name  |
|-------------|-------|
| 10          | Asha  |
| 11          | Asha  |
| 12          | Bruno |

orders:
| order_id | order_date  | customer_id | cost |
|----------|-------------|-------------|------|
| 101      | 2024-01-03  | 10          | 80   |
| 102      | 2024-01-08  | 10          | 81   |
| 103      | 2024-01-12  | 11          | 90   |
| 104      | 2024-01-01  | 12          | 70   |

Output

[
  {"customer_name":"Asha","customer_id":10,"order_id":102,"order_date":"2024-01-08"},
  {"customer_name":"Asha","customer_id":10,"order_id":101,"order_date":"2024-01-03"},
  {"customer_name":"Asha","customer_id":11,"order_id":103,"order_date":"2024-01-12"},
  {"customer_name":"Bruno","customer_id":12,"order_id":104,"order_date":"2024-01-01"}
]

Explanation

When names are equal, customer_id is the tie-break key. Each customer is still processed independently for their top three recent orders.

Example 3

Input

customers:
| customer_id | name   |
|-------------|--------|
| 21          | Irene  |
| 22          | Jasper |

orders:
| order_id | order_date  | customer_id | cost |
|----------|-------------|-------------|------|
| 201      | 2024-05-01  | 21          | 10   |
| 202      | 2024-05-02  | 21          | 12   |
| 203      | 2024-05-03  | 21          | 14   |
| 204      | 2024-05-04  | 21          | 16   |

Output

[
  {"customer_name":"Irene","customer_id":21,"order_id":204,"order_date":"2024-05-04"},
  {"customer_name":"Irene","customer_id":21,"order_id":203,"order_date":"2024-05-03"},
  {"customer_name":"Irene","customer_id":21,"order_id":202,"order_date":"2024-05-02"}
]

Explanation

Customers without orders are absent from the result. For customer 21, the oldest order is dropped to keep only the latest three.

Constraints

1 <= number of rows in customers <= 300000
0 <= number of rows in orders <= 600000
customer_id is unique in customers
order_id is unique in orders
orders.customer_id references customers.customer_id
Each customer has at most one order per calendar date
1 <= cost <= 1000000
order_date is a valid date in YYYY-MM-DD format
Return exactly four columns: customer_name, customer_id, order_id, order_date
Sort by customer_name ASC, customer_id ASC, order_date DESC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns