FastPrepFirst Order per Merchant per Date
Problems/First Order per Merchant per Date

First Order per Merchant per Date

Amazon logoAmazon● MediumINTERNONSITE INTERVIEW

Updated Nov 2026

QuestionConstraints

Problem statement

Source note: In an Amazon intern SQL round built around window functions, the candidate was asked to pull, for every merchant, the first order placed on each date.

The report does not give the table, what first means when two orders share a time, or the output columns. Here, first means the earliest order_time, a tie goes to the smaller order_id, and you return merchant_id, order_date and order_id sorted by merchant and then date.

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

The orders table has one row per order: a unique order_id, the merchant_id of the merchant that received it, and the order_time when it was placed.

For every merchant and every calendar date on which that merchant has at least one order, return the merchant's first order of that date. The first order is the one with the earliest order_time. If several orders share the earliest time, pick the one with the smallest order_id.

Return the columns merchant_id, order_date (the date part of order_time), and order_id. Sort the rows by merchant_id and then by order_date, both from smallest to largest.

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.

orders

One row per order.

ColumnTypeNullableDescription
order_idPKIntegerNo—
merchant_idIntegerNo—
order_timeTimestampNo—

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
merchant_idIntegerNo—
order_dateDateNo—
order_idIntegerNo—

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

Constraints

  • The orders table has from 0 to 10^5 rows.
  • order_id is unique, and 1 <= order_id, merchant_id <= 10^9.
  • No column is NULL.
  • Every order_time is between 2000-01-01 00:00:00 and 2030-12-31 23:59:59, to the second.

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

  • Daily USD Totals with Effective-Dated Exchange RatesMicrosoft · ONSITE INTERVIEWMicrosoft interview questions
  • Most Recent Order for Each CustomerCapgemini · ONSITE INTERVIEWCapgemini interview questions
  • Tenth-Highest Distinct SalaryInfosys · ONSITE INTERVIEWInfosys interview questions
  • Fifth Highest Salary In A DepartmentZS · ONSITE INTERVIEWZS interview questions
  • In-Memory Database QueriesOpenAI · PHONE SCREEN · ONSITE INTERVIEWOpenAI interview questions
  • Nth-Highest Distinct Salary Without Ranking FunctionsMastercard · ONSITE INTERVIEWMastercard interview questions
  • Contest Participants with Long Totals and Short QuestionsThe Walt Disney Company · ONSITE INTERVIEWThe Walt Disney Company interview questions
  • Block Transaction Count TrendsChainalysis · ONSITE INTERVIEWChainalysis interview questions
  • Third-Highest Distinct SalaryExpedia · ONSITE INTERVIEWExpedia interview questions
  • Highest-Paid Employees in Every DepartmentMorgan Stanley · ONSITE INTERVIEWMorgan Stanley 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