kharatatva/BankIQ-SQL

## 📌 项目概述 BankIQ 模拟了一家中等规模零售银行的内部分析数据库，业务覆盖**印度 134 个城市**。 完全从零开始构建 —— 包含 schema 设计、逼真的数据生成，以及 40 个循序渐进的 SQL 查询， 还原了真实银行分析师、合规团队和欺诈团队日常编写的分析代码。 | 指标 | 数值 | |---|---| | 总客户数 | 150–500（通过 Faker 生成） | | 总交易笔数 | 6,169 | | 总处理交易额 | ₹16.82 Crore | | 覆盖城市数 | 134 | | 活跃贷款数 | 85 | | 不良贷款率 | 14.2% | | 平均交易额 | ₹27,269 | ## 🗂️ 仓库结构 ``` BankIQ/ │ ├── README.md ← You are here ├── FINDINGS.md ← Analyst findings report with real insights ├── .env.example ← Example env file for generate_data connection with database ├── 01_schema.sql ← CREATE TABLE for all 12 tables with constraints ├── 02_seed_data.sql ← Original seed data (30 customers, 60 transactions) ├── 03_queries.sql ← 40 SQL tasks across 7 progressive phases ├── 04_analysis.ipynb ← EDA notebook with 6 analytical charts └── scripts/ └── generate_data.py ← Faker script — generates realistic banking data ``` ## 🗃️ 数据库设计 — 12 张表 | 表名 | 描述 | |---|---| | `branches` | 分布在孟买、德里、班加罗尔、钦奈和加尔各答的 6 个实体分支机构 | | `employees` | 员工 —— 包括经理、分析师、信贷员、柜员和合规人员 | | `customers` | 具有KYC状态和CIBIL信用评分的个人和企业客户 | | `accounts` | 储蓄账户、活期账户、工资账户、定期存款和零存整取账户 | | `transactions` | 2024 年发生的 UPI、NEFT、RTGS、IMPS、ATM 和自动扣款交易 | | `loans` | 房贷、车贷、个人贷款、商业贷款、教育贷款和黄金贷款产品 | | `loan_repayments` | EMI还款记录，包含漏缴、拒付和部分付款记录 | | `cards` | 涵盖 Visa、Mastercard 和 RuPay 网络的借记卡和信用卡 | | `fixed_deposits` | 包含到期日和派息配置的定期存款（FD）明细记录 | | `kyc_documents` | 每位客户的 PAN、Aadhaar、护照和选民身份证文件 | | `fraud_alerts` | 由多规则欺诈检测引擎触发的警报标记 | | `interest_postings` | 每季度计入储蓄账户和存款账户的利息 | ## 🔗 实体关系概览 ``` branches ──< employees ──< customers ──< accounts ──< transactions ──< loans ──< loan_repayments ──< cards ──< kyc_documents accounts ──< fixed_deposits accounts ──< interest_postings loans ──< fraud_alerts ``` ## 📋 查询覆盖范围 — 涵盖 7 个阶段的 40 项任务 | 阶段 | 任务 | 涉及技能 | |---|---|---| | 阶段 1 — Schema 与预热 | 1–8 | DDL、ALTER TABLE、基础 SELECT、WHERE、ORDER BY、简单 JOIN | | 阶段 2 — 多表 JOIN | 9–16 | INNER JOIN、LEFT JOIN、反连接模式、4 表 JOIN | | 阶段 3 — 聚合与子查询 | 17–26 | 标量子查询、相关子查询、CASE WHEN、HAVING、日期运算 | | 阶段 4 — CTE | 27–31 | WITH 子句、链式 CTE、客户健康评分、薪资差异分析 | | 阶段 5 — 窗口函数 | 32–38 | RANK、DENSE_RANK、PERCENT_RANK、LAG、累计求和、移动平均值 | | 阶段 6 — 欺诈检测 | 39 | 自连接时间窗口、UNION ALL、MOD、EXTRACT、多规则引擎 | | 阶段 7 — 顶点视图 | 40 | 由 3 个链式 CTE 支撑的 CREATE VIEW，高管仪表盘 | ## ⭐ 项目亮点 ### 🔍 客户健康评分（任务 27） 基于 CTE 的评分系统，根据 KYC 验证状态、漏缴 EMI 次数和账户总余额，对每位客户进行 **A / B / C** 分级。 这与真实银行 CRM 系统中用于营销定位和风险细分的逻辑完全一致。 ### 🚨 多规则欺诈检测引擎（任务 39） 可同时标记匹配以下 4 种独立欺诈模式之一的交易： - 快速借记爆发 —— 在 60 分钟的时间窗口内发生超过 3 次借记（自连接） - 金额异常 —— 交易金额超过该账户自身平均借记金额的 3 倍 - 非营业时间活动 —— 午夜至凌晨 04:59 之间的任何交易 - 整数高额交易 —— 金额可被 ₹10,000 整除且超过 ₹50,000 通过 CTE 和 `UNION ALL` 构建，使得单笔交易可以触发多个警报标记。 ### 📊 高管分支机构仪表盘视图（任务 40） 一个由 3 个链式 CTE 支撑的 `CREATE VIEW`，整合了每个分支机构的月度交易流入、 贷款组合规模、不良贷款数量、客户总数以及平均信用评分 —— 所有这些只需一条 `SELECT` 语句即可查询。 ### 📈 探索性数据分析 (04_analysis.ipynb) 基于 Faker 生成的真实数据，使用 Matplotlib 和 Seaborn 构建的六个分析图表： - 月度交易量趋势（2024 年） - 按总存款额比较各分支机构的业绩 - 带有 CIBIL 区间叠加的客户信用评分分布 - 按城市划分的贷款违约率 - 按借记金额排名的前 5 大消费类别 - 按数量和持有余额划分的账户类型分布 ## 📊 关键发现 - **14.2% 的不良贷款率**（134 笔贷款中有 19 笔违约或成为 NPA）—— 显著高于 3–5% 的行业基准 - **二线城市在信用评分上优于特大城市** —— Tadepalligudem (885)、Kumbakonam (872) 对比孟买 (770)、德里 (777) - **58% 的城市只有 1 位客户** —— 地理集中度风险较高，孟买占据了不成比例的权重 - **平均交易额 ₹27,269** 表明许多客户将 BankIQ 作为辅助银行使用 —— 提高每位客户的交易频率是关键的增长杠杆 ## ⚙️ 设置说明 **环境要求：** MySQL 8.0+ · Python 3.10+ · pip **第 1 步 —— 创建数据库** ``` CREATE DATABASE bank_system; USE bank_system; ``` **第 2 步 —— 创建所有 12 张表** ``` mysql -u root -p bank_system < 01_schema.sql ``` **第 3 步 —— 插入种子数据** ``` mysql -u root -p bank_system < 02_seed_data.sql ``` **第 4 步 —— 生成逼真的数据量（可选，但推荐）** ``` pip install faker mysql-connector-python python scripts/generate_data.py ``` **第 5 步 —— 运行所有 40 个查询** ``` mysql -u root -p bank_system < 03_queries.sql ``` **第 6 步 —— 运行 EDA notebook** ``` pip install pandas sqlalchemy pymysql matplotlib seaborn jupyter jupyter notebook 04_analysis.ipynb ``` ## 🛠️ 展示技能 - 包含主键、外键和 CHECK 约束的关系型 schema 设计 - 处理 `branches` 和 `employees` 表之间的循环外键依赖 - 单个查询中跨越最多 4 张表的多表 JOIN - 使用 `LEFT JOIN + IS NULL` 的反连接模式 - 标量子查询和相关子查询 - 在 `SUM` 和 `COUNT` 中使用 `CASE WHEN` 进行条件聚合 - Common Table Expressions (CTE) —— 简单、链式和多级结构 - 窗口函数 —— `RANK`、`DENSE_RANK`、`PERCENT_RANK`、`LAG`、`FIRST_VALUE` - 使用 `ROWS UNBOUNDED PRECEDING` 计算累计总和 - 使用 `ROWS BETWEEN` 框架子句计算移动平均值 - 基于时间的自连接，用于滑动窗口欺诈检测 - 使用 `CREATE VIEW` 创建可复用的报表层 - 使用 Python + SQLAlchemy 实现数据库连接 - 使用 Pandas、Matplotlib 和 Seaborn 进行探索性数据分析 - 使用 Faker 库生成逼真的模拟数据 ## 📝 简历摘要 ## 👤 作者 **Tatva V. Khara** 印度，古吉拉特邦，瓦皮 (Vapi) kharatatva60@gmail.com

标签：Python, SQL, 代码示例, 数据分析, 无后门, 系统审计, 金融科技