Skip to content

About

PostgreSQL analytics portfolio using PaySim synthetic mobile-money transactions: data-quality auditing, Q1–Q10 analysis, independent SQL validation, and documented findings.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

43 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

PaySim SQL Portfolio

A PostgreSQL-based SQL analytics portfolio project using the PaySim synthetic mobile-money transaction dataset. The project investigates transaction activity, monetary patterns, simulated fraud labels, sender and recipient identifier segments, and transaction balance movements.

The repository documents the full workflow from dataset profiling and data-quality investigation to SQL analysis, independent validation, and business-oriented interpretation.

Data note: PaySim is synthetic data. The results describe the supplied simulation and should not be interpreted as actual UPI or production payment-platform behaviour.


Project at a Glance

Item Details
Dataset Synthetic Financial Datasets For Fraud Detection (PaySim)
Source Kaggle — PaySim
Original transaction records 6,362,620
Original dataset columns 11
Database PostgreSQL
Main table public.transactions
Analytical questions 10
Transaction categories 5
Observed simulation steps 743 (steps 1–743)
SQL validation Independent validation scripts for Q2–Q10

Project Objectives

  • Profile a large transaction-level dataset and document its structure and limitations.
  • Investigate missing values, exact duplicates, identifiers, transaction categories, fraud fields, numeric values, and balance consistency.
  • Define analytical questions that can be answered with the available fields.
  • Implement the analyses in PostgreSQL using clear, reusable SQL.
  • Validate aggregate totals, segment outputs, rankings, and distribution statistics independently of the analytical queries.
  • Translate query outputs into findings while distinguishing evidence from assumptions.
  • Publish an organised and reproducible project repository.

Dataset

Dataset name: Synthetic Financial Datasets For Fraud Detection
Creator: Edgar Alonso Lopez-Rojas
Source: Kaggle — PaySim
Original file: PS_20174392719_1491204439457_log.csv
Original file size: Approximately 470 MB
Dataset licence shown on Kaggle: CC BY-SA 4.0. See the Creative Commons licence.

One row in the original CSV represents one simulated transaction. The original CSV is not committed to this repository. The .gitignore excludes CSV/ZIP files under data/original/, Python bytecode and __pycache__/, the local .venv/, and other temporary files. Download the CSV from Kaggle and keep it in the local data/original/ directory.

Original dataset fields

Original field Description
step Hourly simulation step
type Transaction category
amount Individual transaction amount
nameOrig Sender account identifier
oldbalanceOrg Sender balance before the transaction
newbalanceOrig Sender balance after the transaction
nameDest Recipient account identifier
oldbalanceDest Recipient balance before the transaction
newbalanceDest Recipient balance after the transaction
isFraud Simulated fraud label
isFlaggedFraud Separate rule-based fraud flag

The five transaction categories are CASH_IN, CASH_OUT, DEBIT, PAYMENT, and TRANSFER.

PostgreSQL table and column mapping

The SQL scripts load the original CSV fields into public.transactions using descriptive PostgreSQL column names:

PostgreSQL column Original CSV field / purpose
record_id Database-generated identity key; not an original PaySim transaction ID
step step
transaction_type type
amount amount
sender_id nameOrig
sender_old_balance oldbalanceOrg
sender_new_balance newbalanceOrig
recipient_id nameDest
recipient_old_balance oldbalanceDest
recipient_new_balance newbalanceDest
is_fraud isFraud
is_flagged_fraud isFlaggedFraud

record_id is generated by PostgreSQL when rows are inserted. It should not be treated as a transaction identifier supplied by PaySim. The table definition and constraints are in sql/01_create_transactions_table.sql.

Analytical Questions

The project contains ten SQL analyses:

Question Analysis Main focus
Q1 Transaction Volume Distribution Transaction count and category share
Q2 Transaction Monetary Analysis Total and average transaction amount by category
Q3 Hourly Transaction Trends Transaction count and total amount by simulation step
Q4 Ranking Transaction Activity Rank simulation steps by transaction count, then transaction amount
Q5 Fraud-Labelled Transaction Analysis Fraud-labelled counts, category rates, and concentration
Q6 Fraud Flag vs Fraud Label Analysis Confusion matrix, precision, and recall of the existing flag against the simulated label
Q7 Sender Activity Distribution Top senders and one-time vs repeat sender segments
Q8 Recipient Transaction Concentration Top recipients and customer-coded vs merchant-coded recipient identifier segments
Q9 Transaction Amount Distribution Amount statistics by transaction category and fraud label
Q10 Balance Movement Analysis Sender and eligible recipient balance movement comparisons

