Scalable E-Commerce Analytics Engine (PostgreSQL)

An enterprise-ready SQL analytics repository designed to extract operational insights, revenue distributions, and transactional trends from e-commerce datasets using relational data modeling.

---

Executive Summary

This repository contains structured SQL analytics scripts engineered to audit e-commerce platform performance. It covers foundational metrics, multi-table operational joins, and financial transaction summaries to support data-driven decision-making across business intelligence (BI) domains. And This project showcases production-level SQL engineering techniques including Window Functions with explicit framing, Recursive CTEs, Time-Series Velocity Analytics, Multi-Table Ledger Auditing, and Query Optimization (EXPLAIN ANALYZE).

---

Tech Stack & Database System

• Database Engine: PostgreSQL (v13+)
• Development Environment: VS Code, DBeaver, PostgreSQL CLI (psql)
• Languages: Advanced SQL (ANSI SQL compliant)
• Data Domain: E-Commerce Transactions (Customers, Orders, Order Items, Payments, Products, Sellers)

---

Repository Architecture

ecommerce-analytics/
├── advanced_data_analytics/
│   ├── 01_customer_segmentation.sql    # RFM Quintile Scoring & Regional Value Ranking
│   ├── 02_financial_reconciliation.sql  # Multi-table audit, payment variance & missing rec
│   └── 03_enterprise_ltv_engine.sql     # Moving window frames, time-deltas & running LTV ca
├── analytics/
│   ├── basic.sql                       # Foundation queries & baseline aggregation
│   ├── joins.sql                       # Relational multi-table joins & relational integrity
│   └── schema.sql                      # General revenue & sales summary scripts
│   └── import.sql                      # DDL Schema definitions & data import scripts
└── README.md                           # Technical documentation & project overview

PostgreSQL Installation & Database Setup (Ubuntu / Linux)

Run the following commands in your terminal to install PostgreSQL, configure the database service, and create your initial database:

Update local package index and install PostgreSQL along with contrib packages

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Start and enable the PostgreSQL service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Switch to the default postgres user and launch the psql interactive shell

sudo -i -u postgres psql

Inside the psql prompt, create your database and exit

\q

Download SQL Tools in EXTENSIONS and Click SQL Tools to Create Database in PostgreSQL Server

Create a Database

CREATE DATABASE ecommerce_analytics

• Click Add New Connections inside SQL Tools, SELECT PostgreSQL Database = ecommerce_analytics* and UserName = postgres
  
  
  
  
  

• Connection Options If needed set a password Click Test Connection and After Click Save Connection
  
  
  
  

• After Testing and Saving the Connection, Copy the Datasets using import.sql the Tables created in schema.sql
  Enter into the PostgreSQL using bash code
  
  

• Create delete_and_rerun.sql in advance. So that you rerun the code when needed
  
  

• Create your tables in the schema.sql file, to store all your Dataset's data to store in the created table
  
  

• Find Run on active connection on the top of your code engine and Click it to create the tables
  
  
  
  

• Import All Datasets into the Created Database Tables using Bash

  \i scripts/import.sql

  
  

• Go ahead and check that every table is created or not using viewtable.sql.
  
  

• You can start your analytics journey right from this step, go ahead and explore SQL
  
  

analytics/Exploratory & Core Analytics

The analytics/ directory contains foundational SQL modules dedicated to data validation, baseline aggregations, multi-entity relational joins, transaction pipeline maintenance, and overall financial performance reporting.

analytics/
├── basic.sql               # Baseline metrics, exploratory aggregation & integrity checks
├── financial_summary.sql   # Revenue accounting, order performance & high-level financial KPIs
├── joins.sql               # Relational entity mapping, multi-table joins & relational integrity

advanced_data_analytics/ Advanced Data Analytics Engine

The advanced_data_analytics/ directory contains production-grade SQL analytics models designed to solve enterprise-level data problems. These scripts leverage advanced PostgreSQL features—such as statistical window functions, cohort dynamics, complex CTE architectures, and transaction auditing—to transform raw transactional logs into actionable strategic insights.

advanced_data_analytics/
├── 01_customer_segmentation.sql    # RFM Quintile Scoring, Behavioral Clustering & Geographic Performance
├── 02_financial_reconciliation.sql  # Multi-Table Auditing, Payment Variance Detection & Exception Handling
└── 03_enterprise_ltv_engine.sql     # Cohort Dynamic Windowing, Inter-Purchase Time-Deltas & Cumulative LTV