Maximum Transaction Each Day - Problem

You are given a table Transactions that contains information about financial transactions.

Table: Transactions

Column NameType
transaction_idint
daydatetime
amountint

transaction_id is the column with unique values for this table. Each row contains information about one transaction.

Write a solution to report the IDs of the transactions with the maximum amount on their respective day. If in one day there are multiple such transactions, return all of them.

Return the result table ordered by transaction_id in ascending order.

Table Schema

Transactions
Column Name Type Description
transaction_id PK int Unique identifier for each transaction
day datetime Date when the transaction occurred
amount int Transaction amount
Primary Key: transaction_id
Note: Each row represents one unique transaction with its date and amount

Input & Output

Example 1 — Multiple Days with Ties
Input Table:
transaction_id day amount
3 2021-01-01 1500
4 2021-01-01 1500
2 2021-01-02 2000
1 2021-01-02 1000
Output:
transaction_id
2
3
4
💡 Note:

On 2021-01-01, both transactions 3 and 4 have the maximum amount of 1500. On 2021-01-02, transaction 2 has the maximum amount of 2000. All three transaction IDs are returned, ordered by transaction_id.
Example 2 — Single Transaction Per Day
Input Table:
transaction_id day amount
1 2021-01-01 1000
2 2021-01-02 2000
3 2021-01-03 500
Output:
transaction_id
1
2
3
💡 Note:

Each day has only one transaction, so each transaction has the maximum amount for its respective day. All transaction IDs are returned.
Example 3 — Same Day Multiple Transactions
Input Table:
transaction_id day amount
5 2021-01-01 800
1 2021-01-01 1200
3 2021-01-01 1200
2 2021-01-01 900
Output:
transaction_id
1
3
💡 Note:

On 2021-01-01, transactions 1 and 3 both have the maximum amount of 1200. Only these two transaction IDs are returned, ordered by transaction_id.

Constraints

  • 1 ≤ transaction_id ≤ 10^5
  • day is a valid datetime
  • 1 ≤ amount ≤ 10^6

Visualization

Tap to expand
Maximum Transaction Each Day - Problem OverviewInput: All Transactionstransaction_iddayamount32021-01-01150042021-01-01150022021-01-02200012021-01-021000RANK() OVERPARTITION BY dayORDER BY amount DESCOutput: Maximum Transaction IDstransaction_id234Day 2021-01-01: Max amount 1500 → IDs 3,4Day 2021-01-02: Max amount 2000 → ID 2
Understanding the Visualization
1
Input
Transactions with dates and amounts
2
Partition & Rank
Group by day, rank by amount
3
Filter
Select rank = 1 (maximum amounts)
Key Takeaway
🎯 Key Insight: Use RANK() with PARTITION BY to handle ties elegantly when finding maximum values per group

Related Problems

Asked in
Amazon 28 Microsoft 22 Apple 18
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen