Table: transactions
+------------------+------+ | Column Name | Type | +------------------+------+ | transaction_id | int | | amount | int | | transaction_date | date | +------------------+------+ The transactions_id column uniquely identifies each row in this table. Each row of this table contains the transaction id, amount and transaction date.
Write a solution to find the sum of amounts for odd and even transactions for each day. If there are no odd or even transactions for a specific date, display as 0.
Return the result table ordered by transaction_date in ascending order.
The result format is in the following example.
Example:
Input:
transactions table:
+----------------+--------+------------------+ | transaction_id | amount | transaction_date | +----------------+--------+------------------+ | 1 | 150 | 2024-07-01 | | 2 | 200 | 2024-07-01 | | 3 | 75 | 2024-07-01 | | 4 | 300 | 2024-07-02 | | 5 | 50 | 2024-07-02 | | 6 | 120 | 2024-07-03 | +----------------+--------+------------------+
Output:
+------------------+---------+----------+ | transaction_date | odd_sum | even_sum | +------------------+---------+----------+ | 2024-07-01 | 75 | 350 | | 2024-07-02 | 0 | 350 | | 2024-07-03 | 0 | 120 | +------------------+---------+----------+
Explanation:
Note: The output table is ordered by transaction_date in ascending order.
Problem Overview: You receive a table of transactions and need to classify each transaction based on whether its identifier (or numeric attribute) is odd or even, then aggregate results accordingly. The task is essentially grouping records by parity and computing counts or totals.
Approach 1: Using HashMap for Aggregation (O(n) time, O(1) space)
Iterate through every transaction and determine parity using value % 2. Use a HashMap or dictionary where the keys represent "odd" and "even". For each record, perform a constant-time lookup and update the aggregated value such as count or total amount. This works well when processing rows in application code after fetching them from the database and demonstrates standard HashMap aggregation patterns.
The key insight is that parity checking is constant time and only two buckets are required. You simply iterate once through the dataset and update the corresponding bucket. Because the number of buckets never grows beyond two, the auxiliary memory remains constant while the runtime scales linearly with the number of transactions.
Approach 2: SQL Query Approach (O(n) time, O(1) extra space)
The database can perform the aggregation directly using a CASE WHEN expression combined with GROUP BY. For example, compute parity with CASE WHEN transaction_id % 2 = 0 THEN 'even' ELSE 'odd' END and group on that derived column. The database scans the table once, classifies each row, and aggregates counts or sums in the same pass.
This approach pushes the work to the query engine, which is typically more efficient for large datasets. SQL aggregation operations such as COUNT, SUM, and grouping logic are optimized internally. The pattern relies on basic relational operations and concepts from SQL and database query processing.
Recommended for interviews: The SQL approach is usually expected for database problems because it demonstrates that you understand grouping, conditional expressions, and aggregation directly in queries. The HashMap approach still matters when the interviewer asks how you would process rows in application code after retrieving them. Showing both indicates you understand the same aggregation concept at both the database and application layer.
This approach applies a HashMap (or dictionary) to aggregate sums for odd and even transaction IDs by date.
Iterate through the transaction data, evaluate if a transaction ID is odd or even, and then aggregate the amounts by their respective dates.
This implementation utilizes a defaultdict to store 'odd' and 'even' sums for each date. We sort the dates while preparing the result to ensure ascending order output.
Time Complexity: O(n log n) due to sorting, where n is the number of transactions.
Space Complexity: O(n) to store sums per date.
This approach uses an SQL query to group data by dates and conditionally sum based on odd/even transaction ids. This ensures efficiency via database optimization features.
This SQL query uses conditional aggregation. For each transaction_date, it calculates the sum of 'amount' for both odd and even transaction ids.
SQL
Time Complexity: O(n), where n is the number of transactions, given operations are constant-time arithmetic and grouping over indexed columns.
Space Complexity: O(n) due to required storage of transaction groupings in memory.
We can group the data by transaction_date, and then calculate the sum of transaction amounts for odd and even dates separately. Finally, sort by transaction_date in ascending order.
| Approach | Complexity |
|---|---|
| Using HashMap for Aggregation | Time Complexity: O(n log n) due to sorting, where n is the number of transactions. |
| SQL Query Approach | Time Complexity: O(n), where n is the number of transactions, given operations are constant-time arithmetic and grouping over indexed columns. |
| Grouping and Summing | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| HashMap Aggregation | O(n) | O(1) | Processing transaction records in application code after fetching rows |
| SQL CASE + GROUP BY | O(n) | O(1) extra | Preferred for database problems where aggregation should be done directly in SQL |
Leetcode MEDIUM 3220 - Odd and Even Transactions SUM CASE WHEN - Explained by Everyday Data Science • Everyday Data Science • 768 views views
Watch 8 more video solutions →Practice Odd and Even Transactions with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor