How To Solve LeetCode 1193

This problem begins with a table of transactions that has the following column names: id, country, state, amount, and trans_date. As explained, the id is the primary key, and the table contains information on incoming transactions. When looking at this, it is important to note that the state column is an enumeration that categorizes the transaction as approved or declined. We are required to return the result set in any order with specific aggregate columns, such as: month (year-month), trans_count (total transaction count per grouping), approved_count (count of approved transactions per grouping), trans_total_amount (sum of transaction amounts per grouping), and approved_total_amount (sum of approved transaction amounts per grouping).

Approach

My approach is to first identify the necessary grouping, which in this case is by the month-year of the transaction date and the country. All of the grouping can be achieved in a single GROUP BY query utilizing aggregate functions like COUNT() and SUM(). For some of the aggregate functions, it is necessary to use CASE statements to properly return the correct aggregation.

Aggregate Columns

To calculate the month and year, I convert the transaction date into a string using the TO_CHAR(, ) function in PostgreSQL. The string representation I used is ‘YYYY-MM’, and the date column is trans_date. This built-in function returns the string representation of the year and month. Furthermore, to get the total transaction count (trans_count), you simply use the COUNT() aggregate function and pass in the transaction id as an argument. The transaction total amount (trans_total_amount) is similar; all that is necessary is to pass the amount column into the SUM() function to get the sum for the grouped data.

For the approved count (approved_count), I used the aggregate SUM() function with a CASE statement inside as an argument. This works because the engine first evaluates the CASE statement and returns the result, and then applies the SUM function to the grouped data by the columns specified in the GROUP BY clause. Note that in the CASE statement, it is important to return 1 if the state is ‘approved’ and 0 otherwise, so that when the SUM function is applied, it will yield the count. The approved total amount (approved_total_amount) is similar to the approved count, with the only difference being what is returned in the CASE statement if the state is ‘approved’. The amount is returned in the CASE statement to get the sum of all transaction amounts per the grouping where the state is approved.

SELECT 
    TO_CHAR(trans_date, 'YYYY-MM') AS month,
    country                        AS country,
    COUNT(id)                      AS trans_count,
    SUM(
        CASE
            WHEN "state" = 'approved' THEN 1
            ELSE 0 
        END
    )                              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 TO_CHAR(trans_date, 'YYYY-MM'), country