SQL Sales Analytics: Advanced Business Intelligence Queries

Developing complex T-SQL queries, window functions, common table expressions (CTEs), and pivot tables for sales performance reporting.

Problem Statement

Relational sales databases contain transaction data that requires structured aggregation to answer business questions:

• Ranking products and customer tiers without dataset mutation.
• Calculating month-over-month sales velocity and running revenue totals.
• Re-formatting category metrics using cross-tab pivot structures.
• Identifying margin erosion linked to promotional discount bands.

Entity Relationship Diagram (ERD)

Customers (1) -< Orders (M) >- (1) Products

Table Schemas

• Customers: customer_id (PK), customer_name, segment, city, state
• Products: product_id (PK), product_name, category, sub_category
• Orders: order_id (PK), order_date, customer_id (FK), product_id (FK), sales, quantity, discount, profit

Analytical Techniques & Matrix

Technique	Function / Syntax	Business Use Case
Product Ranking	ROW_NUMBER(), RANK(), DENSE_RANK()	Rank product performance within categories
Customer Segmentation	NTILE(4)	Divide customer base into spending quartiles
Growth Velocity	LAG(), LEAD()	Measure Month-over-Month (MoM) revenue growth
Cumulative Metrics	SUM() OVER (ORDER BY date)	Compute running revenue totals and cumulative %
Multi-Step Analytics	WITH CTE AS (...)	Chain customer aggregates to derive value tiers
Cross-Tab Matrix	PIVOT / UNPIVOT	Convert category rows into monthly column metrics

Project Structure

SQL_Sales_Project/
├── SQL_Sales_Project.sql       # Schema DDL, inserts, and foundational queries
├── complex_queries.sql         # T-SQL window functions, CTEs, and pivots
├── sqlite_queries.sql          # Standard ANSI SQL / SQLite compatible queries
├── query_results.md            # Verified query output snapshots
└── README.md

How to Execute

Microsoft SQL Server (T-SQL)

- Execute schema and setup
SQL_Sales_Project.sql

- Execute advanced analytics
complex_queries.sql

SQLite / PostgreSQL

- Execute cross-platform ANSI SQL
sqlite_queries.sql

Author

Sanman Kadam
MSc Statistics | Data Analyst