Domain 2MediumMEDIUM

Monthly Transactions II

Calculate monthly transaction counts and revenue from approved transactions using Pandas groupby aggregation.

Domain: medium•Updated: 2026-09-10
#Pandas GroupBy#DataFrame Aggregation#O(N)#Linear Space

Problem Breakdown

The objective is to compute aggregated metrics (transaction_count and revenue) for each month based on the Transactions table. The solution must focus specifically on rows where the transaction state is ‘approved’.

Schema Assumptions:

  • Table Name: transactions_df (DataFrame)
  • Columns: id, order_id, amount, state, date.
  • Key Logic: Filter by state == 'approved', group by month derived from date, and aggregate count/sum.

Outputs Required:

  1. A DataFrame with columns: month (YYYY-MM), transaction_count, revenue.
  2. Rows must be sorted or grouped correctly to represent monthly buckets accurately.

Intuition

Aggregation problems are best solved by filtering the dataset first to reduce memory overhead and computational load before grouping. In Pandas, vectorized operations like .groupby() leverage C-level optimizations which run in linear time O(N)O(N) relative to row count.

The core insight is that revenue should only include approved transactions; cancelled ones must be excluded entirely from both counts and sums. Using dt.to_period('M') efficiently extracts the monthly key without string manipulation overhead, ensuring high performance even on large datasets (millions of rows).

Approach

  1. Filter: Select only rows where state.lower() == 'approved'. This ensures cancelled transactions do not affect revenue or count metrics.
  2. Group Key Generation: Extract the month from the date column using .dt.to_period('M'), which handles timezone-naive timestamps robustly for grouping purposes.
  3. Aggregation: Use .groupby() with .agg() to calculate:
    • Count of rows (using 'id') as transaction count.
    • Sum of amount column as revenue.
  4. Reset Index: Convert the group keys back into a standard DataFrame row (month).

Complexity Analysis

  • Time Complexity: O(N)O(N), where NN is the number of rows in the input DataFrame. Filtering and GroupBy operations are linear scans optimized by Pandas internals.
  • Space Complexity: O(M+K)O(M + K), where MM is memory for the filtered subset (worst case all approved) and KK is the number of distinct months (K≤N/12K \le N/12). This fits standard interview constraints without excessive overhead.

Solution

import pandas as pd

def monthly_transactions(transactions_df):
    """
    Calculates monthly transaction stats (count and revenue) 
    based on approved transactions only using Pandas vectorization.
    
    Args:
        transactions_df (pd.DataFrame): DataFrame with columns ['id', 'order_id', 'amount', 'state', 'date']
        
    Returns:
        pd.DataFrame: Resulting aggregated data per month with columns ['month', 'transaction_count', 'revenue'].
    """
    # Filter for approved transactions to ensure accurate revenue and count metrics. 
    # Using .str.lower() handles case-insensitive matching (e.g., "Approved", "APPROVED").
    filtered = transactions_df[transactions_df['state'].str.lower() == 'approved']

    # Extract monthly period from the date column using vectorized accessor.
    # This creates a PeriodIndex which is ideal for grouping by month.
    if not filtered.empty:
        month_key = filtered['date'].dt.to_period('M')
        
        return filtered.groupby(month_key).agg(
            transaction_count=('id', 'count'), 
            revenue=('amount', 'sum')
        ).reset_index()

    # If no approved transactions exist, groupby on empty df returns correct schema with 0 rows.
    # However, to ensure consistent column names even if input is completely empty or filtered out:
    return pd.DataFrame(columns=['month', 'transaction_count', 'revenue'])

# Example usage (assuming input data structure):
if __name__ == "__main__":
    df = pd.DataFrame({
        "id": [1, 2, 3], 
        "order_id": [101, 102, 103], 
        "amount": [50.0, -10.0, None], # Negative or NULL handled by sum logic naturally if filtered
        "state": ["approved", "cancelled", "approved"],
        "date": pd.to_datetime(["2023-01-01", "2023-01-05", "2023-01-10"])
    })
    
    print(monthly_transactions(df))

Edge Cases Handled

  • Cancelled Transactions: Explicitly filtered out before aggregation to prevent inflating revenue counts with negative values from cancellations if they exist in amount.
  • NULL Amounts: Pandas’ .agg function handles NULL sums gracefully (ignores them), preventing calculation errors. If an approved transaction has a null amount, it is counted but adds 0 to revenue.
  • Empty Input/No Approved Rows: The code checks for empty filtered data and returns a DataFrame with the correct schema columns (month, transaction_count, revenue) containing no rows, rather than returning raw input columns which would be incorrect output format.
  • Date Parsing Errors: Assumes valid datetime input; in production, one would add error handling (try-except) around .dt.to_period().