Domain 2MediumMEDIUM

Monthly Transactions I

Implements SQL GROUP BY and HAVING logic in Python to filter monthly transactions by count threshold and amount limit.

Domain: medium•Updated: 2026-09-10
#aggregation#hash-map#O(N)#filtering

Problem Breakdown

The goal is to identify months where transaction patterns meet specific criteria, typically involving aggregating data by month (e.g., GROUP BY) and filtering based on group-level properties like total count or sum of amounts (HAVING).

Inputs: A collection of transactions containing at least a month identifier and an amount. Outputs: A filtered list of months that satisfy the business logic conditions. Implicit Constraints & Edge Cases:

  • Empty Input: Handling cases where no transactions exist (return empty list).
  • Grouping Logic: The filtering condition depends on aggregated values per group, not individual rows. This maps to iterating over dictionary keys after accumulation in Python.
  • Data Types: Amounts might be floats; ensure precision handling if necessary.

Intuition

A brute force approach would check every transaction against global stats repeatedly (O(N^2)).

  1. Key Insight: We must aggregate data into buckets (months) first to calculate counts/sums efficiently, then apply filters based on those local stats. This maps directly to SQL’s GROUP BY + HAVING.
  2. Optimal Approach: Use a Hash Map (defaultdict) to accumulate metrics per month in O(N), then iterate over the map keys (M <= N) for filtering logic.

Approach

  1. Initialize a dictionary where keys are months and values store {count: int, total_amount: float}.
  2. Iterate through each transaction record; extract month key; update count and sum for that month.
  3. After accumulation, iterate over the dictionary items (months).
  4. Apply filtering logic based on thresholds (threshold, limit) to build the result set. If a threshold is not provided or met conditions are generic, ensure at least one transaction exists per group if required by specific constraints.
  5. Return the filtered list of months/records as specified in output requirements (typically just month ID).

Complexity Analysis

  • Time: O(N), where N is the number of transactions. We iterate through input once for aggregation and M times for filtering groups (M <= N).
  • Space: O(M), where M is the number of unique months to store aggregated dictionary.

Solution

from collections import defaultdict
from typing import List, Dict, Any, Optional

def monthly_transactions(transactions: List[Dict[str, Any]], threshold: int = 10, limit: float = None) -> List[Dict[str, Any]]:
    """
    Simulates SQL aggregation logic (GROUP BY + HAVING) in Python.
    
    Args:
        transactions: A list of dicts representing rows from the Transactions table 
                      with keys 'month' and 'amount'.
        threshold: Minimum count required for a month to be included. Default 10.
        limit: Maximum total amount allowed for a month to be included. Optional.
                      
    Returns:
        List of months meeting specific criteria based on aggregated stats.
    """
    # Aggregation bucket using a dictionary (equivalent to SQL GROUP BY)
    monthly_stats = defaultdict(lambda: {'count': 0, 'total_amount': 0})

    for tx in transactions:
        month_key = str(tx['month']) if not isinstance(tx.get('month'), int/float) else tx['month']
        amount = float(tx['amount']) if isinstance(tx.get('amount'), (str)) else float(tx['amount'])
        
        # Accumulate metrics per group (Month)
        monthly_stats[month_key]['count'] += 1
        monthly_stats[month_key]['total_amount'] += amount

    result = []
    
    for month_key, stats in monthly_stats.items():
        count = stats['count']
        total_amt = stats['total_amount']
        
        # Apply filtering logic (HAVING clause equivalent)
        if threshold is None: 
            continue
        
        meets_count_threshold = count >= threshold
        meets_limit_condition = True  # Default to true if limit not specified or handled by SQL context
            
        if limit is not None and total_amt > limit:
            meets_limit_condition = False

        if meets_count_threshold and meets_limit_condition:
            result.append({
                'month': month_key,
                **stats 
            })

    return result

Edge Cases Handled

  • Empty Input: The loop over transactions simply won’t execute; the function returns an empty list immediately.
  • No Matching Groups: If no months meet filtering criteria (e.g., all counts below threshold), the final iteration produces an empty result.
  • Missing Keys/Types: Using .get() and type casting handles potential string vs int amount variations gracefully, though standard schema usually guarantees types.