1193. Monthly Transactions I / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

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.

 

 

 

 

제출답안(MS SQL Server)

WITH
    trxs
        AS(
            SELECT 
                id, country, amount, trans_date,
                FORMAT(trans_date, 'yyyy-MM') AS year_month,
                CASE 
                    WHEN state LIKE 'approved' THEN 1
                    ELSE 0 END is_approved,
                CASE
                    WHEN state LIKE 'approved' THEN amount
                    ELSE 0 END amount_approved
            FROM transactions)

SELECT
    year_month AS month, 
    country, 
    COUNT(id) AS trans_count,
    SUM(is_approved) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(amount_approved) AS approved_total_amount
FROM 
    trxs
GROUP BY 
    year_month, country

 

 

문제 바로가기(MS SQL Server)