Detailed question definitions, calculation logic, query outputs, interpretations, and limitations are documented in research/analytical_questions.md. Consolidated findings are in research/results_and_insights.md.

Selected Findings

The figures below are from the recorded query outputs and consolidated analysis document.

Transaction activity and monetary value

  • CASH_OUT is the largest category by transaction count: 2,237,500 transactions (35.17%).
  • PAYMENT contributes 2,151,495 transactions (33.81%). Together, CASH_OUT and PAYMENT represent 68.98% of transaction records.
  • TRANSFER has the highest category-level total transaction amount: 485,291,987,263.17, and the highest average amount: 910,647.01.
  • Hourly activity is uneven. Step 19 has the highest transaction count (51,352); step 304 has the highest total transaction amount in the hourly output (24,963,550,343.94).

Fraud labels and existing flag

  • The dataset contains 8,213 fraud-labelled records. They occur in CASH_OUT (4,116) and TRANSFER (4,097).
  • TRANSFER has the higher within-category fraud-label rate (0.77%) compared with CASH_OUT (0.18%).
  • The existing rule-based flag marks 16 records that all match the dataset's fraud label. Against those labels, the recorded confusion matrix gives 100% precision and approximately 0.19% recall. This evaluates agreement with the synthetic labels, not a production fraud system.

Sender and recipient segments

Sender segment Distinct sender identifiers Transactions Transaction share Monetary value share
One-time 6,344,009 6,344,009 99.7075% 99.7021%
Repeat 9,298 18,611 0.2925% 0.2979%

Q8 classifies recipient identifiers using their supplied prefixes. These are dataset identifier categories, not independent verification of real-world customer or merchant identity.

Recipient identifier segment Distinct identifiers Transactions Transaction share Monetary value share
Customer-coded (C prefix) 571,961 4,211,125 66.1854% 97.5451%
Merchant-coded (M prefix) 2,150,401 2,151,495 33.8146% 2.4549%

Transaction amount and balance movement

  • Q9 compares average, median, minimum, and maximum transaction amounts across seven transaction-type/fraud-label groups.
    • Fraud-labelled TRANSFER records have a higher average transaction amount (1,480,891.67) than non-fraud-labelled TRANSFER records (906,229.01). For CASH_OUT, the pattern is reversed: the fraud-labelled group averages 1,455,102.59, compared with 1,739,917.16 for the non-fraud-labelled group. These are descriptive comparisons within the synthetic dataset, not evidence of a real-world fraud signal.
  • Q10 uses separate expected-movement logic for incoming and outgoing sender transactions. Recipient reconciliation is limited to eligible, non-fraud-labelled TRANSFER records with tracked recipient balances.
  • The recorded independent validation results include 8 passing Q7 checks, 16 passing Q8 checks, 10 passing Q9 checks, and 26 passing Q10 checks.

See research/results_and_insights.md for the full Q1–Q10 result tables, cross-question insights, validation evidence, and analytical limitations.

Methodology and SQL Techniques

Data understanding and quality checks

Python and Pandas were used for initial CSV inspection and data-quality investigation. The documented checks include:

  • Dataset dimensions and data types.
  • Missing-value counts.
  • Exact duplicate records.
  • Sender and recipient identifier frequency.
  • Transaction category and fraud-field values.
  • Numeric-value quality and zero-amount records.
  • Sender and recipient balance consistency.

The recorded data-quality findings include zero missing values, zero exact duplicate rows, zero unexpected transaction categories, zero unexpected fraud-flag values, zero negative numeric values, and 16 zero-amount transactions.

SQL analysis

The PostgreSQL analyses use:

  • GROUP BY and aggregate functions (COUNT, SUM, AVG, MIN, MAX).
  • CASE expressions and conditional aggregation.
  • Common table expressions (CTEs).
  • Window functions and ranking.
  • Percentage calculations and denominator reconciliation.
  • PERCENTILE_CONT(0.5) for median transaction amounts.
  • Explicit scope rules for balance movement comparisons.

The source dataset is primarily a single transaction table, so the project does not manufacture relational joins where the source does not provide a justified relationship.

Independent validation

Validation SQL is stored in sql/validation/. These scripts independently recalculate and reconcile key outputs such as record counts, monetary totals, group counts, rankings, segment values, and distribution measures. Validation scripts are separate from the analytical queries so that checks do not simply reuse the analytical result as their own expected answer.

Execution Evidence

PostgreSQL execution screenshots and validation evidence for Q5–Q10 are documented in the evidence index.

The index identifies the analytical outputs, validation results, and interpretation limitations for each question.

Repository Structure

PaySim_SQL_Portfolio/

