Odd and Even Transactions - Practice Coding Problems

Odd and Even Transactions - Problem

Database Medium

You are analyzing financial data for a trading platform and need to generate daily reports that categorize transactions by their amounts.

Given a transactions table with transaction IDs, amounts, and dates, your task is to calculate the daily sum of odd-amount transactions and daily sum of even-amount transactions.

Key Requirements:

Group transactions by date
Separate odd amounts (1, 3, 5, 101, etc.) from even amounts (2, 4, 6, 100, etc.)
If no odd transactions exist for a date, show 0 for odd sum
If no even transactions exist for a date, show 0 for even sum
Sort results by transaction date in ascending order

This type of analysis helps financial teams understand daily transaction patterns and identify trends in customer behavior.

Input & Output

example_1.sql — Basic Case

$ Input: transactions table: | transaction_id | amount | transaction_date | |----------------|--------|------------------| | 1 | 150 | 2023-01-01 | | 2 | 25 | 2023-01-01 | | 3 | 30 | 2023-01-02 | | 4 | 75 | 2023-01-02 |

› Output: | transaction_date | odd_sum | even_sum | |------------------|---------|----------| | 2023-01-01 | 25 | 150 | | 2023-01-02 | 75 | 30 |

💡 Note: For 2023-01-01: amount 150 is even, amount 25 is odd. For 2023-01-02: amount 30 is even, amount 75 is odd. Results are sorted by date ascending.

example_2.sql — Only Odd Transactions

$ Input: transactions table: | transaction_id | amount | transaction_date | |----------------|--------|------------------| | 1 | 15 | 2023-01-01 | | 2 | 33 | 2023-01-01 | | 3 | 7 | 2023-01-02 |

› Output: | transaction_date | odd_sum | even_sum | |------------------|---------|----------| | 2023-01-01 | 48 | 0 | | 2023-01-02 | 7 | 0 |

💡 Note: All transactions have odd amounts. Even sums are 0 for both dates as required. 2023-01-01: 15+33=48 odd, 0 even. 2023-01-02: 7 odd, 0 even.

example_3.sql — Mixed Edge Case

$ Input: transactions table: | transaction_id | amount | transaction_date | |----------------|--------|------------------| | 1 | 100 | 2023-01-01 | | 2 | 200 | 2023-01-01 | | 3 | 1 | 2023-01-03 | | 4 | 2 | 2023-01-03 |

› Output: | transaction_date | odd_sum | even_sum | |------------------|---------|----------| | 2023-01-01 | 0 | 300 | | 2023-01-03 | 1 | 2 |

💡 Note: 2023-01-01 has only even amounts (100+200=300), so odd_sum is 0. 2023-01-03 has both odd (1) and even (2) amounts. Note there's no 2023-01-02 in the data.

Visualization

Tap to expand

Understanding the Visualization

Daily Transactions Flow In

Throughout the day, transactions with various amounts are processed

Smart Categorization

Each transaction is instantly categorized as odd or even based on amount

Daily Totals Calculated

At day's end, we have separate totals for odd and even amounts

Historical Report Generated

Results are sorted chronologically for trend analysis

Key Takeaway

🎯 Key Insight: SQL's conditional aggregation with CASE statements allows us to categorize and sum transactions in a single pass, making this solution both elegant and optimal for large datasets.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through n transactions with grouping

✓ Linear Growth

Space Complexity

O(d)

Storage for d distinct dates in result set

✓ Linear Space

Constraints

1 ≤ transaction_id ≤ 10⁵
1 ≤ amount ≤ 10⁶
transaction_date is in YYYY-MM-DD format
Each transaction_id is unique
At least one transaction exists in the table

Asked in

JP JPMorgan 35 GS Goldman Sachs 28 a Amazon 25 ⊞ Microsoft 20 G Google 18

The optimal solution uses SQL conditional aggregation with GROUP BY transaction_date and SUM(CASE WHEN amount % 2 = 1 THEN amount ELSE 0 END) to calculate odd and even sums in a single table scan, achieving O(n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ One-Pass Conditional Aggregation (Optimal)	O(n)	O(d)	Use SQL's GROUP BY with CASE statements to calculate odd/even sums in single pass
Two-Pass Aggregation	O(n)	O(n)	First pass groups by date, second pass calculates odd/even sums
Multiple Subqueries (Brute Force)	O(n²)	O(d)	Separate queries for each date and transaction type, then combine results

One-Pass Conditional Aggregation (Optimal) — Algorithm Steps

Use GROUP BY transaction_date to group transactions by date
Apply SUM(CASE WHEN amount % 2 = 1 THEN amount ELSE 0 END) for odd sums
Apply SUM(CASE WHEN amount % 2 = 0 THEN amount ELSE 0 END) for even sums
ORDER BY transaction_date for ascending order

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Transaction

Read each transaction once

Group by Date

Assign to date bucket

Conditional Sum

Add to odd or even sum based on amount % 2

Return Sorted

Sort by date and return results

Code -

solution.c — C

int solution(Transaction* transactions, int n, Result* results) {
    // Hash table simulation using arrays (simplified)
    char dates[100][20];
    int odd_sums[100] = {0};
    int even_sums[100] = {0};
    int date_count = 0;
    
    // One pass: group and calculate simultaneously
    for (int i = 0; i < n; i++) {
        int date_index = -1;
        
        // Find existing date or create new one
        for (int j = 0; j < date_count; j++) {
            if (strcmp(dates[j], transactions[i].transaction_date) == 0) {
                date_index = j;
                break;
            }
        }
        
        if (date_index == -1) {
            strcpy(dates[date_count], transactions[i].transaction_date);
            date_index = date_count++;
        }
        
        // Add to appropriate sum
        if (transactions[i].amount % 2 == 1) {
            odd_sums[date_index] += transactions[i].amount;
        } else {
            even_sums[date_index] += transactions[i].amount;
        }
    }
    
    // Format results and sort
    for (int i = 0; i < date_count; i++) {
        strcpy(results[i].transaction_date, dates[i]);
        results[i].odd_sum = odd_sums[i];
        results[i].even_sum = even_sums[i];
    }
    
    qsort(results, date_count, sizeof(Result), compare_dates);
    return date_count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through n transactions with grouping

✓ Linear Growth

Space Complexity

O(d)

Storage for d distinct dates in result set

✓ Linear Space

Constraints

1 ≤ transaction_id ≤ 10⁵
1 ≤ amount ≤ 10⁶
transaction_date is in YYYY-MM-DD format
Each transaction_id is unique
At least one transaction exists in the table

28.5K Views

Medium Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

One-Pass Conditional Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler