Skip to content
All projects

Project 06 // Analytics · 2025

Bank Transaction SQL Analysis

Validate first, then analyze

A data-quality bar shows 87.7% complete rows, 12.3% missing a field, and 17 invalid currency codes, then credit bands show Poor-credit accounts hold 51.4% of transaction value.
of rows had a missing field
12.3%
Invalid currency codes
17
Accounts
800
of value from Poor-credit accounts (52% of customers)
51%

In short

The question

Which branches and currencies carry the most activity? How does behavior differ by credit-score band, account type, customer sector, and city? When are the peak transaction hours?

What I built

  1. 01
    Excel / .bak1,000 transactions
  2. 02
    validation.sqlProfiles missing fields and currency codes
    • Data profiling and validation
  3. 03
    transform.sqlBuckets values and parses semi-structured fields
    • CASE-based bucketing
    • Parsing semi-structured fields
  4. 04
    analysis.sqlBusiest hour is 15:00; not tested against chance
    • T-SQL joins and aggregation
    • Exploratory analysis
  5. 05
    InsightsValue is roughly proportional to customer share

What it found

  • In these 1,000 transactions the busiest hour is 15:00; I have not tested whether this exceeds chance variation.
  • Poor-credit accounts hold 51% of transaction value, matching their 52% share of customers, so value is roughly proportional to customer share.
  • SQL Server
  • T-SQL
  • Data quality

Full case study // tap a section to open it

01Problem & context

Which branches and currencies carry the most activity? How does behavior differ by credit-score band, account type, customer sector, and city? When are the peak transaction hours?

Banks act on branch, product, and customer-segment insights. Analysis built on data nobody has checked leads to confident but wrong decisions, so this project proves the data can be trusted before answering any business question.

A retail-banking analysis in SQL Server that answers questions such as which branches and currencies carry the most activity, how behavior differs by credit-score band, account type, sector, and city, and when transactions peak.

The workflow mirrors a real analyst engagement: first prove the data can be trusted (validation), fix what is broken (transformation), then analyze. A query-by-query walkthrough in the repository shows each SQL statement, its result, and what it means.

02My role & approach

Solo project: validation, transformation, and analysis scripts in T-SQL.

  1. 01

    Validate: NULL checks on every column, duplicate-key checks, distinct-value profiling, and distributions for categorical columns.

  2. 02

    Transform: standardize missing transaction amounts.

  3. 03

    Analyze: volume and value by branch, currency, credit-score range, account type, transaction type, sector, residence length, and city; peak transaction hours.

  4. 04

    Parsed a semi-structured text field (sector, years of residence, city) during analysis.

Problems I hit and how I handled them

  • Every column profiled first: NULL checks, duplicate-key checks, distinct values, and distributions for categorical columns.
  • Missing transaction amounts standardized in a separate transform step, so the fix is explicit and repeatable.
  • A semi-structured text field (sector, years of residence, city) parsed with T-SQL so it could be analyzed.
03Tech stack & data model
  • SQL Server and SSMS

    Restoring the database and running the three scripts in order.

  • T-SQL

    CTEs, window functions (LAG, RANK, SUM OVER), CASE bucketing, and parsing a semi-structured text field.

  • Data profiling

    NULL checks, duplicate-key checks, distinct values, and categorical distributions before any analysis.

  • DuckDB

    Re-running the same logic on the same Excel files to publish the result tables in the walkthrough.

Data model

Two tables joined on account number. A semi-structured details field is parsed into sector, years of residence, and city during analysis.

  • Fact table

    banking_transaction

    1,000 rows, Jan 2023 – Sep 2025: transaction ID, account, type, amount, date, time, branch, and currency.

  • Dimension

    customer_account

    800 rows: account holder, account type, balance, interest rate, credit score, opening date, loan amount, and holder details.

Skills shown

  • Data profiling and validation
  • T-SQL joins and aggregation
  • CASE-based bucketing
  • Parsing semi-structured fields
  • Exploratory analysis
04What I learned & would improve

Lessons

  • Profiling first (12.3% of rows missing fields, 17 invalid currency codes) decides which questions the data can honestly answer.
  • A partial year can look like a 25% drop; comparing per month removes the false signal.

What I would do next

  • Add validation rules at load time so bad rows are flagged before analysis.
  • Convert amounts with a dated exchange-rate table so cities can be compared in one currency.

Walkthrough

What each dashboard and diagram shows, and why it matters. Click an image to enlarge it.

  1. 1

    Validate: can we trust the data?

    Before any analysis, every column is profiled.

    • 12.3% of raw transactions had at least one missing field: 60 with no transaction type, 60 with no amount, and 5 with no currency (some rows miss more than one).
    • 17 rows carried invalid currency codes such as -999 and *^$$, which would distort any currency-level report.
    • Primary keys in both tables are unique, so joins will not double-count.
  2. 2

    When customers transact

    Transactions by hour of day.

    • Activity peaks at 15:00.
    • Useful for staffing branches and scheduling system maintenance away from busy hours.
  3. 3

    Value by account type

    How each account type is actually used.

    • Credit accounts lean on transfers, checking accounts on withdrawals, and loan accounts on deposits and payments.
    • The mix across transaction types, currencies, and account types is fairly balanced; no single segment dominates.
  4. 4

    Analysis queries

    Context matters as much as the headline number.

    • The apparent 25% drop in 2025 is a partial year; per month, activity is flat.
    • Poor-credit customers account for 51% of value, which simply matches their 52% share of customers.
    • Activity is spread over 431 branches, so branch rankings are noisy at this data size.
Next projectMedical Cost Modeling and Prediction
Esc

↑ ↓ to moveEnter to open