Fintech SQL Analysis.sql

CREATE DATABASE FINTECH_ANALYTICS;
USE FINTECH_ANALYTICS;

CREATE TABLE CUSTOMERS
(
    CUSTOMER_ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    EMAIL VARCHAR(100),
    CITY VARCHAR(50),
    CREDIT_SCORE INT,
    REFERRAL_CODE VARCHAR(20)
);

CREATE TABLE ACCOUNTS
(
    ACCOUNT_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ACCOUNT_TYPE VARCHAR(20),
    BALANCE DECIMAL(12,2),
    ACCOUNT_STATUS VARCHAR(20),
    OPEN_DATE DATE,

    FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMERS(CUSTOMER_ID)
);

CREATE TABLE LOANS
(
    LOAN_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    LOAN_AMOUNT DECIMAL(12,2),
    INTEREST_RATE DECIMAL(5,2),
    LOAN_STATUS VARCHAR(20),

    FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMERS(CUSTOMER_ID)
);

CREATE TABLE TRANSACTIONS
(
    TRANSACTION_ID INT PRIMARY KEY,
    ACCOUNT_ID INT,
    TRANSACTION_DATE DATE,
    TRANSACTION_TYPE VARCHAR(20),
    AMOUNT DECIMAL(12,2),

    FOREIGN KEY (ACCOUNT_ID)
    REFERENCES ACCOUNTS(ACCOUNT_ID)
);

CREATE TABLE SUPPORT_TICKETS
(
    TICKET_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ISSUE_TYPE VARCHAR(50),
    PRIORITY VARCHAR(20),
    RESOLUTION_HOURS INT,

    FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMERS(CUSTOMER_ID)
);

INSERT INTO CUSTOMERS VALUES
(1,'Rahul','Sharma','rahul@gmail.com','Mumbai',760,'REF101'),
(2,'Priya','Patel',NULL,'Ahmedabad',810,NULL),
(3,'Amit','Verma','amit@gmail.com','Delhi',680,'REF102'),
(4,'Sneha','Joshi',NULL,'Pune',720,NULL),
(5,'Rohan','Mehta','rohan@gmail.com','Bangalore',790,'REF103'),
(6,'Karan','Singh',NULL,'Mumbai',640,NULL),
(7,'Neha','Kapoor','neha@gmail.com','Delhi',830,'REF104'),
(8,'Arjun','Rao',NULL,'Hyderabad',710,NULL);

INSERT INTO ACCOUNTS VALUES
(101,1,'Savings',125000,'Active','2022-01-15'),
(102,2,'Current',220000,'Active','2021-05-10'),
(103,3,'Savings',80000,'Dormant','2023-03-22'),
(104,4,'Current',45000,'Active','2022-11-02'),
(105,5,'Savings',310000,'Active','2020-08-19'),
(106,6,'Savings',60000,'Closed','2021-09-01'),
(107,7,'Current',500000,'Active','2019-04-14'),
(108,8,'Savings',150000,'Active','2022-06-25');

INSERT INTO LOANS VALUES
(201,1,500000,8.5,'Approved'),
(202,2,250000,9.2,'Approved'),
(203,3,1000000,10.5,'Rejected'),
(204,5,750000,8.0,'Approved'),
(205,7,300000,7.5,'Approved');

INSERT INTO TRANSACTIONS VALUES
(301,101,'2025-01-01','Credit',15000),
(302,101,'2025-01-15','Debit',5000),
(303,102,'2025-01-10','Credit',25000),
(304,103,'2025-02-01','Debit',12000),
(305,104,'2025-02-15','Credit',18000),
(306,105,'2025-03-01','Credit',45000),
(307,106,'2025-03-10','Debit',10000),
(308,107,'2025-04-05','Credit',60000),
(309,108,'2025-04-12','Debit',7000);

INSERT INTO SUPPORT_TICKETS VALUES
(401,1,'Login Issue','Low',12),
(402,2,'Loan Query','High',48),
(403,3,'Account Freeze','High',72),
(404,4,'KYC Update','Medium',24),
(405,5,'Card Issue','Low',8),
(406,7,'Loan Query','High',36);

-- Show customer full names.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME FROM CUSTOMERS;

-- Show customer full names in uppercase.
SELECT UPPER(CONCAT(FIRST_NAME," ",LAST_NAME)) AS FULL_NAME FROM CUSTOMERS;

-- Show the first three characters of each city.
SELECT DISTINCT CITY, LEFT(CITY,3) FROM CUSTOMERS;

-- Show customer initials. Example: Rahul Sharma -> RS.
SELECT FIRST_NAME, LAST_NAME, CONCAT(LEFT(FIRST_NAME,1),LEFT(LAST_NAME,1)) AS INITIALS FROM CUSTOMERS;

-- Show email usernames only. Example: rahul@gmail.com -> rahul.
SELECT SUBSTRING_INDEX(EMAIL,'@',1) AS EMAIL_USERNAME FROM CUSTOMERS;

-- Show customer names and email values. If email is null, show NO EMAIL.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, EMAIL, COALESCE(NULLIF(EMAIL,''),"NO EMAIL") AS EMAIL_STATUS FROM CUSTOMERS;

-- Show customer names and referral codes. If referral code is null, show NOT_REFERRED.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, COALESCE(NULLIF(REFERRAL_CODE,''),"NOT_REFERRED") AS REFERRAL_STATUS FROM CUSTOMERS;

-- Find customers whose email is null.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, EMAIL FROM CUSTOMERS 
WHERE EMAIL IS NULL;

-- Find customers whose referral code is not null.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, REFERRAL_CODE FROM CUSTOMERS
WHERE REFERRAL_CODE IS NOT NULL;

-- Show email and referral contact details, replacing missing values with UNKNOWN.
SELECT
    EMAIL,
    REFERRAL_CODE,
    COALESCE(NULLIF(REFERRAL_CODE,''),"UNKNOWN") AS REFERRAL_STATUS,
    COALESCE(NULLIF(EMAIL,''),"UNKNOWN") AS EMAIL_STATUS
FROM CUSTOMERS;

-- Classify customers by credit score.
SELECT *,
CASE 
    WHEN CREDIT_SCORE >= 800 THEN "EXCELLENT"
    WHEN CREDIT_SCORE BETWEEN 750 AND 799 THEN "GOOD"
    WHEN CREDIT_SCORE BETWEEN 700 AND 749 THEN "FAIR"
    WHEN CREDIT_SCORE < 700 THEN "POOR"
END AS CREDIT_SCORE_BUCKET
FROM CUSTOMERS;

-- Classify accounts by balance.
SELECT *,
CASE
    WHEN BALANCE >= 300000 THEN "VIP"
    WHEN BALANCE BETWEEN 100000 AND 299999 THEN "PREMIUM"
    WHEN BALANCE < 100000 THEN "REGULAR"
END AS BALANCE_SEGMENT
FROM ACCOUNTS;

-- Classify loans by loan amount.
SELECT *,
CASE
    WHEN LOAN_AMOUNT >= 750000 THEN "HIGH VALUE"
    WHEN LOAN_AMOUNT BETWEEN 300000 AND 749999 THEN "MEDIUM VALUE"
    WHEN LOAN_AMOUNT < 300000 THEN "LOW VALUE"
END AS LOAN_BUCKET
FROM LOANS;

-- Show customer name, account status, and account status category.
SELECT  CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, ACCOUNT_STATUS,
CASE
    WHEN ACCOUNT_STATUS = "Active" THEN "OPERATING"
    WHEN ACCOUNT_STATUS = "Dormant" THEN "INACTIVE"
    WHEN ACCOUNT_STATUS = "Closed" THEN "CLOSED"
END AS ACCOUNT_STATUS_CATEGORY
FROM ACCOUNTS AS A
INNER JOIN CUSTOMERS AS C ON A.CUSTOMER_ID = C.CUSTOMER_ID;

-- Show customer full names and city.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, CITY FROM CUSTOMERS;

-- Find customers whose credit score is greater than 750.
SELECT * FROM CUSTOMERS
WHERE CREDIT_SCORE > 750;

-- Show account type and balance.
SELECT ACCOUNT_TYPE, BALANCE FROM ACCOUNTS;

-- Count customers city-wise.
SELECT CITY, COUNT(*) AS TOTAL_CUSTOMERS FROM CUSTOMERS
GROUP BY CITY
ORDER BY TOTAL_CUSTOMERS DESC;

-- Find the highest account balance.
SELECT MAX(BALANCE) AS HIGHEST_BALANCE FROM ACCOUNTS;

-- Show customer name, account type, and balance using a join.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, ACCOUNT_TYPE, BALANCE FROM CUSTOMERS AS C
INNER JOIN ACCOUNTS AS A ON A.CUSTOMER_ID = C.CUSTOMER_ID;

-- Find customers whose balance is above the average account balance.
SELECT *, CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME FROM CUSTOMERS AS C
INNER JOIN ACCOUNTS AS A ON A.CUSTOMER_ID = C.CUSTOMER_ID
WHERE BALANCE > (SELECT AVG(BALANCE) FROM ACCOUNTS);

-- Rank customers based on account balance.
SELECT *, RANK() OVER(ORDER BY BALANCE DESC) AS BALANCE_RANK FROM ACCOUNTS; 

-- Classify customers by credit score using CASE.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, CREDIT_SCORE,
CASE
    WHEN CREDIT_SCORE >= 800 THEN "EXCELLENT"
    WHEN CREDIT_SCORE BETWEEN 700 AND 799 THEN "GOOD"
    WHEN CREDIT_SCORE < 700 THEN "NEEDS IMPROVEMENT"
END AS CREDIT_SCORE_BUCKET
FROM CUSTOMERS;

-- Show customer name and email. If email is null, show NO EMAIL.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, EMAIL, COALESCE(NULLIF(EMAIL,''),"NO EMAIL") AS EMAIL_STATUS FROM CUSTOMERS;

-- Show customer full names in uppercase.
SELECT UPPER(CONCAT(FIRST_NAME," ",LAST_NAME)) AS FULL_NAME FROM CUSTOMERS;

-- Show customer initials. Example: Rahul Sharma -> RS.
SELECT CONCAT(LEFT(FIRST_NAME,1),LEFT(LAST_NAME,1)) AS CUSTOMER_INITIALS FROM CUSTOMERS;

-- Extract only the username from email. Example: rahul@gmail.com -> rahul.
SELECT SUBSTRING_INDEX(EMAIL,'@',1) AS USERNAME FROM CUSTOMERS;

-- Show the first three characters of each city.
SELECT DISTINCT LEFT(CITY,3) AS CITY_PREFIX FROM CUSTOMERS;

-- Show customer name and city in one field.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME," ","-"," ",CITY) AS CUSTOMER_NAME_CITY FROM CUSTOMERS;

-- Find customers whose balance is above their account type average balance.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME, BALANCE, ACCOUNT_TYPE FROM ACCOUNTS AS A
INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = A.CUSTOMER_ID
WHERE BALANCE > (SELECT AVG(BALANCE) FROM ACCOUNTS WHERE ACCOUNT_TYPE = A.ACCOUNT_TYPE);

-- Find the top two customers by balance within each account type.
WITH TOP_CUSTOMERS AS (
    SELECT
        CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME,
        BALANCE,
        ACCOUNT_TYPE,
        DENSE_RANK() OVER(PARTITION BY ACCOUNT_TYPE ORDER BY BALANCE DESC) AS BALANCE_RANK
    FROM ACCOUNTS AS A
    INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = A.CUSTOMER_ID
)
SELECT FULL_NAME, BALANCE, ACCOUNT_TYPE, BALANCE_RANK FROM TOP_CUSTOMERS
WHERE BALANCE_RANK <= 2;

-- Show customer name, balance, account type average balance, and difference from average.
SELECT
    CONCAT(FIRST_NAME," ",LAST_NAME) AS FULL_NAME,
    BALANCE,
    ACCOUNT_TYPE,
    AVG(BALANCE) OVER(PARTITION BY ACCOUNT_TYPE) AS ACCOUNT_TYPE_AVERAGE_BALANCE,
    BALANCE - AVG(BALANCE) OVER(PARTITION BY ACCOUNT_TYPE) AS DIFFERENCE_FROM_AVERAGE
FROM ACCOUNTS AS A
INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = A.CUSTOMER_ID;

-- Show running total of transaction amounts ordered by transaction date.
SELECT TRANSACTION_DATE, AMOUNT, SUM(AMOUNT) OVER(ORDER BY TRANSACTION_DATE ASC) AS RUNNING_TOTAL FROM TRANSACTIONS;

-- Find customers having loans greater than the overall average loan amount.
SELECT * FROM LOANS AS L
INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = L.CUSTOMER_ID
WHERE LOAN_AMOUNT > (SELECT AVG(LOAN_AMOUNT) FROM LOANS);

-- Find customers having loans greater than the overall average loan amount. Show customer name and loan amount.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, LOAN_AMOUNT FROM CUSTOMERS AS C
INNER JOIN LOANS AS L ON C.CUSTOMER_ID = L.CUSTOMER_ID
WHERE LOAN_AMOUNT > (SELECT AVG(LOAN_AMOUNT) FROM LOANS);

