1193 Monthly Transactions I

Description

Table: Transactions

• 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”].

Problem Statement

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:

Output:

Solution

The problem asking to aggregate information based on month of the transaction which you can find from trans_date column. You can use DATE_FORMAT() function to extract the year and month from this column using DATE_FORMAT(trans_date, '%Y-%m'). Next, you need to aggregate by this month and country fields.

• trans_count is simply count of records when grouped by month and country.
• appproved_count is count of approved records when grouped by month and country. Here, you can use state = "approved" clause.
• trans_total_amount is sum of amount when grouped by month and country.
• approved_total_amount is sum of amount when state = "approved" and grouped by month and country.

1SELECT 
2    DATE_FORMAT(trans_date, "%Y-%m") AS month, 
3    country, 
4    COUNT(1) AS trans_count, 
5    SUM(IF(state = "approved", 1, 0)) AS approved_count, 
6    SUM(amount) AS trans_total_amount, 
7    SUM(IF(state = "approved", amount, 0 )) approved_total_amount 
8    FROM Transactions
9    GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country;