Welcome to Subscribe On Youtube

3220. Odd and Even Transactions

Description

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:

+-+--++
\| 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       \|
++++----+
  

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.

Solutions

Solution 1: Grouping and Summing

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.

• Python
• SQL

• import pandas as pd
  
  
  def sum_daily_odd_even(transactions: pd.DataFrame) -> pd.DataFrame:
      transactions["odd_sum"] = transactions["amount"].where(
          transactions["amount"] % 2 == 1, 0
      )
      transactions["even_sum"] = transactions["amount"].where(
          transactions["amount"] % 2 == 0, 0
      )
  
      result = (
          transactions.groupby("transaction_date")
          .agg(odd_sum=("odd_sum", "sum"), even_sum=("even_sum", "sum"))
          .reset_index()
      )
  
      result = result.sort_values("transaction_date")
  
      return result
  
  

• # Write your MySQL query statement below
  SELECT
      transaction_date,
      SUM(IF(amount % 2 = 1, amount, 0)) AS odd_sum,
      SUM(IF(amount % 2 = 0, amount, 0)) AS even_sum
  FROM transactions
  GROUP BY 1
  ORDER BY 1;
  
  

All Problems

All Solutions