1831. Maximum Transaction Each Day


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[1] for transaction in date_transactions)
16        max_transactions = [transaction[0] for transaction in date_transactions if transaction[1] == 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[1]));
13        const max_transactions = date_transactions.filter(transaction => transaction[1] === max_amount).map(transaction => transaction[0]);
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[0];
14            LocalDateTime day = (LocalDateTime) transaction[1];
15            Integer amount = (Integer) transaction[2];
16            LocalDate date = day.toLocalDate();
17
18            daily_transactions.putIfAbsent(date, new ArrayList<>());
19            daily_transactions.get(date).add(new Object[]{transaction_id, amount});
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[1]).max().orElse(0);
26            date_transactions.stream()
27                    .filter(transaction -> (int) transaction[1] == max_amount)
28                    .forEach(transaction -> result.add((Integer) transaction[0]));
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.


Got a question? Ask the Teaching Assistant anything you don't understand.

Still not clear? Ask in the Forum,  Discord or Submit the part you don't understand to our editors.


TA 👨‍🏫