LeetCode 1193 - Monthly Transactions I

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

The problem is asking us to produce aggregated statistics on transactions, grouped by month and country, from the Transactions table. Each row in the table represents a transaction and contains the following columns: id (primary key), country (string), state (enum of approved or declined), amount (integer), and trans_date (date).

For each combination of month and country, we are asked to compute six values: the month itself, the country, the total number of transactions (trans_count), the number of approved transactions (approved_count), the total amount across all transactions (trans_total_amount), and the total amount across approved transactions only (approved_total_amount). The month is derived from the trans_date in YYYY-MM format.

The constraints are relatively straightforward: the table may have multiple rows per month and per country, the state column is strictly limited to "approved" or "declined", and amount is an integer. Importantly, the problem allows the results to be returned in any order, so we do not need to worry about sorting. Edge cases to consider include months with no approved transactions, countries with only one transaction in a month, and months with only declined transactions.

Approaches

A brute-force approach would involve manually iterating through all the transactions in a procedural fashion, accumulating counts and sums for each month and country. While this would produce correct results, it is not scalable or idiomatic in SQL. SQL provides aggregation functions and grouping operators that are more efficient and expressive for this task.

The optimal approach leverages SQL's GROUP BY clause to aggregate rows based on the derived month (DATE_FORMAT(trans_date, '%Y-%m')) and the country. Standard aggregate functions COUNT and SUM are used to compute the total number of transactions and total amounts. Conditional aggregation with CASE is employed to calculate approved transactions and their sums. This method is efficient because it only scans the table once and computes all required aggregates in a single pass.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n*m)	O(n*m)	Manually iterate through all transactions and accumulate values in a procedural way.
Optimal	O(n)	O(k)	Use SQL `GROUP BY` and aggregate functions to compute results in a single query, where `k` is the number of distinct month-country combinations.

Algorithm Walkthrough

Extract the month from the trans_date column in YYYY-MM format. This can be achieved using the DATE_FORMAT function in MySQL or TO_CHAR in PostgreSQL.
Group the transactions by the derived month and the country column using GROUP BY.
For each group, count all transactions to get trans_count.
Use conditional aggregation to count only the transactions where state = 'approved' to get approved_count.
Sum the amount column for all transactions to get trans_total_amount.
Use conditional aggregation with SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) to compute approved_total_amount.
Select all aggregated columns and return the result.

Why it works: The GROUP BY clause ensures that all transactions for a specific month and country are aggregated together. Conditional aggregation ensures that only approved transactions contribute to the approved counts and sums. This approach guarantees correctness while minimizing table scans.

Python Solution

Although this is an SQL problem, we can demonstrate how to execute the query using Python with a database connection:

import sqlite3

query = """
SELECT
    strftime('%Y-%m', trans_date) AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY month, country
"""

# Example Python usage
conn = sqlite3.connect('transactions.db')
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
    print(row)

This code connects to a SQLite database, executes the SQL query, and fetches all aggregated results. The conditional aggregation is implemented using CASE WHEN statements.

Go Solution

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
)

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        panic(err)
    }
    defer db.Close()

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

    rows, err := db.Query(query)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var month, country string
        var transCount, approvedCount, transTotal, approvedTotal int
        err := rows.Scan(&month, &country, &transCount, &approvedCount, &transTotal, &approvedTotal)
        if err != nil {
            panic(err)
        }
        fmt.Println(month, country, transCount, approvedCount, transTotal, approvedTotal)
    }
}

The Go implementation differs slightly in syntax for connecting to the database and scanning results. Conditional aggregation works the same way as in Python/SQL.

Worked Examples

Example 1:

Input:

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

Step-by-step aggregation:

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

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n)	Single scan of the Transactions table to aggregate all rows.
Space	O(k)	Stores k aggregated rows, where k is the number of distinct month-country combinations.

The query scales linearly with the number of transactions and is efficient for practical database sizes. Conditional aggregation does not increase time complexity beyond a single pass.

Test Cases

# Provided example
assert fetch_aggregated_transactions() == [
    ('2018-12', 'US', 2, 1, 3000, 1000),
    ('2019-01', 'US', 1, 1, 2000, 2000),
    ('2019-01', 'DE', 1, 1, 2000, 2000)
]

# Edge case: month with only declined transactions
assert fetch_aggregated_transactions() == [
    ('2023-05', 'US', 3, 0, 4500, 0)
]

# Single transaction
assert fetch_aggregated_transactions() == [
    ('2023-06', 'CA', 1, 1, 1000, 1000)
]

# Multiple countries same month
assert fetch_aggregated_transactions() == [
    ('2023-07', 'US', 2, 1, 3000, 1000),
    ('2023-07', 'CA', 1, 1, 1000, 1000)
]

Test	Why
Provided examples	Validate correctness for multiple months and countries.
Only declined transactions	Tests handling of zero approved counts and sums.
Single transaction	Minimal input size edge case.
Multiple countries, same month	Ensures grouping by both month and country works correctly.

Edge Cases

First, consider months where all transactions are declined. A naive sum for approved transactions might incorrectly include declined amounts; using conditional aggregation ensures only approved transactions contribute.

Second, months with a single transaction should still appear in the result set. The aggregation logic ensures that even if there is only one row in a group, it is counted correctly.

Third, multiple countries sharing the same month could be incorrectly aggregated together if we only group by month. Grouping by both month and country guarantees that each country gets its own aggregated row per month, maintaining data accuracy.