1193. Monthly Transactions I

Problem¶

For each (month, country), compute:

Total number of transactions.
Total transaction amount.
Number of approved transactions.
Total approved amount.

Input:

# Transactions
----+---------+----------+--------+------------
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 | 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

Key trick¶

Use conditional aggregation:

Count all rows with COUNT(*).
Count approved rows with SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END).
Sum approved amounts with SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END).

Trap¶

In Pandas, groupby drops NaN groups by default, so use dropna=False.
Do not solve this with multiple grouped DataFrames and positional assignment; group alignment can break.
SQLite does not support TO_CHAR; use strftime.

Why is it interesting?¶

This is a compact test of date bucketing, grouped aggregation, conditional aggregation, and null-key handling.

SQL solution¶

SQLite¶

SELECT
    -- SQLite formats dates with strftime.
    strftime('%Y-%m', trans_date) AS month,
    country,

    -- Count every transaction in the group.
    COUNT(*) AS trans_count,

    -- Count only approved transactions.
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,

    -- Sum every transaction amount.
    SUM(amount) AS trans_total_amount,

    -- Sum only approved transaction amounts.
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY
    strftime('%Y-%m', trans_date),
    country;

PostgreSQL¶

SELECT
    -- PostgreSQL can format dates with TO_CHAR.
    TO_CHAR(trans_date, 'YYYY-MM') AS month,
    country,

    -- Count every transaction in the group.
    COUNT(*) AS trans_count,

    -- FILTER is PostgreSQL-specific and clean for conditional aggregation.
    COUNT(*) FILTER (WHERE state = 'approved') AS approved_count,

    -- Sum every transaction amount.
    SUM(amount) AS trans_total_amount,

    -- Sum only approved transaction amounts.
    COALESCE(SUM(amount) FILTER (WHERE state = 'approved'), 0) AS approved_total_amount
FROM Transactions
GROUP BY
    TO_CHAR(trans_date, 'YYYY-MM'),
    country;

Python Pandas¶

import pandas as pd


def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
    df = transactions.copy()

    # Build the monthly bucket.
    df["month"] = pd.to_datetime(df["trans_date"]).dt.strftime("%Y-%m")

    # Build conditional columns once, then aggregate in one groupby.
    df["approved_count_value"] = (df["state"] == "approved").astype(int)
    df["approved_amount_value"] = df["amount"].where(df["state"] == "approved", 0)

    result = (
        df.groupby(["month", "country"], as_index=False, dropna=False)
        .agg(
            trans_count=("id", "size"),
            approved_count=("approved_count_value", "sum"),
            trans_total_amount=("amount", "sum"),
            approved_total_amount=("approved_amount_value", "sum"),
        )
    )

    return result[
        [
            "month",
            "country",
            "trans_count",
            "approved_count",
            "trans_total_amount",
            "approved_total_amount",
        ]
    ]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_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
    strftime('%Y-%m', trans_date),
    country
