Essential SQL Project with Top 9 Queries for Financial Analysis

Introduction to SQL Project

In the financial industry, data analysis is crucial for understanding customer behavior, identifying patterns, and making strategic business decisions. This SQL project explores key transactional metrics using advanced SQL queries to analyze data and provide actionable insights. Through this case study, AB Bank will gain an in-depth view of their customers’ spending behaviors, trends, and potential growth areas. Each query is designed to address specific aspects of financial data analysis, from tracking transaction totals to identifying customer segments and high-value regions.For a detailed guide and resources, visit our SQL Guide.

Defining the Research Problem for SQL Financial Data Analysis

The purpose of this case study is to provide AB Bank with a structured framework to analyze its customers’ financial transactions. The objective is to identify trends, understand regional dynamics, categorize customers based on their spending, and pinpoint high-value opportunities. By analyzing these factors, AB Bank can make data-driven decisions to better serve its customers and optimize its resources.

SQL Project Objectives

1. Track Spending Patterns by Customer – Discover total and cumulative spending trends.
2. Analyze Transaction Frequency – Evaluate how transaction counts vary by day and region.
3. Calculate Seasonal Trends – Use rolling averages and growth rates to understand long-term patterns.
4. Segment Customer Spending – Categorize customers based on transaction amounts.
5. Assess Regional Performance – Track growth rates across different regions.

Data Creation for the SQL Project

1. Country Table

SQL Code:

CREATE TABLE Country (
    id INT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    country_name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL
);

INSERT INTO Country (id, country_code, country_name, region) VALUES
(1, 'US', 'United States', 'North America'),
(2, 'CA', 'Canada', 'North America'),
(3, 'FR', 'France', 'Europe'),
(4, 'JP', 'Japan', 'Asia'),
(5, 'AU', 'Australia', 'Oceania'),
(6, 'BR', 'Brazil', 'South America'),
(7, 'IN', 'India', 'Asia'),
(8, 'ZA', 'South Africa', 'Africa'),
(9, 'DE', 'Germany', 'Europe'),
(10, 'MX', 'Mexico', 'North America');

Output Table:

id	country_code	country_name	region
1	US	United States	North America
2	CA	Canada	North America
3	FR	France	Europe
4	JP	Japan	Asia
5	AU	Australia	Oceania
6	BR	Brazil	South America
7	IN	India	Asia
8	ZA	South Africa	Africa
9	DE	Germany	Europe
10	MX	Mexico	North America

Attributes:

• id: Unique identifier for each country, serving as the primary key.
• country_code: Two-character ISO code representing the country (e.g., ‘US’, ‘IN’), ensuring standardization.
• country_name: Full name of the country (e.g., ‘United States’, ‘India’).
• region: Geographical region of the country (e.g., ‘Asia’, ‘Europe’).

2. Card_Type Table

SQL Code:

CREATE TABLE Card_Type (
    id INT PRIMARY KEY,
    card_type_name VARCHAR(50) NOT NULL
);

INSERT INTO Card_Type (id, card_type_name) VALUES
(1, 'Visa'),
(2, 'MasterCard'),
(3, 'American Express'),
(4, 'Discover'),
(5, 'Diners Club'),
(6, 'JCB'),
(7, 'UnionPay'),
(8, 'Maestro'),
(9, 'RuPay'),
(10, 'Elo');

Output Table:

id	card_type_name
1	Visa
2	MasterCard
3	American Express
4	Discover
5	Diners Club
6	JCB
7	UnionPay
8	Maestro
9	RuPay
10	Elo

Attributes:

• id: Unique identifier for each card type, serving as the primary key.
• card_type_name: Name of the card type (e.g., ‘Visa’, ‘MasterCard’), ensuring identification of various card types.

3. Customer Table

SQL Code:

CREATE TABLE Customer (
    id INT PRIMARY KEY,
    NIN VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    country_id INT,
    account_status ENUM('Active', 'Inactive', 'Suspended') NOT NULL,
    total_credit DECIMAL(10, 2) DEFAULT 0.00,
    FOREIGN KEY (country_id) REFERENCES Country(id)
);

INSERT INTO Customer (id, NIN, first_name, last_name, country_id, account_status, total_credit) VALUES
(1, 'NIN12345', 'Alice', 'Johnson', 1, 'Active', 1500.00),
(2, 'NIN23456', 'Bob', 'Smith', 2, 'Inactive', 500.00),
(3, 'NIN34567', 'Charlie', 'Davis', 3, 'Suspended', 1000.00),
(4, 'NIN45678', 'Dana', 'Lee', 4, 'Active', 2000.00),
(5, 'NIN56789', 'Eve', 'Martinez', 5, 'Active', 750.00);

Output Table:

id	NIN	first_name	last_name	country_id	account_status	total_credit
1	NIN12345	Alice	Johnson	1	Active	1500.00
2	NIN23456	Bob	Smith	2	Inactive	500.00
3	NIN34567	Charlie	Davis	3	Suspended	1000.00
4	NIN45678	Dana	Lee	4	Active	2000.00
5	NIN56789	Eve	Martinez	5	Active	750.00

Attributes:

• id: Unique identifier for each customer, serving as the primary key.
• NIN: National Identification Number for the customer; unique and cannot be null.
• first_name: First name of the customer; cannot be null.
• last_name: Last name of the customer; cannot be null.
• country_id: Foreign key linking to the Country table, identifying the customer’s country.
• account_status: Status of the customer’s account (Active, Inactive, or Suspended), ensuring predefined status values.
• total_credit: Total credit associated with the customer, defaulting to 0.00 if not specified.

Relationships:

• The country_id column establishes a foreign key relationship with the id column in the Country table.

4. Card Table

SQL Code:

CREATE TABLE Card (
    id INT PRIMARY KEY,
    card_number VARCHAR(20) UNIQUE NOT NULL,
    customer_id INT,
    card_type_id INT,
    card_status ENUM('Active', 'Blocked', 'Expired') NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customer(id),
    FOREIGN KEY (card_type_id) REFERENCES Card_Type(id)
);

INSERT INTO Card (id, card_number, customer_id, card_type_id, card_status) VALUES
(1, '**** **** **** 1234', 1, 1, 'Active'),
(2, '**** **** **** 2345', 2, 2, 'Blocked'),
(3, '**** **** **** 3456', 3, 3, 'Expired'),
(4, '**** **** **** 4567', 4, 4, 'Active'),
(5, '**** **** **** 5678', 5, 5, 'Blocked');

Output Table:

id	card_number	customer_id	card_type_id	card_status
1	**** **** **** 1234	1	1	Active
2	**** **** **** 2345	2	2	Blocked
3	**** **** **** 3456	3	3	Expired
4	**** **** **** 4567	4	4	Active
5	**** **** **** 5678	5	5	Blocked

Attributes:

• id: Unique identifier for each card, serving as the primary key.
• card_number: Unique card number for each card; cannot be null and ensures security with obfuscated format.
• customer_id: Foreign key linking to the Customer table, identifying the cardholder.
• card_type_id: Foreign key linking to the Card_Type table, specifying the type of card (e.g., Visa, MasterCard).
• card_status: Status of the card (Active, Blocked, or Expired), ensuring predefined status values.

Relationships:

• customer_id: References the id column in the Customer table, linking each card to a specific customer.
• card_type_id: References the id column in the Card_Type table, linking each card to its type.

5. Card_Transaction Table

SQL Code:

CREATE TABLE Card_Transaction (
    id INT PRIMARY KEY,
    date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    location VARCHAR(100) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    card_id INT,
    FOREIGN KEY (card_id) REFERENCES Card(id)
);

INSERT INTO Card_Transaction (id, date, amount, location, currency, card_id) VALUES
(1, '2024-01-01', 100.50, 'New York, USA', 'USD', 1),
(2, '2024-01-05', 200.75, 'Toronto, Canada', 'CAD', 2),
(3, '2024-01-10', 150.00, 'Paris, France', 'EUR', 3),
(4, '2024-01-15', 80.25, 'Tokyo, Japan', 'JPY', 4),
(5, '2024-01-20', 120.00, 'Sydney, Australia', 'AUD', 5);

Output Table:

id	date	amount	location	currency	card_id
1	2024-01-01	100.50	New York, USA	USD	1
2	2024-01-05	200.75	Toronto, Canada	CAD	2
3	2024-01-10	150.00	Paris, France	EUR	3
4	2024-01-15	80.25	Tokyo, Japan	JPY	4
5	2024-01-20	120.00	Sydney, Australia	AUD	5

Attributes:

• id: Unique identifier for each transaction, serving as the primary key.
• date: Date of the transaction; cannot be null and records when the transaction occurred.
• amount: Monetary value of the transaction, stored with two decimal places; cannot be null.
• location: Location where the transaction took place; cannot be null and allows up to 100 characters.
• currency: Currency code for the transaction (e.g., ‘USD’, ‘CAD’); cannot be null and follows the ISO 4217 standard with a length of 3 characters.
• card_id: Foreign key linking to the Card table, identifying the card used for the transaction.

Relationships:

• card_id: References the id column in the Card table, linking each transaction to a specific card.

Data Analysis in the SQL Project: SQL Case Study Questions

1. Total Spend by Quarter and Customer

Question: What is the total amount spent by each customer, aggregated by quarter and year, to observe spending patterns over time?

SELECT customer_id, 
       DATE_TRUNC('quarter', date) AS quarter,
       SUM(amount) AS total_spend
FROM Card_Transaction
GROUP BY customer_id, DATE_TRUNC('quarter', date)
ORDER BY customer_id, quarter;

Output Table:

customer_id	quarter	total_spend
1	2024-Q1	200.50
2	2024-Q1	500.75

Explanation: This query groups transaction data by customer and quarter, calculating the total spend using SUM and DATE_TRUNC.

Finding: By reviewing quarterly spending patterns, we can identify peak and off-peak spending periods, which will assist AB Bank in seasonal budgeting and forecasting.

2. Calculate Running Totals of Transactions

Question: How can cumulative transaction amounts be calculated over each month to track progressive spending?

SELECT date,
       SUM(amount) OVER (ORDER BY date) AS cumulative_spending
FROM Card_Transaction
ORDER BY date;

Output Table:

date	cumulative_spending
2024-01-01	100.50
2024-01-05	301.25

Explanation: This query uses a window function to calculate a running total, allowing AB Bank to track overall spending patterns over time.

Finding: This cumulative total shows monthly spending progress, helpful for understanding general spending trends and whether there are steady increases or fluctuations.

3. Calculate Moving Average of Transaction Values

Question: What is the moving average of transaction amounts over a rolling 3-month period to understand trends and seasonality?

SELECT date,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM Card_Transaction
ORDER BY date;

Output Table:

date	moving_avg
2024-01-01	100.50
2024-01-05	150.63

Explanation: This moving average query smoothens transaction data, making it easier to spot gradual changes over time without day-to-day noise.

Finding: A rolling average reveals seasonal trends and helps AB Bank forecast customer spending behavior over time.

4. Time Series Analysis by Day

Question: How does daily transaction value change for high-value customers or specific regions, providing insight into short-term financial trends?

SELECT date, SUM(amount) AS daily_total
FROM Card_Transaction
GROUP BY date
ORDER BY date;

Output Table:

date	daily_total
2024-01-01	100.50
2024-01-05	200.75

Explanation: By grouping transactions by date, AB Bank can observe daily changes in transaction value across different customer types or regions.

Finding: Daily transaction data provides insight into short-term fluctuations in spending, highlighting potential market changes or external impacts on customer activity.

Time series analysis involves examining data points collected or recorded at specific time intervals to identify trends, patterns, and seasonality. It is crucial for businesses like AB Bank to monitor daily, monthly, or yearly changes in transaction values. This enables decision-makers to forecast trends, detect anomalies, and adapt strategies for optimal performance. Using SQL, time series analysis becomes efficient and scalable, offering actionable insights from financial datasets. For more details, check out How to Conduct Time Series Forecasting with SQL .

5. Transaction Frequency by Day of the Week

Question: How frequently do transactions occur on each day of the week, and which days see the highest volumes?

SELECT EXTRACT(DOW FROM date) AS day_of_week,
       COUNT(*) AS transaction_count
FROM Card_Transaction
GROUP BY day_of_week
ORDER BY transaction_count DESC;

Output Table:

day_of_week	transaction_count
1	150
5	120

Explanation: This query reveals transaction frequency by day of the week using the EXTRACT function to assess activity levels.

Finding: Identifying high-traffic days helps AB Bank allocate resources efficiently and plan for busier transaction days.

6. Monthly Transaction Growth Rate by Country

Question: What is the monthly growth rate in transaction volume and amount for each country to track regional growth and decline?

SELECT country_id,
       date_trunc('month', date) AS month,
       amount,
       LAG(amount, 1) OVER (PARTITION BY country_id ORDER BY month) AS previous_month,
       (amount - LAG(amount, 1) OVER (PARTITION BY country_id ORDER BY month)) / LAG(amount, 1) OVER (PARTITION BY country_id ORDER BY month) * 100 AS growth_rate
FROM Card_Transaction
JOIN Customer ON Card_Transaction.customer_id = Customer.id
ORDER BY country_id, month;

Output Table:

country_id	month	amount	previous_month	growth_rate
1	2024-01	500	NULL	NULL
1	2024-02	600	500	20%

Explanation: Using LAG, this query calculates the growth rate by comparing each month’s amount to the previous month.

Finding: Monthly growth rates reveal which regions have rising or declining transaction volumes, aiding AB Bank in assessing market trends.

7. Top Merchant Locations by Spending

Question: Which merchant locations have the highest transaction amounts, and how are they distributed across regions or card types?

SELECT location, 
       SUM(amount) AS total_spend
FROM Card_Transaction
GROUP BY location
ORDER BY total_spend DESC;

Output Table:

location	total_spend
New York, USA	5000.00
Tokyo, Japan	4500.00

Explanation: Grouping by location provides insight into high-activity areas based on transaction amounts.

Finding: This helps AB Bank recognize top merchant locations, potentially focusing promotional efforts or partnerships in high-traffic areas.

8. Top Merchant Locations by Spending

Question: What is the total annual revenue, grouped by high-value, medium-value, and low-value customers based on spending, to classify customer segments?

SELECT customer_id, 
       SUM(amount) AS annual_revenue,
       CASE 
           WHEN SUM(amount) > 1000 THEN 'High-Value'
           WHEN SUM(amount) BETWEEN 500 AND 1000 THEN 'Medium-Value'
           ELSE 'Low-Value'
       END AS customer_segment
FROM Card_Transaction
GROUP BY customer_id
ORDER BY annual_revenue DESC;

Output Table:

customer_id	annual_revenue	customer_segment
1	1500	High-Value
2	800	Medium-Value

Explanation: This segmentation groups customers based on spending thresholds, making it easy to identify valuable customers.

Finding: Customer segmentation assists AB Bank in tailoring marketing and offers based on customer value.

Bonus Question

Here’s a challenge to put your SQL skills to the test and apply concepts from an SQL project! Share your solution in the comments below.

Question: How can you calculate the average credit utilization for each card type, categorized by card status? Use the transaction data to determine utilization patterns, where credit utilization is defined as the ratio of the amount used to the credit limit. Write an SQL query to group the results by card type and card status, and calculate the average utilization for each category.

💡 Share your approach and query in the comments! For more such SQL challenges, check out Colon Coding Community.

Findings from SQL Financial Data Analysis

Spending Patterns: Spending trends reveal significant seasonal fluctuations, with peaks observed in specific quarters. This SQL project highlights patterns that can guide AB Bank in planning promotional campaigns during high-spending periods to maximize customer engagement.

Customer Segmentation: By leveraging SQL financial data analysis, AB Bank can categorize customers into high, medium, and low spenders. This segmentation allows for targeted marketing and personalized offers, improving customer retention and satisfaction.

Regional Growth: The SQL case study uncovers that transaction growth varies widely across regions. Identifying high-growth regions enables AB Bank to focus resources strategically and capture emerging market opportunities.

Utilization Trends: Credit utilization analysis from this SQL project reveals higher activity on specific card types, indicating opportunities for cross-selling and product upgrades. Understanding utilization patterns helps the bank enhance customer loyalty and profitability.

Recommendations for AB Bank Based on the SQL Project

1. Promotional Focus: Target peak spending quarters with promotions to drive customer engagement and revenue.
2. Customer Engagement: Utilize SQL financial data analysis insights to craft personalized offers for high-value customer segments.
3. Expand in Growing Regions: Leverage findings from the SQL case study to enhance services in high-growth regions and capture additional market share.
4. Manage High Utilization Cards: Encourage customers with high credit utilization to upgrade to premium products or consider cross-selling additional financial services.

Conclusion

This SQL project has provided AB Bank with a comprehensive framework for SQL financial data analysis, enabling the assessment of customer spending habits, segmentation by value, regional transaction growth, and credit utilization trends. By applying the insights from this SQL case study, AB Bank can make data-driven decisions to improve customer satisfaction, optimize operational efficiency, and achieve sustainable revenue growth. Through SQL analysis, AB Bank is well-positioned to address industry challenges and stay ahead in a competitive financial landscape.