Monthly Transactions I - Practice Coding Problems

Monthly Transactions I - Problem

Database Medium

📊 Monthly Transaction Report Generator

You're working for a fintech company that processes millions of transactions worldwide. Your task is to create a monthly financial report that summarizes transaction data by country and month.

Given a Transactions table with the following structure:

Column Name	Type	Description
`id`	int	Unique transaction identifier
`country`	varchar	Country where transaction occurred
`state`	enum	Transaction status: "approved" or "declined"
`amount`	int	Transaction amount in cents
`trans_date`	date	Date when transaction was processed

Your mission: Write an SQL query to generate a comprehensive monthly report showing:

📅 Month and Country breakdown
📈 Total transactions count and total amount
✅ Approved transactions count and approved amount

The report should help executives quickly understand transaction patterns and approval rates across different markets.

Input & Output

example_1.sql — Basic Monthly Report

$ Input: Transactions table: | id | country | state | amount | trans_date | |----|---------|----------|--------|-----------| | 121| US | approved | 1000 | 2018-12-18| | 122| US | declined | 2000 | 2018-12-19| | 123| US | approved | 2000 | 2019-01-01| | 124| DE | approved | 2000 | 2019-01-07|

› Output: | month | country | trans_count | trans_total_amount | approved_count | approved_total_amount | |---------|---------|-------------|-------------------|----------------|-----------------------| | 2018-12 | US | 2 | 3000 | 1 | 1000 | | 2019-01 | DE | 1 | 2000 | 1 | 2000 | | 2019-01 | US | 1 | 2000 | 1 | 2000 |

💡 Note: December 2018: US had 2 transactions totaling $3000, with 1 approved for $1000. January 2019: DE had 1 approved transaction for $2000, US had 1 approved transaction for $2000.

example_2.sql — All Declined Transactions

$ Input: Transactions table: | id | country | state | amount | trans_date | |----|---------|----------|--------|-----------| | 201| CA | declined | 500 | 2019-03-15| | 202| CA | declined | 750 | 2019-03-20| | 203| MX | declined | 1200 | 2019-03-15|

› Output: | month | country | trans_count | trans_total_amount | approved_count | approved_total_amount | |---------|---------|-------------|-------------------|----------------|-----------------------| | 2019-03 | CA | 2 | 1250 | 0 | 0 | | 2019-03 | MX | 1 | 1200 | 0 | 0 |

💡 Note: March 2019: Canada had 2 declined transactions totaling $1250 with 0 approved. Mexico had 1 declined transaction for $1200 with 0 approved.

example_3.sql — Mixed Approval Rates

$ Input: Transactions table: | id | country | state | amount | trans_date | |----|---------|----------|--------|-----------| | 301| UK | approved | 800 | 2020-06-10| | 302| UK | approved | 1200 | 2020-06-15| | 303| UK | declined | 600 | 2020-06-20| | 304| FR | approved | 900 | 2020-06-25|

› Output: | month | country | trans_count | trans_total_amount | approved_count | approved_total_amount | |---------|---------|-------------|-------------------|----------------|-----------------------| | 2020-06 | FR | 1 | 900 | 1 | 900 | | 2020-06 | UK | 3 | 2600 | 2 | 2000 |

💡 Note: June 2020: France had 1 transaction (100% approved) for $900. UK had 3 transactions totaling $2600, with 2 approved totaling $2000 (67% approval rate).

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all transactions with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Space for k unique month-country combinations in result set

✓ Linear Space

Constraints

1 ≤ number of transactions ≤ 10⁵
Amount values are integers (can be negative for refunds)
State is enum: ['approved', 'declined']
Date format in result should be 'YYYY-MM'
Country names are non-empty varchar strings

Asked in

The optimal solution uses GROUP BY to organize transactions by month and country, then employs conditional aggregation with CASE statements to calculate both total and approved metrics in a single efficient table scan. Key functions: DATE_FORMAT for month extraction, COUNT(*) for total transactions, SUM(amount) for total amounts, and SUM(CASE WHEN...) for approved-only calculations.

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation (Optimal SQL)	O(n)	O(k)	Use GROUP BY with CASE statements to calculate all metrics in one pass
Nested Subqueries (Brute Force)	O(n²)	O(n)	Multiple separate queries joined together for each metric

