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 | +----------+---------+-------------+----------------+--------------------+-----------------------+
To solve #1193 Monthly Transactions I, the goal is to compute monthly transaction statistics for each country. The key idea is to group records by both the transaction month and the country. First, extract the month from the transaction date using a date formatting function so all transactions within the same month are grouped together.
Once grouped, use SQL GROUP BY to aggregate data. You can calculate metrics such as the total number of transactions, total transaction amount, number of approved transactions, and the amount associated with approved transactions. Conditional aggregation using expressions like SUM(CASE WHEN ... THEN ... END) helps compute values for specific transaction states (e.g., approved).
This approach efficiently summarizes the dataset in a single pass using SQL aggregation functions. Since the database processes each row once during grouping, the overall time complexity is O(n), where n is the number of transaction records, and the space complexity depends on the number of grouped results.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| SQL Aggregation with GROUP BY and Conditional SUM | O(n) | O(g) where g is the number of month-country groups |
Learn With Chirag
Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
SQL aggregation and reporting problems similar to Monthly Transactions I are common in data-related interviews at large tech companies. They test your understanding of grouping, filtering, and conditional aggregation in relational databases.
The optimal approach is to use SQL aggregation with GROUP BY on the transaction month and country. Conditional aggregation using CASE expressions allows you to count and sum values for specific states like approved transactions.
Date formatting functions such as DATE_FORMAT or similar database-specific functions are useful to extract the month from the transaction date. Aggregation functions like COUNT and SUM combined with CASE conditions help compute the required metrics.
The main concept is SQL aggregation and grouping. Understanding how GROUP BY works along with conditional aggregation helps compute multiple statistics from the same dataset efficiently.