{x}
blog image

Monthly Transactions I

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

Solution Explanation: Monthly Transactions I

This problem requires retrieving aggregated data from the Transactions table, specifically focusing on monthly transactions categorized by country. The goal is to compute the total number of transactions, total transaction amount, number of approved transactions, and total amount of approved transactions for each month and country.

Approach

The solution utilizes SQL's grouping and aggregation capabilities. We achieve this by:

  1. Extracting the Month: The DATE_FORMAT(trans_date, '%Y-%m') function extracts the year and month from the trans_date column, formatting it as 'YYYY-MM'. This creates a consistent monthly grouping key.

  2. Grouping the Data: The GROUP BY 1, 2 clause groups the transactions by the extracted month (column 1) and country (column 2). This ensures that aggregations are performed separately for each month and country combination.

  3. Aggregating the Data: Several aggregate functions are used within the SELECT statement:

    • COUNT(1): Counts all transactions within each group.
    • SUM(state = 'approved'): Counts the number of approved transactions. The expression state = 'approved' evaluates to 1 (true) for approved transactions and 0 (false) otherwise. Summing these values provides the count of approved transactions.
    • SUM(amount): Calculates the total amount of all transactions in each group.
    • SUM(IF(state = 'approved', amount, 0)): Calculates the total amount of only the approved transactions. The IF function conditionally sums the amount only when the transaction is approved; otherwise, it adds 0.

Code (MySQL)

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(IF(state = 'approved', 1, 0)) 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 1, 2;

This SQL query efficiently retrieves the required statistics. The use of IF (or equivalently, CASE WHEN as shown above) is crucial for selectively summing the amounts based on the transaction state.

Time Complexity Analysis

The time complexity of this SQL query is dominated by the GROUP BY operation. In general, the time complexity of a GROUP BY operation is O(N log N) or O(N) depending on the specific database implementation and indexing. N represents the number of rows in the Transactions table. The other operations (date formatting, summing, counting) are linear, O(N), and therefore don't dominate the overall complexity.

Therefore, the overall time complexity is approximately O(N log N) or O(N), depending on the database's implementation details. The space complexity is also proportional to the number of unique month-country combinations, which could be significantly smaller than N in many cases.