-- Find accounts whose balance is greater than the average balance of their own account type.
SELECT ACCOUNT_ID, ACCOUNT_TYPE, BALANCE FROM ACCOUNTS AS A
WHERE BALANCE > (SELECT AVG(BALANCE) FROM ACCOUNTS WHERE ACCOUNT_TYPE = A.ACCOUNT_TYPE);

-- Find customers having the highest account balance within their account type.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, ACCOUNT_TYPE, BALANCE FROM ACCOUNTS AS A
INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = A.CUSTOMER_ID
WHERE BALANCE = (SELECT MAX(BALANCE) FROM ACCOUNTS WHERE ACCOUNT_TYPE = A.ACCOUNT_TYPE);

-- Find customers who have taken at least one loan.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY FROM CUSTOMERS AS C
WHERE C.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM LOANS);

-- Find customers who have never taken a loan.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY FROM CUSTOMERS AS C
WHERE CUSTOMER_ID NOT IN (SELECT CUSTOMER_ID FROM LOANS);

-- Find customers whose credit score is above the average credit score of customers in the same city.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY, CREDIT_SCORE FROM CUSTOMERS AS C
WHERE CREDIT_SCORE >= (SELECT AVG(CREDIT_SCORE) FROM CUSTOMERS WHERE CITY = C.CITY);

-- Find accounts having the lowest balance within their account type.
SELECT ACCOUNT_ID, ACCOUNT_TYPE, BALANCE FROM ACCOUNTS AS A
WHERE BALANCE = (SELECT MIN(BALANCE) FROM ACCOUNTS WHERE ACCOUNT_TYPE = A.ACCOUNT_TYPE);

-- Find customers whose loan amount is greater than the average loan amount of approved loans only.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, LOAN_AMOUNT FROM LOANS AS L
INNER JOIN CUSTOMERS AS C ON C.CUSTOMER_ID = L.CUSTOMER_ID
WHERE LOAN_AMOUNT > (SELECT AVG(LOAN_AMOUNT) FROM LOANS WHERE LOAN_STATUS = "Approved");

-- Find customers who have raised at least one support ticket.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY FROM CUSTOMERS AS C
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM SUPPORT_TICKETS);

-- Find customers who have never raised any support ticket.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY FROM CUSTOMERS AS C
WHERE CUSTOMER_ID NOT IN (SELECT CUSTOMER_ID FROM SUPPORT_TICKETS);

-- Find customers whose credit score equals the highest credit score in their city.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY, CREDIT_SCORE FROM CUSTOMERS AS C
WHERE CREDIT_SCORE = (SELECT MAX(CREDIT_SCORE) FROM CUSTOMERS WHERE CITY = C.CITY);

-- Find loans whose interest rate is below the overall average interest rate.
SELECT LOAN_ID, INTEREST_RATE FROM LOANS AS L 
WHERE INTEREST_RATE < (SELECT AVG(INTEREST_RATE) FROM LOANS);

-- Find customers who own accounts with balances greater than the highest balance among dormant accounts.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, BALANCE FROM CUSTOMERS AS C
INNER JOIN ACCOUNTS AS A ON C.CUSTOMER_ID = A.CUSTOMER_ID
WHERE BALANCE > (SELECT MAX(BALANCE) FROM ACCOUNTS WHERE ACCOUNT_STATUS = 'Dormant');

-- Find account types whose average balance is greater than the overall average account balance.
SELECT ACCOUNT_TYPE, AVG(BALANCE) AS AVERAGE_BALANCE FROM ACCOUNTS
GROUP BY ACCOUNT_TYPE
HAVING AVG(BALANCE) > (SELECT AVG(BALANCE) FROM ACCOUNTS);

-- Find customers whose account balance is greater than the average balance of customers in the same city.
SELECT CONCAT(FIRST_NAME," ",LAST_NAME) AS CUSTOMER_NAME, CITY, BALANCE FROM CUSTOMERS AS C
INNER JOIN ACCOUNTS AS A ON C.CUSTOMER_ID = A.CUSTOMER_ID
WHERE BALANCE > (
    SELECT AVG(A2.BALANCE)
    FROM ACCOUNTS A2
    INNER JOIN CUSTOMERS C2 ON C2.CUSTOMER_ID = A2.CUSTOMER_ID
    WHERE C2.CITY = C.CITY
);