 # Leetcode 1831. Maximum Transaction Each Day Solution

## Problem

In this problem, we are given a table `Transactions` with the following columns: `transaction_id`, `day`, and `amount`. Our task is to write an SQL query that reports the transaction IDs with the maximum amount for their respective days. If multiple transactions have the same amount on the same day, include all of them. The result should be sorted in ascending order by `transaction_id`.

### Example

Consider the following `Transactions` table:

``````1+----------------+--------------------+--------+
2| transaction_id | day                | amount |
3+----------------+--------------------+--------+
4| 8              | 2021-4-3 15:57:28  | 57     |
5| 9              | 2021-4-28 08:47:25 | 21     |
6| 1              | 2021-4-29 13:28:30 | 58     |
7| 5              | 2021-4-28 16:39:59 | 40     |
8| 6              | 2021-4-29 23:39:28 | 58     |
9+----------------+--------------------+--------+``````

The expected result table would be:

``````1+----------------+
2| transaction_id |
3+----------------+
4| 1              |
5| 5              |
6| 6              |
7| 8              |
8+----------------+``````

Here's the walk through for the example above:

• "2021-4-3" has only one transaction with ID 8, so we add 8 to the result table.
• "2021-4-28" has 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" has two transactions with IDs 1 and 6. Both transactions have the same amount of 58, so we include both in the result table.

Finally, the result table is sorted by `transaction_id`.

### Approach

To solve this problem, we can use the following approach:

1. Select the date (without time) for each transaction and group transactions by day.
2. For each day, find the transaction(s) with the maximum amount.
3. Order the result table by `transaction_id`.

## Solution in SQL

We can implement this approach using SQL:

``````1WITH daily_transactions AS (
2  SELECT transaction_id, DATE(day) AS date, amount
3  FROM Transactions
4)
5
6SELECT transaction_id
7FROM daily_transactions
8WHERE (date, amount) IN (
9  SELECT date, MAX(amount)
10  FROM daily_transactions
11  GROUP BY date
12)
13ORDER BY transaction_id;``````

### Explanation

1. We first create a Common Table Expression (CTE) `daily_transactions` where we store the `transaction_id`, date (without time), and `amount` of each transaction.
2. Next, we select `transaction_id` from the `daily_transactions` table, where the `(date, amount)` tuple is in the result of finding the maximum amount for each date.
3. Finally, we order the result table by `transaction_id`.## Solutions in Python, JavaScript and Java

Normally, SQL problems should be solved using SQL queries. However, if you need to implement it in a programming language, you can do that with the following code snippets for Python, JavaScript, and Java.

### Python

``````1from collections import defaultdict
2import datetime
3
4
5def max_transactions(transactions):
6    daily_transactions = defaultdict(list)
7
8    for transaction in transactions:
9        transaction_id, day, amount = transaction
10        date = day.date()
11        daily_transactions[date].append((transaction_id, amount))
12
13    result = []
14    for date_transactions in daily_transactions.values():
15        max_amount = max(transaction for transaction in date_transactions)
16        max_transactions = [transaction for transaction in date_transactions if transaction == max_amount]
17        result.extend(max_transactions)
18
19    result.sort()
20    return result
21
22
23transactions = [
24    (8, datetime.datetime(2021, 4, 3, 15, 57, 28), 57),
25    (9, datetime.datetime(2021, 4, 28, 8, 47, 25), 21),
26    (1, datetime.datetime(2021, 4, 29, 13, 28, 30), 58),
27    (5, datetime.datetime(2021, 4, 28, 16, 39, 59), 40),
28    (6, datetime.datetime(2021, 4, 29, 23, 39, 28), 58),
29]
30
31print(max_transactions(transactions))``````

### JavaScript

``````1function max_transactions(transactions) {
2    const daily_transactions = {};
3
4    transactions.forEach(([transaction_id, day, amount]) => {
5        const date = day.toISOString().substring(0, 10);
6        if (!daily_transactions[date]) daily_transactions[date] = [];
7        daily_transactions[date].push([transaction_id, amount]);
8    });
9
10    const result = [];
11    Object.values(daily_transactions).forEach(date_transactions => {
12        const max_amount = Math.max(...date_transactions.map(transaction => transaction));
13        const max_transactions = date_transactions.filter(transaction => transaction === max_amount).map(transaction => transaction);
14        result.push(...max_transactions);
15    });
16
17    result.sort((a, b) => a - b);
18    return result;
19}
20
21const transactions = [
22    [8, new Date('2021-04-03T15:57:28'), 57],
23    [9, new Date('2021-04-28T08:47:25'), 21],
24    [1, new Date('2021-04-29T13:28:30'), 58],
25    [5, new Date('2021-04-28T16:39:59'), 40],
26    [6, new Date('2021-04-29T23:39:28'), 58],
27];
28
29console.log(max_transactions(transactions));``````

### Java

``````1import java.time.LocalDate;
2import java.time.LocalDateTime;
3import java.util.ArrayList;
4import java.util.HashMap;
5import java.util.List;
6import java.util.Map;
7
8public class MaxTransactions {
9    public static List<Integer> max_transactions(List<Object[]> transactions) {
10        Map<LocalDate, List<Object[]>> daily_transactions = new HashMap<>();
11
12        for (Object[] transaction : transactions) {
13            Integer transaction_id = (Integer) transaction;
14            LocalDateTime day = (LocalDateTime) transaction;
15            Integer amount = (Integer) transaction;
16            LocalDate date = day.toLocalDate();
17
18            daily_transactions.putIfAbsent(date, new ArrayList<>());
20        }
21
22        List<Integer> result = new ArrayList<>();
23
24        for (List<Object[]> date_transactions : daily_transactions.values()) {
25            int max_amount = date_transactions.stream().mapToInt(transaction -> (int) transaction).max().orElse(0);
26            date_transactions.stream()
27                    .filter(transaction -> (int) transaction == max_amount)
29        }
30
31        result.sort(Integer::compareTo);
32        return result;
33    }
34
35    public static void main(String[] args) {
36        List<Object[]> transactions = List.of(
37                new Object[]{8, LocalDateTime.of(2021, 4, 3, 15, 57, 28), 57},
38                new Object[]{9, LocalDateTime.of(2021, 4, 28, 8, 47, 25), 21},
39                new Object[]{1, LocalDateTime.of(2021, 4, 29, 13, 28, 30), 58},
40                new Object[]{5, LocalDateTime.of(2021, 4, 28, 16, 39, 59), 40},
41                new Object[]{6, LocalDateTime.of(2021, 4, 29, 23, 39, 28), 58}
42        );
43
44        System.out.println(max_transactions(transactions));
45    }
46}``````

These code snippets implement the same algorithm in different programming languages. It first processes the transactions by day, then finds the maximum amount transactions for each day, and finally combines and sorts the resulting transactions.