Monthly Transactions I | Leetcode 1193 | Crack SQL Interviews in 50 Qs

Welcome to Chapter 20 of the SQL Interview Series. In this chapter, we tackle LeetCode 1193 (Monthly Transactions I), a classic data aggregation problem that tests your ability to extract date components, handle conditional sums, group by multiple columns, and clean up transaction metrics in MySQL.

1. Problem Statement & Schema Overview

We are given a single database table named Transactions which records incoming financial transactions across various countries.

Table Schema (Transactions)

Required Output Columns

To solve this interview question, we must write a SQL query that returns the following 6 columns for each unique month and country combination:

1. month: The year and month formatted as YYYY-MM.
2. country: The country associated with the transactions.
3. trans_count: Total number of transactions executed in that month and country.
4. approved_count: Total number of transactions that were successfully approved.
5. trans_total_amount: Sum of monetary amounts for all transactions.
6. approved_total_amount: Sum of monetary amounts strictly for approved transactions.

The result table can be returned in any order.

2. Real-Life Mental Model & Analogy

Real-Life Analogy: Imagine you manage a regional bank branch network. At the end of every business month, corporate headquarters asks for a performance ledger. They don't want to see individual receipts; instead, they want a neatly summarized spreadsheet grouped by Month and Country.

Within each country folder for a specific month, you must count how many total receipts came in, tally how many were successfully approved, add up all money moving through the register, and isolate only the cash collected from approved transactions.

3. Naive vs. Production-Ready Approach

The Naive Approach (Mental Trap)

A beginner might attempt to write separate subqueries for each column: one subquery for total counts, another for approved counts, another for total sums, and join them all together.

• The Problem: This leads to massive performance overhead, complex multiple joins, and heavily bloated SQL code that is prone to aggregation bugs and slow execution speeds in production databases.

The Production-Ready Approach (Single-Pass Grouping)

We can solve this efficiently using a single SQL query utilizing standard aggregate functions (COUNT, SUM), date formatting utilities (DATE_FORMAT), conditional logic (IF), and a composite GROUP BY clause (GROUP BY month, country).

4. Step-by-Step SQL Construction

Let's build the query from the ground up, starting with our data source and grouping logic.

Step A: Defining the Source and Grouping

We pull data directly from the Transactions table and group records that share the same month and country.

SELECT 
    -- We will format our month here
    country,
    -- Aggregate metrics go here
FROM 
    Transactions
GROUP BY 
    month, 
    country;

Step B: Formatting the Date into YYYY-MM

MySQL provides the DATE_FORMAT() function to extract and format specific parts of a datetime or date column. To convert trans_date into the YYYY-MM format required by the problem, we use the specifiers %Y (4-digit year) and %m (2-digit month).

DATE_FORMAT(trans_date, '%Y-%m') AS month

Step C: Calculating Transaction Counts

• Total Transactions (trans_count): Simply count the primary key id for each group.
• Approved Transactions (approved_count): We can leverage MySQL's ability to evaluate boolean conditions as 1 (true) or 0 (false). By summing up instances where state = 'approved', we instantly get our approved count.

COUNT(id) AS trans_count,
SUM(state = 'approved') AS approved_count

Note: In MySQL, conditional expressions like state = 'approved' evaluate to 1 when true and 0 when false. Passing this directly into a SUM() function is a clean, idiomatic way to count matching rows without writing cumbersome CASE WHEN statements.

Step D: Calculating Monetary Amounts

• Total Amount (trans_total_amount): The cumulative sum of all transaction amounts.
• Approved Amount (approved_total_amount): We only want to sum the amount if the state is 'approved'. Otherwise, we contribute 0 to the sum. We achieve this using an IF conditional statement.

SUM(amount) AS trans_total_amount,
SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount

5. Complete Final SQL Query

Here is the complete, clean, production-ready SQL solution that successfully passes LeetCode 1193:

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(id) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, 
    country;

6. Execution Flow Walkthrough

Let's trace how the database engine executes our final query:

7. Summary and Key Takeaways

• Date Formatting: Use DATE_FORMAT(date_col, '%Y-%m') to reshape standard dates into custom year-month string representations.
• Composite Grouping: You can pass multiple columns and computed aliases (like month) into the GROUP BY clause (GROUP BY month, country) to segment aggregates precisely.
• Boolean Summation trick: SUM(condition = value) is a concise MySQL shorthand to count matching rows dynamically without explicit CASE blocks.
• Conditional Summation with IF: Use IF(condition, true_value, false_value) inside aggregate functions like SUM() to filter monetary amounts conditionally on the fly.
• Efficiency: Solving complex reporting queries in a single pass minimizes table scans and ensures high query performance in production database environments.