FastPrepMost Recent Order for Each Customer
Problems/Most Recent Order for Each Customer

Most Recent Order for Each Customer

Capgemini logoCapgemini● MediumNEW GRADONSITE INTERVIEW
QuestionConstraints

Problem statement

Source note: In an on-campus technical interview, the candidate was asked to write an SQL query with joins that returns each customer's most recent order.

The report gives no tables, columns, tie rule, or row order. Here, a customers table and an orders table are joined, ties on the latest date go to the larger order_id, customers with no orders are left out, and rows are sorted by customer_id.

The judged task matches the report at about 85%.

You are given two tables. customers has one row per customer. orders has one row per order, with the customer who placed it, the date it was placed, and its amount.

For each customer who has placed at least one order, return their most recent order: the order with the latest order_date. Return customer_id, customer_name, order_id, order_date, and amount, sorted by customer_id from smallest to largest.

  • If a customer has several orders on their latest date, return the one with the largest order_id.
  • All five values in a row must come from the same order.
  • Customers with no orders are left out.
  • Two customers can have the same name. They are still different customers.

Table schema

MySQLPostgreSQLPandas

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

customers

One row per customer.

ColumnTypeNullableDescription
customer_idPKIntegerNo—
customer_nameTextNo—

orders

One row per order, with the customer who placed it, its date, and its amount.

ColumnTypeNullableDescription
order_idPKIntegerNo—
customer_idIntegerNo—
order_dateDateNo—
amountIntegerNo—

Foreign key: customer_id → customers(customer_id)

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
customer_idIntegerNo—
customer_nameTextNo—
order_idIntegerNo—
order_dateDateNo—
amountIntegerNo—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

  • customers has at most 300 rows, and orders has at most 1000 rows.
  • customer_id and order_id are unique integers between 1 and 10^9.
  • customer_name is non-empty text of at most 50 characters.
  • Every orders.customer_id matches a row in customers.
  • order_date is a date between 2000-01-01 and 2099-12-31.
  • amount is an integer between 1 and 10^6.

More Capgemini problems

  • Contains DuplicatePHONE SCREEN · Seen Sep 2026
  • Detect a Linked-List CycleONSITE INTERVIEW · Seen Sep 2026
  • Longest Substring Without Repeating CharactersPHONE SCREEN · Seen Sep 2026
All Capgemini interview questionsSee Capgemini hiring insights