Watch 10 video solutions for Monthly Transactions I, a medium level problem involving Database. This walkthrough by Learn With Chirag has 14,376 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |
