Problem

Table: transactions

1
2
3
4
5
6
7
8
9

+------------------+------+
| 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 inascending order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

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:
* For transaction dates:
* 2024-07-01:
* Sum of amounts for odd transactions: 75
* Sum of amounts for even transactions: 150 + 200 = 350
* 2024-07-02:
* Sum of amounts for odd transactions: 0
* Sum of amounts for even transactions: 300 + 50 = 350
* 2024-07-03:
* Sum of amounts for odd transactions: 0
* Sum of amounts for even transactions: 120
**Note:** The output table is ordered by `transaction_date` in ascending
order.

Solution

Method 1 – Conditional Aggregation

Intuition

We need to sum the amounts for odd and even transactions for each day. We can use conditional aggregation to sum amounts where the amount is odd or even, and group by date.

Approach

  1. Group transactions by transaction_date.
  2. For each group, sum amounts where amount is odd and where amount is even.
  3. Use COALESCE or CASE to ensure 0 is returned if there are no odd/even transactions for a date.
  4. Order the result by transaction_date ascending.

Code

1
2
3
4
5
6
7

SELECT
  transaction_date,
  COALESCE(SUM(CASE WHEN amount % 2 = 1 THEN amount END), 0) AS odd_sum,
  COALESCE(SUM(CASE WHEN amount % 2 = 0 THEN amount END), 0) AS even_sum
FROM transactions
GROUP BY transaction_date
ORDER BY transaction_date ASC;
1
2
3
4
5
6
7

SELECT
  transaction_date,
  COALESCE(SUM(CASE WHEN amount % 2 = 1 THEN amount END), 0) AS odd_sum,
  COALESCE(SUM(CASE WHEN amount % 2 = 0 THEN amount END), 0) AS even_sum
FROM transactions
GROUP BY transaction_date
ORDER BY transaction_date ASC;
1
2
3
4
5
6
7
8

class Solution:
    def odd_even_transactions(self, transactions: 'pd.DataFrame') -> 'pd.DataFrame':
        df = transactions.copy()
        df['odd'] = df['amount'].where(df['amount'] % 2 == 1, 0)
        df['even'] = df['amount'].where(df['amount'] % 2 == 0, 0)
        res = df.groupby('transaction_date').agg(odd_sum=('odd', 'sum'), even_sum=('even', 'sum')).reset_index()
        res = res.sort_values('transaction_date')
        return res

Complexity

  • ⏰ Time complexity: O(N), where N is the number of transactions. Each row is processed once.
  • 🧺 Space complexity: O(D), where D is the number of unique dates, for the result table.