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 INPUT Transactions Table txn_id day amount max? 1 2021-01 100 OK 2 2021-01 100 OK 3 2021-01 50 - 4 2021-02 200 OK 5 2021-02 150 - Day 2021-01 IDs: 1, 2, 3 Max: 100 Day 2021-02 IDs: 4, 5 Max: 200 Group transactions by day Find max amount per day ALGORITHM STEPS 1 Find Max Per Day GROUP BY day, get MAX(amount) SELECT day, MAX(amount) as max_amt FROM Transactions GROUP BY day 2 Join Back Match original with max amounts FROM Transactions t JOIN max_per_day m ON t.day=m.day AND t.amount=m.max 3 Filter Max Transactions Keep rows where amount = max 4 Order Results ORDER BY transaction_id ASC Window Function Alternative: RANK() OVER(PARTITION BY day) FINAL RESULT Output: transaction_id transaction_id 1 2 4 Day 2021-01-01 Max amount: 100 IDs 1, 2 both have 100 (tie) Day 2021-02-01 Max amount: 200 Only ID 4 has 200 Sorted ASC: [1, 2, 4] Key Insight: Use a subquery or CTE to find MAX(amount) per day, then JOIN back to get all transaction_ids matching that maximum. This handles ties automatically (multiple transactions with same max). Alternative: Use RANK() window function to rank amounts within each day, then filter rank=1. TutorialsPoint - Maximum Transaction Each Day | Optimal Solution

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