Northwind Trading Analysis

SQL + Pyhton project using CTEs, window functions, and aggregatied queries on the Northwind database.

Database

SQLite (Northwind) Tables: Customers, Orders, OrderDetails, Products, Employees, Shippers, Suppliers, Categories.

Queries - found in queries folder

Each query is a .sql file. There questions/analysis below are implemented.

1. Customer revenue ranking - DENSE_RANK() by total spend
2. Daily running total - SUM() OVER(ORDER BY OrderDate)
3. Month on month revenue change - LAG() with difference
4. Top 3 products per category - ROW_NUMBER() PARTITION BY
5. Employee sales vs company average - AVG() OVER()
6. Employee sales rank - RANK()
7. Customer order frequency rank - DENSE_RANK()
8. First and last order per customer - MIN/MAX OVER(PARTITION BY)
9. Product revenue rank within category - RANK() OVER(PARTITION BY)
10. Unordered products - LEFT JOIN with IS NULL

Run all queries with python (SQLalchemy engine)

ERD table diagram

Tech

• SQLite
• Python (pandas, sqlalchemy)
• SQL (window functions, CTEs, joins)