"""


def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
    df = transactions.copy()

    # Include null countries as their own group.
    df["month"] = pd.to_datetime(df["trans_date"]).dt.strftime("%Y-%m")
    df["approved_count_value"] = (df["state"] == "approved").astype(int)
    df["approved_amount_value"] = df["amount"].where(df["state"] == "approved", 0)

    return (
        df.groupby(["month", "country"], as_index=False, dropna=False)
        .agg(
            trans_count=("id", "size"),
            approved_count=("approved_count_value", "sum"),
            trans_total_amount=("amount", "sum"),
            approved_total_amount=("approved_amount_value", "sum"),
        )
    )


def run_sql_solution(rows):
    con = sqlite3.connect(":memory:")
    con.execute(
        """
        CREATE TABLE Transactions (
            id INTEGER,
            country TEXT,
            state TEXT,
            amount INTEGER,
            trans_date TEXT
        )
        """
    )
    con.executemany(
        """
        INSERT INTO Transactions (id, country, state, amount, trans_date)
        VALUES (?, ?, ?, ?, ?)
        """,
        rows,
    )

    return pd.read_sql_query(SQL_QUERY, con)


def normalize(df: pd.DataFrame) -> pd.DataFrame:
    cols = [
        "month",
        "country",
        "trans_count",
        "approved_count",
        "trans_total_amount",
        "approved_total_amount",
    ]

    out = df[cols].copy()

    # Make null comparison stable between SQLite None and Pandas NaN.
    out["country"] = out["country"].where(out["country"].notna(), "<NULL>")

    return out.sort_values(cols).reset_index(drop=True)


@pytest.mark.parametrize(
    "rows, expected_rows",
    [
        (
            [
                (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"),
            ],
            [
                ("2018-12", "US", 2, 1, 3000, 1000),
                ("2019-01", "DE", 1, 1, 2000, 2000),
                ("2019-01", "US", 1, 1, 2000, 2000),
            ],
        ),
        (
            [
                (121, "US", "approved", 1000, "2018-12-18"),
                (122, "US", "declined", 2000, "2018-12-19"),
                (123, "US", "approved", 2000, "2019-01-01"),
                (124, None, "approved", 2000, "2019-01-07"),
            ],
            [
                ("2018-12", "US", 2, 1, 3000, 1000),
                ("2019-01", None, 1, 1, 2000, 2000),
                ("2019-01", "US", 1, 1, 2000, 2000),
            ],
        ),
        (
            [
                (1, "FR", "declined", 10, "2020-05-01"),
                (2, "FR", "declined", 20, "2020-05-02"),
                (3, "FR", "approved", 30, "2020-06-01"),
            ],
            [
                ("2020-05", "FR", 2, 0, 30, 0),
                ("2020-06", "FR", 1, 1, 30, 30),
            ],
        ),
    ],
)
def test_monthly_transactions_sql_and_pandas(rows, expected_rows):
    expected = pd.DataFrame(
        expected_rows,
        columns=[
            "month",
            "country",
            "trans_count",
            "approved_count",
            "trans_total_amount",
            "approved_total_amount",
        ],
    )

    transactions = pd.DataFrame(
        rows,
        columns=["id", "country", "state", "amount", "trans_date"],
    )

    sql_result = run_sql_solution(rows)
    pandas_result = monthly_transactions(transactions)

    assert_frame_equal(normalize(sql_result), normalize(expected), check_dtype=False)
    assert_frame_equal(normalize(pandas_result), normalize(expected), check_dtype=False)

Comment on my solution¶

Your SQL logic is correct for PostgreSQL, but not SQLite because SQLite does not have TO_CHAR.
Your first Pandas solution fails because groupby(["month", "country"]) drops null countries unless dropna=False.
Your second Pandas solution still has the same null-country issue.
The second Pandas solution also relies on positional alignment between separately grouped DataFrames, which is fragile.
A single groupby(..., dropna=False).agg(...) is simpler, faster, and safer.

-- WORKS
WITH transactions_month AS (
    SELECT
        *,
        TO_CHAR(trans_date, 'YYYY-MM') AS month
    FROM Transactions
)
SELECT
    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_month
GROUP BY month, country;

import pandas as pd

# Wrong Answer 15/16 testcases passed
# 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 | null    | approved | 2000   | 2019-01-07 |
#
# Expected:
# | 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 | null    | 1           | 1              | 2000               | 2000                  |
def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
    df = transactions.copy()
    df["month"] = df["trans_date"].dt.strftime("%Y-%m")
    df_trans_count = (
        df.groupby(["month", "country"], as_index=False)
        .size()
        .rename(columns={"size": "trans_count"})
    )
    df["approved"] = (df["state"] == "approved").astype(int)
    df_approved_count = (
        df.groupby(["month", "country"], as_index=False)
        .agg(approved_count=("approved", "sum"))
    )
    df_trans_total_amount = (
        df.groupby(["month", "country"], as_index=False)
        .agg(trans_total_amount=("amount", "sum"))
    )
    df["approved_amount"] = df["amount"].where((df["state"] == "approved"), 0)
    df_approved_total_amount = (
        df.groupby(["month", "country"], as_index=False)
        .agg(approved_total_amount=("approved_amount", "sum"))
    )
    result = (
     df_trans_count.merge(df_approved_count, on=["month", "country"], how="left")
        .merge(df_trans_total_amount, on=["month", "country"], how="left")
        .merge(df_approved_total_amount, on=["month", "country"], how="left")
    )
    return result

# Wrong Answer 15/16 testcases passed
# 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 | null    | approved | 2000   | 2019-01-07 |
#
# Expected:
# | 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 | null    | 1           | 1              | 2000               | 2000                  |

def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
    df = transactions.copy()
    df["month"] = df["trans_date"].dt.strftime("%Y-%m")
    df_trans_count = (
        df.groupby(["month", "country"], as_index=False)
        .size()
        .rename(columns={"size": "trans_count"})
        .sort_values(["month", "country"])
        .reset_index(drop=True)
    )
    df["approved"] = (df["state"] == "approved").astype(int)
    df_approved_count = (
        df.groupby(["month", "country"], as_index=False)
        .agg(approved_count=("approved", "sum"))
        .sort_values(["month", "country"])
        .reset_index(drop=True)
    )
    df_trans_total_amount = (
        df.groupby(["month", "country"], as_index=False)
        .agg(trans_total_amount=("amount", "sum"))
        .sort_values(["month", "country"])
        .reset_index(drop=True)
    )
    df["approved_amount"] = df["amount"].where((df["state"] == "approved"), 0)
    df_approved_total_amount = (
        df.groupby(["month", "country"], as_index=False)
        .agg(approved_total_amount=("approved_amount", "sum"))
        .sort_values(["month", "country"])
        .reset_index(drop=True)
    )
    result = df_trans_count
    result["approved_count"] = df_approved_count["approved_count"]
    result["trans_total_amount"] = df_trans_total_amount["trans_total_amount"]
    result["approved_total_amount"] = df_approved_total_amount["approved_total_amount"]

    return result[["month", "country", "trans_count",
                   "approved_count", "trans_total_amount",
                   "approved_total_amount"]]