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 fromdate, and aggregate count/sum.
Outputs Required:
- A DataFrame with columns:
month(YYYY-MM),transaction_count,revenue. - 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 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
- Filter: Select only rows where
state.lower() == 'approved'. This ensures cancelled transactions do not affect revenue or count metrics. - Group Key Generation: Extract the month from the
datecolumn using.dt.to_period('M'), which handles timezone-naive timestamps robustly for grouping purposes. - Aggregation: Use
.groupby()with.agg()to calculate:- Count of rows (using
'id') as transaction count. - Sum of
amountcolumn as revenue.
- Count of rows (using
- Reset Index: Convert the group keys back into a standard DataFrame row (
month).
Complexity Analysis
- Time Complexity: , where is the number of rows in the input DataFrame. Filtering and GroupBy operations are linear scans optimized by Pandas internals.
- Space Complexity: , where is memory for the filtered subset (worst case all approved) and is the number of distinct months (). 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’
.aggfunction 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().