├── data/
│   └── original/
│       └── .gitkeep
├── evidence/
│   ├── README.md
│   └── screenshots/
│       └── Q5–Q10 analytical and validation screenshots
├── research/
│   ├── analytical_questions_overview.md
│   ├── analytical_questions.md
│   ├── data_quality_findings.md
│   ├── dataset_source_notes.md
│   ├── results_and_insights.md
│   ├── missing_value_audit.csv
│   ├── exact_duplicate_audit.csv
│   ├── identifier_frequency_summary.csv
│   ├── transaction_category_counts.csv
│   ├── fraud_flag_value_counts.csv
│   ├── numeric_quality_summary.csv
│   ├── zero_amount_summary.csv
│   ├── zero_amount_transactions.csv
│   ├── sender_balance_movement.csv
│   ├── recipient_balance_movement.csv
│   ├── top_10_senders.csv
│   ├── top_10_recipients.csv
│   └── q3_hourly_transaction_trends.csv
├── scripts/
│   ├── 01_initial_csv_inspection.py
│   ├── 02_missing_value_audit.py
│   ├── 03_exact_duplicate_audit.py
│   ├── 04_identifier_frequency_audit.py
│   ├── 05_category_flag_validation.py
│   ├── 06_zero_amount_audit.py
│   ├── 07_balance_consistency_audit.py
│   └── 08_monetary_precision_audit.py
├── sql/
│   ├── 00_create_database.sql
│   ├── 01_create_transactions_table.sql
│   ├── 02_import_transactions.sql
│   ├── 03_q1_transaction_volume.sql
│   ├── 04_q2_transaction_monetary_analysis.sql
│   ├── 05_q3_hourly_transaction_trends.sql
│   ├── 06_q4_hourly_activity_ranking.sql
│   ├── 07_q5_fraud_label_analysis.sql
│   ├── 08_q6_fraud_flag_evaluation.sql
│   ├── 09_q7_sender_activity_distribution.sql
│   ├── 10_q8_recipient_transaction_concentration.sql
│   ├── 11_q9_transaction_amount_distribution.sql
│   ├── 12_q10_balance_movement_analysis.sql
│   ├── .gitkeep
│   └── validation/
│       ├── 01_q2_monetary_reconciliation.sql
│       ├── 02_q3_hourly_transaction.sql
│       ├── 03_q4_hourly_activity_ranking.sql
│       ├── 04_q5_fraud_label_analysis.sql
│       ├── 05_q6_fraud_flag_evaluation.sql
│       ├── 06_q7_sender_activity_distribution.sql
│       ├── 07_q8_recipient_transaction_concentration.sql
│       ├── 08_q9_transaction_amount_distribution.sql
│       └── 09_q10_balance_movement_validation.sql
├── .gitignore
└── README.md

The original PaySim CSV is intentionally excluded from tracked files. The local .venv/, Python cache files, and temporary files are excluded through .gitignore.

Reproducing the Analysis

Clone the repository

Open PowerShell or a terminal and execute:

git clone https://github.com/ANIKETKOLI20/PaySim_SQL_Portfolio.git
cd PaySim_SQL_Portfolio

Run the subsequent commands from this project root directory so that the relative SQL-script and CSV paths resolve as documented.

Requirements

  • PostgreSQL (the project was developed using PostgreSQL 18.7).
  • psql command-line client.
  • Python and Pandas only if you want to rerun the optional data-profiling and quality-audit scripts (the recorded environment used Python 3.13.13 and Pandas 3.1.1).
  • The original PaySim CSV downloaded from the Kaggle dataset page. For the optional Python data-quality audit scripts, create and activate a virtual environment, then install Pandas:
python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install pandas

1. Obtain the dataset

Download PS_20174392719_1491204439457_log.csv from Kaggle and place it at:

data/original/PS_20174392719_1491204439457_log.csv

The CSV is excluded from Git by .gitignore; do not commit the original dataset or local credentials.

2. Create the database

From the repository root, open psql and connect to a database where you have permission to create databases (for example, the default postgres database):

psql -U postgres -h localhost -p 5432 -d postgres

At the psql prompt, run:

\i sql/00_create_database.sql

Exit psql, then connect to the project database:

psql -U postgres -h localhost -p 5432 -d paysim_analytics

3. Create the transaction table

At the paysim_analytics psql prompt:

\i sql/01_create_transactions_table.sql

4. Import the original CSV

The import script uses \copy with a project-relative path:

data/original/PS_20174392719_1491204439457_log.csv

Start psql from the repository root before running the import. This is important because \copy reads the CSV through the local PostgreSQL client, and the relative path is resolved from the client's current working directory.

