8 Week SQL Challenge - Case Study 4 (Customer Transactions)

Moving on to focus area: B. Customer Transactions which is all about the customers and the transactions they have performed with Data Bank

What is the unique count and total amount for each transaction type?

SELECT 
    txn_type,
    COUNT(txn_type) AS total_count,
    SUM(txn_amount) AS total_amount
FROM customer_transactions
GROUP BY txn_type;

What is the average total historical deposit counts and amounts for all customers?

WITH sum_count_per_deposit_customer AS (
    SELECT
        COUNT(txn_type) AS deposit_count,
        SUM(txn_amount) AS deposit_amount
    FROM customer_transactions
    WHERE txn_type = 'deposit'
    GROUP BY customer_id
)
SELECT
    ROUND(AVG(deposit_count), 2) AS avg_deposit_count,
    ROUND(AVG(deposit_amount), 2) AS avg_deposit_amount
FROM sum_count_per_deposit_customer;

For each month - how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?

WITH txn_monthly_summary AS (
    SELECT 
        customer_id,
        MONTH(txn_date) AS txn_month,
        SUM(CASE WHEN txn_type = 'deposit' THEN 1 ELSE 0 END) AS deposit_count,
        SUM(CASE WHEN txn_type = 'purchase' THEN 1 ELSE 0 END) AS purchase_count,
        SUM(CASE WHEN txn_type = 'withdrawal' THEN 1 ELSE 0 END) AS withdrawal_count
    FROM customer_transactions
    GROUP BY customer_id, MONTH(txn_date)
)

SELECT 
    txn_month,
    COUNT(customer_id) AS total_customers
FROM txn_monthly_summary
WHERE deposit_count > 1 AND (purchase_count >= 1 OR withdrawal_count >= 1)
GROUP BY txn_month
ORDER BY txn_month;

What is the closing balance for each customer at the end of the month?

WITH monthly_txn_summary AS (
    SELECT
        customer_id,
        MONTH(txn_date) AS txn_month,
        SUM(CASE WHEN txn_type = 'deposit' THEN txn_amount ELSE -txn_amount END) AS net_amount
    FROM customer_transactions
    GROUP BY customer_id, MONTH(txn_date)
)

SELECT
    customer_id,
    txn_month,
    SUM(net_amount) OVER (PARTITION BY customer_id ORDER BY txn_month) AS closing_balance
FROM monthly_txn_summary
GROUP BY customer_id, txn_month
ORDER BY customer_id;