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:
- Select the date (without time) for each transaction and group transactions by day.
- For each day, find the transaction(s) with the maximum amount.
- 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
- We first create a Common Table Expression (CTE)
daily_transactions
where we store thetransaction_id
, date (without time), andamount
of each transaction. - Next, we select
transaction_id
from thedaily_transactions
table, where the(date, amount)
tuple is in the result of finding the maximum amount for each date. - 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.
Which of the following problems can be solved with backtracking (select multiple)
Which algorithm is best for finding the shortest distance between two points in an unweighted graph?
Solution Implementation
Which of the following shows the order of node visit in a Breadth-first Search?
What's the relationship between a tree and a graph?
Recommended Readings
Top Patterns to Conquer the Technical Coding Interview Should the written word bore you fear not A delightful video alternative awaits iframe width 560 height 315 src https www youtube com embed LW8Io6IPYHw title YouTube video player frameborder 0 allow accelerometer autoplay clipboard write encrypted media gyroscope picture in picture
Recursion Recursion is one of the most important concepts in computer science Simply speaking recursion is the process of a function calling itself Using a real life analogy imagine a scenario where you invite your friends to lunch https algomonster s3 us east 2 amazonaws com recursion jpg You first
Runtime Overview When learning about algorithms and data structures you'll frequently encounter the term time complexity This concept is fundamental in computer science and offers insights into how long an algorithm takes to complete given a certain input size What is Time Complexity Time complexity represents the amount of time
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.