Advanced SQL Forecast Analysis
Project Description
This project demonstrates advanced SQL techniques to analyze and compare forecast accuracy for customers across fiscal years 2020 and 2021. By leveraging common table expressions (CTEs), temporary tables, stored procedures, and views, the analysis delivers actionable insights for supply chain managers to identify trends, improve forecasting, and optimize decision-making.
Features 1. Fact Table Creation: • Combines sales and forecast data into a unified fact table. 2. Null Value Handling: • Replaces null values with zeros to ensure accurate data analysis. 3. Yearly Accuracy Reports: • Calculates net error, absolute error, and forecast accuracy percentages. 4. Customer and Market Insights: • Detailed analysis of customer-level and market-level forecast accuracy. 5. Comparison Reports: • Highlights customers with declining forecast accuracy between fiscal years. 6. Automation: • Includes stored procedures and reusable views for recurring tasks.
SQL Techniques Demonstrated • Data Integration: • Joins (LEFT JOIN, RIGHT JOIN) and unions for fact table creation. • Temporary Tables: • Efficient intermediate data storage for calculations. • Common Table Expressions (CTEs): • Simplifies complex queries. • Stored Procedures: • Automates calculations and generates dynamic reports. • Views: • Reusable query logic for faster and consistent analysis. • Error Metrics: • Calculates net and absolute error percentages for forecast accuracy.
Folder Structure
- Table Schemas
Contains SQL scripts to create tables used in the project: • fact_sales_monthly.sql • fact_forecast_monthly.sql • dim_customer.sql
- SQL Scripts
Step-by-step SQL scripts for data processing and analysis: • 01_fact_table_creation.sql • 02_null_handling_and_cleaning.sql • 03_forecast_accuracy_fiscal_year.sql • 04_forecast_accuracy_customer_analysis.sql • 05_forecast_comparison.sql • 06_stored_procedures_and_views.sql
- Sample Data
CSV files with sample datasets for testing: • fact_sales_monthly_sample.csv • fact_forecast_monthly_sample.csv • dim_customer_sample.csv
Setup Instructions
- Clone the Repository
git clone https://github.com/Mugeshgithub/Advanced_SQL_Forecast_Analysis.git
- Create the Database
Use the table creation scripts in the table_schemas folder to create the necessary tables:
mysql -u -p < database_name < table_schemas/fact_sales_monthly.sql mysql -u -p < database_name < table_schemas/fact_forecast_monthly.sql mysql -u -p < database_name < table_schemas/dim_customer.sql
- Load Sample Data
Populate the tables using the sample data files in the sample_data folder: • Use your SQL client or import the CSV files directly.
- Run SQL Scripts
Execute the SQL scripts in the sql_scripts folder in the following order: 1. 01_fact_table_creation.sql 2. 02_null_handling_and_cleaning.sql 3. 03_forecast_accuracy_fiscal_year.sql 4. 04_forecast_accuracy_customer_analysis.sql 5. 05_forecast_comparison.sql 6. 06_stored_procedures_and_views.sql
- Analyze Outputs
Review the results to gain insights into customer forecast accuracy and trends.
Results and Insights 1. Identified customers with declining forecast accuracy across fiscal years. 2. Highlighted top-performing customers and markets for targeted actions. 3. Automated repetitive tasks to improve efficiency.
Contact
For any queries, feel free to reach out at: • Email: [email protected] • GitHub Profile: https://github.com/Mugeshgithub