Aggregate Monthly Transaction Totals and Approved Totals

### Problem For each calendar month and country, report month, country, trans_count, approved_count, trans_total_amount and approved_total_amount. Month uses the YYYY-MM format. Transaction dates are finite Gregorian dates from 0001-01-01 through 9999-12-31. Count all transactions for the first totals and only state=approved for the approved totals. Rows may appear in any order. ### Tables Table: `Transactions` ```text +---------------+---------+ | 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"]. ``` ### Constraints - In this version, every transaction has a non-NULL `trans_date`. ### Example ```text Transactions (id,country,state,amount,trans_date): (1,US,approved,20,2021-01-01), (2,US,declined,30,2021-01-02), (3,NULL,declined,40,2021-01-03), (4,US,approved,50,2021-02-01) Output (month,country,trans_count,approved_count,trans_total_amount,approved_total_amount): (2021-01,US,2,1,50,20), (2021-01,NULL,1,0,40,0), (2021-02,US,1,1,50,50) ``` ### What a Strong Answer Covers - Report all requested metrics per month and country. - Include all transactions in the overall totals and only approvals in the approved totals. ### Follow-up Questions - How would you include calendar months with no transactions? - How would you report the approval rate alongside the counts?