Table: Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id is the primary key of this table. The table has information about incoming transactions. The state column is an enum of type ["approved", "declined"].
Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
Return the result table in any order.
The query result format is in the following example.
Example 1:
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 | 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 | +----------+---------+-------------+----------------+--------------------+-----------------------+
Problem Overview: Given a Transactions table containing id, country, state, amount, and trans_date, compute monthly statistics per country. For each month and country, return the total number of transactions, number of approved transactions, total transaction amount, and total approved transaction amount.
Approach 1: SQL Group By and Conditional Aggregation (O(n) time, O(1) extra space)
This approach performs a single pass over the table and groups rows by month and country. The month is extracted from trans_date using functions like DATE_FORMAT() or EXTRACT(). Aggregates such as COUNT(*) and SUM(amount) compute overall transaction metrics. Conditional aggregation handles approved transactions using expressions like SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) and SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END). The database engine scans the table once and maintains aggregate counters per group, giving O(n) time complexity with constant extra space aside from grouping buffers. This is the most efficient and idiomatic SQL solution for analytics-style queries.
Approach 2: SQL Group By and Sub-query (O(n) time, O(g) space)
This method separates total transaction metrics and approved transaction metrics into different queries, then joins them. One query groups all transactions by month and country to compute counts and total amounts. A second sub-query filters rows where state = 'approved' and computes approved counts and sums. The results are joined on the same grouping keys. While still O(n) because the table is scanned a small number of times, it introduces additional intermediate result sets proportional to the number of groups g. This pattern is useful when conditional logic becomes complex or when different filters are easier to manage in separate queries.
The key concept here is SQL aggregation. Understanding how grouping works, and how conditional logic can be embedded inside aggregate functions, is essential for many database interview problems. These patterns frequently appear in analytics queries involving metrics like counts, totals, and filtered subsets. Review related topics such as SQL, Database, and Aggregation.
Recommended for interviews: The SQL Group By with Conditional Aggregation approach is the expected solution. It performs all calculations in a single grouped query and demonstrates strong understanding of SQL aggregates. The sub-query version shows correctness but is usually less concise and slightly less efficient.
By utilizing SQL's GROUP BY clause, we can categorize transactions by year-month and country. The challenge here is to compute both the total transaction count and amount as well as approved transaction details. Using conditional aggregation helps isolate approved transactions. We extract the year-month from the transaction date using the DATE_FORMAT function and employ SUM and COUNT with CASE statements to achieve the desired calculations.
This query calculates the number of total transactions, total amount, approved transaction count, and their amount for each month and country pair using DATE_FORMAT to extract year-month and CASE within SUM for conditional aggregation.
SQL
Time Complexity: O(n), where n is the number of transactions, due to the need to process each transaction for grouping and aggregation.
Space Complexity: O(k), where k is the number of unique month-country pairs.
This approach involves leveraging SQL's GROUP BY clause along with conditional aggregation to compute the desired metrics. We can extract the month from the trans_date using the DATE_FORMAT function. To get the number of approved transactions and their total amount, use conditional aggregation by checking if the state is 'approved'.
The query groups records by month and country, counting the total transactions and summing the amounts. It uses conditional aggregation to count and sum only approved transactions, using a CASE statement for conditions.
SQL
Time Complexity is O(n), where n is the number of transactions, and Space Complexity is O(m), where m is the number of grouped results.
This approach uses a sub-query to first separate the totals and approved totals, and then combines them in the main query. It's useful to separate concerns clearly and avoid complex aggregation logic in a single step.
This solution uses a sub-query to calculate approved counts and totals upfront, then aggregates these values in the outer query by month and country.
SQL
Time Complexity is O(n), due to iterating through each transaction twice (once in the sub-query and once in the aggregation), Space Complexity is also O(m).
We can first group by month and country, and then use the COUNT and SUM functions to respectively calculate the number of transactions, the number of approved transactions, the total amount, and the total amount of approved transactions for each group.
MySQL
| Approach | Complexity |
|---|---|
| SQL Group By and Conditional Aggregation | Time Complexity: O(n), where n is the number of transactions, due to the need to process each transaction for grouping and aggregation. |
| Approach 1: SQL Grouping and Conditional Aggregation | Time Complexity is O(n), where n is the number of transactions, and Space Complexity is O(m), where m is the number of grouped results. |
| Approach 2: SQL Group By and Sub-query | Time Complexity is O(n), due to iterating through each transaction twice (once in the sub-query and once in the aggregation), Space Complexity is also O(m). |
| Grouping and Aggregation | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Group By and Conditional Aggregation | O(n) | O(1) | Best general solution when calculating filtered aggregates in one query |
| SQL Group By with Sub-query | O(n) | O(g) | Useful when separating complex filters or building metrics in stages |
Monthly Transactions I | Leetcode 1193 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 14,376 views views
Watch 9 more video solutions →Practice Monthly Transactions I with our built-in code editor and test cases.
Practice on FleetCode