SQL Sales Analytics Library

10 production-grade SQL queries for B2B sales analytics — CTEs, window functions, cohort retention, and more.

Queries

#	Query	Technique
01	Revenue by Region	GROUP BY aggregation
02	Top Customers by LTV	Common Table Expression (CTE)
03	Monthly Cohort Retention	CTE + JULIANDAY date math
04	Sales Rep Ranking	RANK() OVER (PARTITION BY ...)
05	Rolling Avg Revenue Forecast	AVG() OVER (ROWS BETWEEN ...)
06	Product Category Mix	SUM() OVER () for percentage share
07	Order Frequency Segments	CASE expression segmentation
08	YoY Growth by Region	Self-join on yearly CTE
09	Average Order Value Trend	Moving average with window frame
10	Churn Risk Score	Recency-based CASE classification

Getting Started

# Clone the repo
git clone https://github.com/AItools-guru/sql-sales-analytics.git
cd sql-sales-analytics

# Run all queries (creates sales.db automatically)
python3 run_queries.py

The script creates an SQLite database, loads the schema and seed data (50 customers, 20 products, 8 reps, 500 orders), then executes all 10 queries with formatted output.

Dataset

Table	Rows	Description
customers	50	4 regions, 3 segments (Enterprise/SMB/Startup)
products	20	3 categories (Software/Hardware/Services), $50–$5,000
sales_reps	8	2 reps per region
orders	500	2024-01-01 to 2025-12-31, qty 1–20, discounts 0–25%

Tech Stack

• SQL dialect: SQLite (compatible with PostgreSQL/MySQL with minor syntax changes)
• Runtime: Python 3 + sqlite3 (standard library — zero dependencies)
• Techniques: CTEs, window functions (RANK, AVG, SUM OVER), cohort analysis, self-joins, CASE segmentation

Sample Output

See sample_output.md for formatted query results.

Author

Saurabh Shidhore — github.com/AItools-guru