Bank Customer Analysis with SQL

A SQL project that transforms relational banking data into a customer-level analytical table, combining account ownership, incoming and outgoing transactions, monetary totals and age.

The main deliverable is banca.feature_cliente: one row per customer, with 27 columns, including the customer identifier and age. The script includes source-data checks and reconciliation queries throughout the transformation.

Read the SQL project

Code comments and column names are in Italian. This README provides an English overview.

Project objective

Build a consolidated customer view that can support reporting, customer profiling and subsequent analytical work. Customers without accounts and accounts without transactions are retained, so absence of activity does not remove a customer from the final table.

The project constructs analytical features; it does not train a predictive model or assign customer segments.

Source data and prerequisites

The script expects an existing banca database imported from db_bancario.sql. That source database file is not included in this repository, so the script cannot be executed from the repository alone.

Source table	Role
banca.cliente	Customer identifiers and dates of birth
banca.conto	Accounts, their owners and account types
banca.tipo_conto	Account-type descriptions
banca.tipo_transazione	Transaction types and direction through segno
banca.transazioni	Account transactions and amounts

The script uses MySQL-style SQL, including session variables, TIMESTAMPDIFF, temporary tables and DECIMAL(18,2). The repository does not specify a tested database-server version.

Transformation workflow

Transactions + transaction types
              |
              v
Transaction totals per account
              |
              v
All accounts + account types + transaction totals
              |
              v
Customer totals and breakdowns by account type
              |
              v
All customers + age + aggregated indicators
              |
              v
banca.feature_cliente

The script is organised into 17 numbered blocks:

1. Inspect the source: count rows, inspect account and transaction types, check amount signs, missing values, identifiers and relationships.
2. Aggregate transactions by account: calculate incoming and outgoing counts and amounts before joining account-level data. This avoids counting an account once for every transaction.
3. Retain all accounts: use left joins and assign zero activity indicators where an account has no transactions.
4. Aggregate by customer: calculate overall indicators and conditional breakdowns for four account types.
5. Calculate age: use completed years at a reference date fixed once per session.
6. Create and populate the final table: retain the full customer register and store monetary amounts with two decimal places.
7. Reconcile the result: compare coverage, counts and amounts with the source and intermediate aggregates.

Final table

Column group	Contents	Columns
Identification and age	id_cliente, eta	2
Overall transactions	Incoming/outgoing transaction counts and amounts	4
Account ownership	Total accounts and counts for each of four account types	5
Transactions by account type	Incoming/outgoing counts and amounts for each type	16

The four account types are encoded explicitly in the script:

Account type ID	Source label
0	Conto base
1	Conto business
2	Conto privati
3	Conto famiglie

id_cliente is the primary key. Banking activity indicators are non-null, while eta remains nullable.

Business rules

• Transaction direction is determined by tipo_transazione.segno: + for incoming and - for outgoing transactions.
• Outgoing amounts are multiplied by -1 to present them as positive values, assuming the source uses negative amounts for outgoing transactions. The initial checks identify violations of that convention.
• All available transactions are included; no reporting-period filter is applied.
• Missing activity after aggregation is represented by zero. Missing or future dates of birth produce a null age, not zero years.
• The reference date defaults to CURRENT_DATE(). Age therefore changes across execution dates; a fixed reference date is needed when reproducing a historical snapshot.
• Amounts are rounded when written to the final table. Intermediate aggregations retain their original precision.

Data-quality and reconciliation checks

The SQL includes queries for:

• Missing amounts and amounts inconsistent with transaction direction.
• Orphaned account and transaction references.
• Missing or duplicate identifiers in the customer, account and lookup tables.
• Account and transaction totals across aggregation stages.
• Incoming and outgoing amounts across source and intermediate tables.
• Missing or unexpected customers in the final table.
• Non-zero activity indicators for customers without accounts.
• Agreement between each customer's total counts and the sum of account-type counts.
• Per-customer monetary totals recalculated from the source tables.

Several final checks return OK or DA VERIFICARE. These are diagnostic query results, not automatic execution guards: review anomalies before relying on the final table.

The repository contains the SQL implementation but no saved execution results or source database. Numerical customer totals and successful validation outcomes are therefore not claimed here.

How to run

1. Obtain the assignment's db_bancario.sql and import it into a MySQL environment, creating the expected banca schema and tables.
2. Open progetto_sql_banca_claudio_lombardi.sql in a SQL client such as MySQL Workbench.
3. Run the numbered blocks in order, using the same database connection throughout because temporary tables and variables are session-scoped.
4. Review the initial quality checks and confirm the account-type mapping and transaction-sign convention.
5. Review the final coverage, count and monetary reconciliation results.
6. Inspect the output:

SELECT *
FROM banca.feature_cliente
ORDER BY id_cliente
LIMIT 20;

Execution behaviour: block 12 drops and recreates banca.feature_cliente; block 13 populates the newly created table. Running the full script replaces any existing project output table. The five source tables are not modified by this script.

Repository contents

File	Description
progetto_sql_banca_claudio_lombardi.sql	Transformation and validation queries
README.md	Project documentation

The resulting table is created inside the database. The script does not automatically export a CSV.

Skills demonstrated

SQL joins · Conditional aggregation · Temporary tables · Customer-level feature engineering · Null handling · Date calculations · Explicit table schemas · Data-quality checks · Reconciliation

Author

Claudio Lombardi