At the paysim_analytics psql prompt, execute:

\i sql/02_import_transactions.sql

The import script appends rows; it does not truncate the table. Run it only once against an empty public.transactions table. Running it again will create duplicate copies of the imported records.

Check the imported row count:

SELECT COUNT(*) FROM public.transactions;

Expected row count: 6362620.

5. Run the analytical SQL files

Run the Q1–Q10 scripts from the repository root, in question order:

\i sql/03_q1_transaction_volume.sql
\i sql/04_q2_transaction_monetary_analysis.sql
\i sql/05_q3_hourly_transaction_trends.sql
\i sql/06_q4_hourly_activity_ranking.sql
\i sql/07_q5_fraud_label_analysis.sql
\i sql/08_q6_fraud_flag_evaluation.sql
\i sql/09_q7_sender_activity_distribution.sql
\i sql/10_q8_recipient_transaction_concentration.sql
\i sql/11_q9_transaction_amount_distribution.sql
\i sql/12_q10_balance_movement_analysis.sql

6. Run the independent validation scripts

The validation scripts are separate from the analytical queries. Run the available Q2–Q10 validations after the corresponding analysis:

\i sql/validation/01_q2_monetary_reconciliation.sql
\i sql/validation/02_q3_hourly_transaction.sql
\i sql/validation/03_q4_hourly_activity_ranking.sql
\i sql/validation/04_q5_fraud_label_analysis.sql
\i sql/validation/05_q6_fraud_flag_evaluation.sql
\i sql/validation/06_q7_sender_activity_distribution.sql
\i sql/validation/07_q8_recipient_transaction_concentration.sql
\i sql/validation/08_q9_transaction_amount_distribution.sql
\i sql/validation/09_q10_balance_movement_validation.sql

The \i commands are psql meta-commands. Run them inside the psql terminal, not in a standard SQL query editor. Paths are relative to the current working directory from which psql was launched; start psql from the repository root or adjust the paths accordingly.

Important Dataset and Analytical Limitations

  • Synthetic data: PaySim simulates mobile-money transactions. Findings are not actual UPI, bank, merchant, or customer performance results.
  • Fraud labels: is_fraud contains simulated labels, not independently verified real-world fraud outcomes.
  • Fraud flag: is_flagged_fraud is a separate rule-based field. Precision and recall in this project measure agreement with the PaySim labels only.
  • Simulation time and observed steps: The PaySim research paper describes a 744-step simulation representing one month. The supplied CSV contains transactions across 743 observed step values (1–743). The analysis reports observed transaction steps and does not assume every configured simulation step contains transactions. Source: PaySim research paper.
  • Transaction identifiers: The original dataset does not provide a dedicated transaction ID. record_id is generated by PostgreSQL and is not an original PaySim transaction identifier.
  • Recipient classification: Q8 uses C and M identifier prefixes to form customer-coded and merchant-coded groups. These prefixes do not independently verify real-world identity.
  • Balance snapshots: Repeated activity to the same recipient within a simulation step can complicate transaction-level balance reconciliation. Exact intra-hour transaction ordering is not available.
  • Merchant balances: Merchant destination balance information is not available for the same recipient-side reconciliation used for tracked recipients.
  • Monetary units: Amounts are reported in the dataset's recorded units. Do not assume INR unless the source documentation establishes that interpretation.
  • Revenue and profit: Transaction amount is not platform revenue, fee income, profit, or financial benefit.
  • Variance tolerance: The 0.01 minor-variance threshold used in Q10 is a project-specific analytical tolerance, not a universal accounting standard.

Research Reference

Lopez-Rojas, E. A., Elmir, A., and Axelsson, S. (2016). PaySim: A Financial Mobile Money Simulator for Fraud Detection. 28th European Modeling and Simulation Symposium (EMSS 2016).

Project Status

Phase Status
Phase 1 — Setup & Planning Completed
Phase 2 — Data Understanding Completed
Phase 3 — Analytical Question Development Completed
Phase 4 — SQL Implementation Completed
Phase 5 — Results & Insights Completed
Phase 6 — Portfolio Publication In Progress

All ten analytical questions have been implemented. Results and interpretations have been documented, and independent validation has been completed for the analytical checks included in the project.

This repository is a practical SQL analytics portfolio project. Its purpose is to demonstrate structured data investigation, analytical SQL, independent validation, careful interpretation, and clear documentation—not to claim production payment-system or fraud-detection experience.

About

PostgreSQL analytics portfolio using PaySim synthetic mobile-money transactions: data-quality auditing, Q1–Q10 analysis, independent SQL validation, and documented findings.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages