How To Solve LeetCode 1193

Source: Dev.to

Problem Description

The table Transactions has the following columns:

• id (primary key)
• country
• state (enumeration: 'approved' or 'declined')
• amount
• trans_date

We need to return, for each month‑year and country, the following aggregates (order does not matter):

• month – formatted as YYYY-MM
• trans_count – total number of transactions
• approved_count – number of approved transactions
• trans_total_amount – sum of all transaction amounts
• approved_total_amount – sum of amounts for approved transactions

Approach

1. Grouping – Group rows by the month‑year derived from trans_date and by country.
2. Aggregations –
   • COUNT(id) gives the total transaction count.
   • SUM(amount) gives the total transaction amount.
   • For the approved‑only metrics, use CASE expressions inside SUM:
     • Return 1 (or amount) when state = 'approved', otherwise 0.
3. Month formatting – In PostgreSQL, TO_CHAR(trans_date, 'YYYY-MM') converts the date to the required YYYY-MM string.

SQL Query

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;