Conditional Aggregation (Optimal SQL) — Algorithm Steps

Extract month from trans_date using DATE_FORMAT
GROUP BY the month and country to create reporting groups
Use COUNT(*) for total transaction count
Use SUM(amount) for total transaction amount
Use SUM(CASE WHEN state='approved' THEN 1 ELSE 0 END) for approved count
Use SUM(CASE WHEN state='approved' THEN amount ELSE 0 END) for approved amount

Visualization

Tap to expand

Step-by-Step Walkthrough

GROUP BY Month & Country

SQL engine automatically groups transactions by month and country

COUNT(*) for Total

Counts all transactions in each group

SUM(amount) for Total Amount

Sums all transaction amounts in each group

Conditional COUNT for Approved

Uses CASE to count only approved transactions

Conditional SUM for Approved Amount

Uses CASE to sum only approved transaction amounts

Single Result Set

All metrics calculated simultaneously in one efficient operation

Code -

solution.c — C

/* Optimal SQL Query:
SELECT 
  DATE_FORMAT(trans_date, '%Y-%m') AS month,
  country,
  COUNT(*) AS trans_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country;
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_TRANSACTIONS 100000
#define MAX_STRING 50
#define MAX_REPORTS 1000

typedef struct {
    char month[MAX_STRING];
    char country[MAX_STRING];
    char state[MAX_STRING];
    int amount;
} Transaction;

typedef struct {
    char month[MAX_STRING];
    char country[MAX_STRING];
    int trans_count;
    int trans_total_amount;
    int approved_count;
    int approved_total_amount;
} Report;

// Optimal single-pass approach
int optimalTransactionReport(Transaction* transactions, int n, Report* reports) {
    int report_count = 0;
    
    // Single pass through all transactions - optimal!
    for (int i = 0; i < n; i++) {
        Transaction* trans = &transactions[i];
        
        // Find existing report or create new one
        int report_idx = -1;
        for (int j = 0; j < report_count; j++) {
            if (strcmp(reports[j].month, trans->month) == 0 &&
                strcmp(reports[j].country, trans->country) == 0) {
                report_idx = j;
                break;
            }
        }
        
        if (report_idx == -1) {
            // Create new report
            report_idx = report_count++;
            strcpy(reports[report_idx].month, trans->month);
            strcpy(reports[report_idx].country, trans->country);
            reports[report_idx].trans_count = 0;
            reports[report_idx].trans_total_amount = 0;
            reports[report_idx].approved_count = 0;
            reports[report_idx].approved_total_amount = 0;
        }
        
        Report* report = &reports[report_idx];
        
        // Update all metrics in single pass
        report->trans_count++;
        report->trans_total_amount += trans->amount;
        
        if (strcmp(trans->state, "approved") == 0) {
            report->approved_count++;
            report->approved_total_amount += trans->amount;
        }
    }
    
    return report_count;
}

int main() {
    Transaction transactions[MAX_TRANSACTIONS];
    Report reports[MAX_REPORTS];
    
    // Parse input (simplified)
    int n = 0; // number of transactions
    
    // Process and output results
    int report_count = optimalTransactionReport(transactions, n, reports);
    
    // Output JSON format
    printf("[");
    for (int i = 0; i < report_count; i++) {
        if (i > 0) printf(",");
        printf("{\"month\":\"%s\",", reports[i].month);
        printf("\"country\":\"%s\",", reports[i].country);
        printf("\"trans_count\":%d,", reports[i].trans_count);
        printf("\"trans_total_amount\":%d,", reports[i].trans_total_amount);
        printf("\"approved_count\":%d,", reports[i].approved_count);
        printf("\"approved_total_amount\":%d}", reports[i].approved_total_amount);
    }
    printf("]\n");
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all transactions with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Space for k unique month-country combinations in result set

✓ Linear Space

Constraints

1 ≤ number of transactions ≤ 10⁵
Amount values are integers (can be negative for refunds)
State is enum: ['approved', 'declined']
Date format in result should be 'YYYY-MM'
Country names are non-empty varchar strings

25.0K Views

Medium Frequency

~15 min Avg. Time

850 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

📊 Monthly Transaction Report Generator

Input & Output

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Conditional Aggregation (Optimal SQL) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler