SQL Banking Customer Analytics

What this project is

SQL Banking Customer Analytics is a SQL analytics project built on a relational banking model. It creates a complete customer view from transactions, accounts, and customer tables using joins, CTEs, conditional aggregations, rankings, and window-function segmentation.

Technical context

Data in a banking system is distributed across customers, accounts, account types, and transactions. Individual rows describe atomic movements but do not directly show how many accounts a customer owns, how much money they moved, or how they compare with the rest of the portfolio.

The analytical challenge was to turn a normalized relational model into a customer-level summary while retaining customers with no accounts or no transactions. A second requirement was to move beyond absolute metrics and add relative comparisons based on rankings, the global average, and segmentation.

Objective

The objective was to build a reproducible MySQL 8+ project capable of:

• creating a test banking database with explicit relationships and constraints;
• producing one balance-analysis row for every customer;
• separating total income and expenses by account type;
• counting accounts correctly without join-induced duplication;
• ranking customers by income and net transaction flow;
• comparing each customer with the portfolio average;
• assigning four descriptive segments through quartiles;
• validating calculations against known cases and indexing join columns.

The result is a compact customer analytics workflow implemented entirely in SQL, from test-data creation to the final comparative view.

Data model

The banca database contains five connected tables:

• cliente stores customer identity and birth date;
• conto assigns one or more accounts to each customer;
• tipo_conto distinguishes Base, Business, Private, and Family accounts;
• transazioni records the account, type, and amount of each movement;
• tipo_transazione identifies income and expenses through the segno field.

The main relationships are customer-to-account and account-to-transaction, both one-to-many. The synthetic dataset contains 15 customers, 23 accounts, and 359 transactions, together with two edge cases: one customer with no accounts and one account with no transactions.

Customer balance analysis

The main query joins the tables through LEFT JOIN and produces one row per customer. This choice retains inactive customers who would disappear from the result with INNER JOIN.

Metrics are calculated through conditional aggregations:

• total number and amount of incoming transactions;
• total number and amount of outgoing transactions;
• number of distinct accounts per customer;
• counts and amounts split across Base, Business, Private, and Family accounts;
• customer age calculated through TIMESTAMPDIFF.

The SUM(CASE WHEN ... THEN ... ELSE 0 END) pattern pivots row-level categories into adjacent metrics. COUNT(DISTINCT) prevents the same account from being counted repeatedly after joining it to its transactions.

Ranking and segmentation

The second query separates metric calculation from classification through the bilancio_per_cliente CTE. It then applies four comparative analyses:

• RANK() OVER to order customers by total income;
• RANK() OVER to order customers by net transaction flow;
• AVG() OVER () to compare each customer with the global average;
• NTILE(4) to divide the portfolio into Low, Medium-Low, Medium-High, and High tiers.

Window functions preserve one row per customer while adding information relative to the whole portfolio, without requiring a correlated subquery for every record.

Output example

The output highlights that incoming volume and net flow tell different stories:

• Valentina Galli ranks first by net flow, with EUR 6,950 income, EUR 3,595 expenses, and a +EUR 3,355 result;
• Marco Ricci ranks second by net flow, with a +EUR 2,984 result;
• Stefano Marini ranks first by income with EUR 32,250 but last by net flow: EUR 40,108 in expenses produces a -EUR 7,858 result;
• average income per customer, including inactive customers, is EUR 5,882.

This comparison shows why a ranking based only on volume would be incomplete: the same query exposes transaction intensity, net result, and relative position.

Calculation validation

The project includes test_singolo_cliente.sql, used to validate the logic against a controlled case. Customer 15 owns three accounts and has exactly 184 movements: 159 expenses and 25 incoming transactions.

The test also reconciles overall amounts with the sum of amounts split by account type. This incremental development method reduces the risk of accepting aggregations that are syntactically valid but numerically incorrect.

Performance

The setup defines primary keys on every table and indexes on the most frequently joined foreign keys:

• conto.id_cliente for the customer-to-account join;
• transazioni.id_conto for the account-to-transaction join;
• transazioni.id_tipo_trans for movement classification.

With only 359 transactions, the benefit cannot be benchmarked meaningfully. The structure nevertheless prevents the project from ignoring join cost as volume grows. These indexes are therefore a design choice, not a claimed performance result.

Results

• 15 customers retained in the analysis, including inactive edge cases;
• 23 accounts distributed across four account types;
• 359 transactions, including 71 incoming and 288 outgoing movements;
• EUR 88,230 in income and EUR 71,275.50 in expenses across the dataset;
• two independent rankings for income and net flow;
• percentage comparison against a EUR 5,882 average;
• four customer tiers generated through NTILE.

Future evolution

The project uses synthetic data to focus on SQL logic, relational modeling, and analytical view design. The same structure can be extended toward richer banking scenarios by adding temporal dimensions, opening balances, currency, and accounting reconciliation.

The quartile segmentation is designed as an analytics view of the customer portfolio. The main future improvements would be:

• add timestamps for monthly analysis, trends, and rolling metrics;
• model opening and current balances for each account;
• introduce data-quality checks for amounts, signs, and orphan keys;
• create reusable views for balance analysis and segmentation;
• inspect execution plans with EXPLAIN ANALYZE on larger volumes;
• publish the results through a BI dashboard;
• add automated SQL tests for counts, nulls, and amount reconciliation.

Technical FAQ

Why use LEFT JOIN?

It keeps every customer in the result, including customers with no accounts and customers whose accounts have no transactions. An inner join would remove them from the analysis.

Why is COUNT(DISTINCT) required?

After the join, the same account appears once for each transaction. Counting account identifiers without DISTINCT would therefore overstate the number of accounts.

Are net flow and bank balance the same thing?

No. In this project, net flow is the difference between the income and expenses included in the dataset. Without an opening balance and a complete transaction history, it does not represent the actual available balance.

Do the segments represent customer risk?

No. NTILE(4) creates descriptive quartiles ordered by net flow. Estimating risk would require dedicated variables, rules, and validation.

Why is MySQL 8+ required?

The ranking query uses CTEs and window functions, features supported by MySQL starting with version 8.0.