FastPrepOrder Totals for Every Customer
Problems/Order Totals for Every Customer

Order Totals for Every Customer

Amazon logoAmazon● EasyINTERNONSITE INTERVIEW

Updated Nov 2026

QuestionConstraints

Problem statement

Source note: In the same live Amazon intern SQL round, the candidate had to write a join query and defend whether it should be a LEFT JOIN or an INNER JOIN. The interviewer cared most about the reasoning, and the candidate used CTEs.

The report does not give the tables or the exact question. Here, you list every customer with their order count and order total, and customers with no orders still show up with zeros. The "why this join" part is asked in the statement and covered in the editorial, but it is not checked.

Our version is about 60% the same as the reported question.

The customers table has one row per customer. The orders table has one row per order, and each order belongs to one customer through customer_id.

Write a report with one row for every customer, including customers who have never placed an order. For each customer, return:

  • customer_id and customer_name
  • order_count: how many orders the customer placed
  • total_amount: the sum of the amount of those orders

A customer with no orders has 0 in both order_count and total_amount. Sort the rows by customer_id from smallest to largest.

Interview follow-up

Be ready to explain your join choice. Why does this report need a LEFT JOIN, and when would an INNER JOIN be the better choice?

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.

ColumnTypeNullableDescription
order_idPKIntegerNo—
customer_idIntegerNo—
amountIntegerNo—

Foreign key: customer_id → customers(customer_id)

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
customer_idIntegerNo—
customer_nameTextNo—
order_countIntegerNo—
total_amountIntegerNo—

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

Constraints

  • Each table has from 0 to 10^5 rows.
  • customer_id is unique in customers, and order_id is unique in orders.
  • Every customer_id in orders also appears in customers.
  • No column is NULL.
  • 1 <= customer_id, order_id <= 10^9 and 1 <= amount <= 10^6.
  • customer_name has 1 to 50 characters. Two customers can have the same name.

More Amazon Onsite Interview questions

  • Number of IslandsPHONE SCREEN · ONSITE INTERVIEW · Seen Nov 2026
  • Cheapest Flights Within K StopsPHONE SCREEN · ONSITE INTERVIEW · Seen Nov 2026
  • Course SchedulePHONE SCREEN · ONSITE INTERVIEW · Seen Nov 2026
  • Course Schedule IIPHONE SCREEN · ONSITE INTERVIEW · Seen Nov 2026
  • Dungeon GameONSITE INTERVIEW · Seen Nov 2026
  • Integer BreakONSITE INTERVIEW · Seen Nov 2026
  • LFU CacheONSITE INTERVIEW · Seen Nov 2026
  • LRU Cache for Query ResultsPHONE SCREEN · ONSITE INTERVIEW · Seen Nov 2026
  • Count Adjacent Symbol Pairs for BPEONSITE INTERVIEW · Seen Nov 2026
  • First Non-Repeating CharacterOA · ONSITE INTERVIEW · Seen Nov 2026

Similar questions at other companies

  • Delete Duplicate Employee RecordsInfosys · ONSITE INTERVIEWInfosys interview questions
  • Records Created or Updated in a Time WindowPelatro · ONSITE INTERVIEW
  • Top Ten Employee SalariesSolytics Partners · ONSITE INTERVIEW
  • Latest New JobSphere · ONSITE INTERVIEW
  • Employees Whose Manager Left the CompanyZS · ONSITE INTERVIEWZS interview questions
  • Employees Above Average SalaryJP Morgan Chase · ONSITE INTERVIEWJP Morgan Chase interview questions
  • Retrieve Every Student RecordLSEG · ONSITE INTERVIEWLSEG interview questions
  • Conditionally Insert or Update RecordsKlaviyo · ONSITE INTERVIEWKlaviyo interview questions
  • Employees with Matching DepartmentsAMD · ONSITE INTERVIEWAMD interview questions
  • New-User Devices That Opened an AppMicrosoft · ONSITE INTERVIEWMicrosoft interview questions

Interview reports that mention this question

  • Amazon BIE Intern SQL and Interview Questions: 17 Experiences

Amazon interview experiences

  • Amazon SWE OA with AI Coding: 1 Experience
  • Amazon SWE Intern Phone Screen Interview Experience: 1 Experience
  • Amazon New Grad SDE I OA and Virtual Onsite: 1 Experience
  • Amazon Experienced SWE AI-Assisted Debugging OA: 1 Experience
  • Amazon SDE II (L5) Interview Questions: 5 Experiences
See Amazon hiring insights