Skip to main content
Talentd

Maximum Transaction Each Day - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| day            | datetime |
| amount         | int      |
+----------------+----------+
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.

The result format is in the following example.

 

Example 1:

Input: 
Transactions table:
+----------------+--------------------+--------+
| transaction_id | day                | amount |
+----------------+--------------------+--------+
| 8              | 2021-4-3 15:57:28  | 57     |
| 9              | 2021-4-28 08:47:25 | 21     |
| 1              | 2021-4-29 13:28:30 | 58     |
| 5              | 2021-4-28 16:39:59 | 40     |
| 6              | 2021-4-29 23:39:28 | 58     |
+----------------+--------------------+--------+
Output: 
+----------------+
| transaction_id |
+----------------+
| 1              |
| 5              |
| 6              |
| 8              |
+----------------+
Explanation: 
"2021-4-3"  --> We have one transaction with ID 8, so we add 8 to the result table.
"2021-4-28" --> We have two transactions with IDs 5 and 9. The transaction with ID 5 has an amount of 40, while the transaction with ID 9 has an amount of 21. We only include the transaction with ID 5 as it has the maximum amount this day.
"2021-4-29" --> We have two transactions with IDs 1 and 6. Both transactions have the same amount of 58, so we include both in the result table.
We order the result table by transaction_id after collecting these IDs.

 

Follow up: Could you solve it without using the MAX() function?

Approach Overview

Problem Overview: The task is to return the transaction with the highest amount for each day from a transactions table. If multiple transactions occur on the same day, only the one with the maximum amount should appear in the result.

Approach 1: GROUP BY + Join (O(n log n) time, O(n) space)

One straightforward strategy is to compute the maximum transaction amount per day using GROUP BY day. This produces a small table with day and MAX(amount). Join this result back to the original transactions table on both day and amount. The join filters rows so only transactions matching the daily maximum remain. The database engine typically sorts or hashes groups internally, leading to about O(n log n) time with O(n) intermediate storage.

This method works in almost every SQL dialect and is useful when SQL aggregation functions are preferred over analytic features. However, joins can become less readable as queries grow more complex.

Approach 2: Window Function with ROW_NUMBER() (O(n log n) time, O(n) space)

A cleaner solution uses a window function. Partition transactions by day and rank them by amount in descending order:

ROW_NUMBER() OVER (PARTITION BY day ORDER BY amount DESC)

This assigns rank 1 to the largest transaction for each day. After computing the ranking, filter rows where the rank equals 1. The database sorts rows within each partition to compute the ranking, giving O(n log n) time complexity and O(n) space for intermediate results.

Window functions are designed for exactly this type of per-group ranking problem. They avoid extra joins and keep the query compact. Most modern SQL engines including MySQL, PostgreSQL, and SQL Server support this pattern. It is a common technique in database queries and especially in window function problems.

Recommended for interviews: The window function approach using ROW_NUMBER() is the expected solution. It shows strong familiarity with analytic SQL features and keeps the query concise. The GROUP BY + join approach still demonstrates correct reasoning and works in systems without window function support, but ranking with ROW_NUMBER() is the cleaner and more scalable solution.

Solution

We can use the window function RANK(), which assigns a rank to each transaction based on its amount in descending order, and then select the transactions with a rank of 1.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
GROUP BY + JoinO(n log n)O(n)When window functions are unavailable or when using basic SQL aggregation
Window Function (ROW_NUMBER)O(n log n)O(n)Best choice for ranking rows per group in modern SQL databases

Video Solution

LeetCode Medium 1831 Interview SQL Question with Detailed Explanation • Everyday Data Science • 4,159 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Maximum Transaction Each Day easy or hard?
Maximum Transaction Each Day is considered a Medium-level SQL problem. The main challenge is recognizing that window functions like ROW_NUMBER() can rank transactions within each day and make it easy to select the maximum record.
Maximum Transaction Each Day Python/Java solution
This is primarily a SQL database problem rather than a Python or Java algorithm problem. The solution is implemented directly in SQL using analytic functions like ROW_NUMBER() with PARTITION BY and ORDER BY clauses.
How to solve Maximum Transaction Each Day in O(n)?
Pure O(n) time is uncommon because SQL engines typically sort rows to compute rankings or aggregations. The closest practical approach uses ROW_NUMBER() OVER(PARTITION BY day ORDER BY amount DESC), which performs partitioned sorting and runs in roughly O(n log n).
What is the best approach for Maximum Transaction Each Day?
The most efficient and clean solution uses a SQL window function. Apply ROW_NUMBER() with PARTITION BY day and ORDER BY amount DESC, then filter rows where the rank equals 1. This directly selects the highest transaction for each day and avoids extra joins.
Is Maximum Transaction Each Day asked at Google/Amazon/Meta?
Problems involving window functions and per-group ranking frequently appear in SQL interview rounds at companies like Amazon, Meta, and Google. Variations include finding top values per group, ranking rows, or identifying maximum records per category.
What data structure is used in Maximum Transaction Each Day?
This problem relies on database query techniques rather than traditional data structures. The key concept is SQL window functions such as ROW_NUMBER(), which allow ranking rows within partitions defined by a column like day.
What is the time complexity of Maximum Transaction Each Day?
Most SQL solutions run in O(n log n) time because the database must sort rows within each partition or group by day. Window functions and GROUP BY queries both require sorting or hashing operations internally.

Ready to solve this problem?

Practice Maximum Transaction Each Day with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary