00:00:00

Description

Editorial

Latest Three Orders Per Customer

MEDIUM20 pts

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:
1. customer_name in ascending order
2. customer_id in ascending order
3. order_date in descending 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 | 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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":1,"order_date":"2020-07-31","customer_id":1,"cost":30},{"order_id":2,"order_date":"2020-07-30","customer_id":2,"cost":40},{"order_id":3,"order_date":"2020-07-31","customer_id":3,"cost":70},{"order_id":4,"order_date":"2020-07-29","customer_id":4,"cost":100},{"order_id":5,"order_date":"2020-06-10","customer_id":1,"cost":1010},{"order_id":6,"order_date":"2020-08-01","customer_id":2,"cost":102},{"order_id":7,"order_date":"2020-08-01","customer_id":3,"cost":111},{"order_id":8,"order_date":"2020-08-03","customer_id":1,"cost":99},{"order_id":9,"order_date":"2020-08-07","customer_id":2,"cost":32},{"order_id":10,"order_date":"2020-07-15","customer_id":1,"cost":2}]

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 Three Orders Per Customer

MEDIUM20 pts

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:
1. customer_name in ascending order
2. customer_id in ascending order
3. order_date in descending 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 | 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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

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 Three Orders Per Customer

Hints

Latest Three Orders Per Customer

Hints