financial_transaction_analysis.sql

CREATE DATABASE FINANCE;

USE FINANCE;

--- DATA SANITY CHECK

SELECT COUNT(*) AS TOTAL_TRANSACTIONS
FROM financial_transactions;

SELECT TOP 5 *
FROM financial_transactions;

--- EXECUTIVE kpi SNAPSHOT

SELECT
	SUM(CASE
			WHEN TRANSACTION_TYPE = 'CREDIT'
				AND TRANSACTION_STATUS = 'SUCCESS' THEN AMOUNT ELSE 0 END) AS TOTAL_REVENUE,
	SUM(CASE
			WHEN TRANSACTION_TYPE = 'DEBIT'
				AND TRANSACTION_STATUS = 'SUCCESS' THEN AMOUNT ELSE 0 END) AS TOTAL_SPEND,
	COUNT(CASE WHEN TRANSACTION_STATUS = 'FAILED' THEN 1 END) * 100.0 / COUNT(*) AS FAILED_TRANSACTION_PCT
FROM financial_transactions;

--- REVENUE LEAKAGE ANALYSIS

SELECT CHANNEL, 
COUNT(*) AS FAILED_TRANSACTIONS,
SUM(AMOUNT) AS FAILED_AMOUNT
FROM financial_transactions
WHERE transaction_status = 'FAILED'
	--AND transaction_type = 'DEBIT'
GROUP BY channel
ORDER BY FAILED_AMOUNT DESC;

--- FRAUD RISK CONCENTRATION

SELECT CUSTOMER_ID,
COUNT(*) AS FRAUD_COUNT,
SUM(AMOUNT) AS FRAUD_AMOUNT
FROM financial_transactions
WHERE fraud_flag = 1
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY FRAUD_AMOUNT DESC;

--- COUNTRY-WISE FINANCIAL EXPOSURE

SELECT COUNTRY,
SUM(CASE
		WHEN TRANSACTION_TYPE = 'CREDIT' THEN AMOUNT ELSE 0 END) AS REVENUE,
COUNT(CASE
		WHEN TRANSACTION_STATUS = 'FAILED' THEN 1 END) AS FAILED_TRANSACTION
FROM financial_transactions
GROUP BY country
ORDER BY FAILED_TRANSACTION DESC;

--- TOP 10 CUSTOMERS BY NET CONTRIBUTION

SELECT TOP 10 CUSTOMER_ID,
SUM(CASE
		WHEN TRANSACTION_TYPE = 'CREDIT' THEN AMOUNT ELSE 0 END) -
SUM(CASE
		WHEN TRANSACTION_TYPE = 'DEBIT' THEN AMOUNT ELSE 0 END) AS NET_VALUE
FROM financial_transactions
WHERE transaction_status = 'SUCCESS'
GROUP BY customer_id
ORDER BY NET_VALUE DESC;

--- MONTHLY REVENUE TREND

SELECT YEAR(TRANSACTION_DATE) AS YEAR,
MONTH(TRANSACTION_DATE) AS MONTH,
SUM(AMOUNT) AS MONTHLY_REVENUE
FROM financial_transactions
WHERE transaction_status = 'SUCCESS'
	AND transaction_type = 'CREDIT'
GROUP BY YEAR(TRANSACTION_DATE), MONTH(TRANSACTION_DATE)
ORDER BY YEAR, MONTH;