Product & Customers Report View

This repository contains the SQL definition and supporting documentation for the
gold.report_customers and gold.report_product view, an advanced analytics report built on a SQL Server database.
You may use MySQL database too.

Purpose

This view consolidates product-level metrics and behavioural KPIs to help business
stakeholders make data-driven decisions about product performance, resource allocation,
and strategic adjustments.

Key highlights:

• Gathers core product attributes (name, category, sub-category, cost)
• Segments products into High-Performer, Mid-Range, and Low-Performer by revenue
• Aggregates important metrics:
  • Total orders
  • Total sales
  • Total quantity sold
  • Unique customer count
  • Product lifespan (in months)
• Calculates key performance indicators (KPIs)(Key Performance Indicators):
  • Recency (months since last sale)
  • Average order value
  • Average monthly revenue

Pre-requisites

• Microsoft SQL Server (2012 or later)
• A database schema with:
  • gold.fact_sales — transactional sales data
  • gold.dim_products — product master data

Installation

1. Clone or download this repository.
2. Open the create_customer_view.sql, create_product_view.sql script in SQL Server Management Studio (SSMS).
3. Run the script to create or replace the gold.report_customers or gold.report_productsview.

## Creating a View for Cutsomers Reports

CREATE VIEW gold.report_customers AS
  -- (SQL definition here)
  -- (For SQL definition you may reach queries folder of the project, with that there is reports folder)

## Creating a View for Cutsomers Reports
CREATE VIEW gold.report_products AS
  -- (SQL definition here)
  -- (For SQL definition you may reach queries folder of the project, with that there is reports folder)


## Tools & Skills

List key tools used:

- Structured Query language 
Topics are used, joins (Left Join), left-anti join, right-anti join, full-anti join Common Table Expressions (CTEs), Also used 

- For Advanced analysis, used window functuons for finding the business trends, cumulative measures for Business performance analysis, running totals.
- Change-over-trends Analysis
- Cumulative Analysis
- Performace Analysis
- Part-to-whole Analysis
- Data-segmentation Analysis


Window functions :) 
- Aggregate Window Functions: Count(), Min(), Max(), Sum(), Avg() 
- Rank Window Functions: Row_number(), Rank(), Dense_rank(), ntile(), Percent_rank() : ((rank-1)/(Total rank-1)), Cume_dist()
- Value Window functions: Lag(), Lead(), First_value(), Last_value(), Nth_value(n)


Visualization (if used externally)

Data wrangling steps:

##  Pipeline Overview -----------------------------------------------------------------------------------------------------
- Strutured Query Language Analysis Pipeline:               
               
                        ┌──────────────────────┐
                        │  Raw dbo.fact_Sales  │
                        │  (transactions,      │
                        │   customers, etc.)   │
                        └────────┬─────────────┘
                                 │
                                 ▼
                    ┌────────────────────────────┐
                    │  Data Cleaning in SQL      │
                    │  (e.g., remove NULLs,      │
                    │   fix date formats,        │
                    │   filter bad data)         │
                    └────────┬───────────────────┘
                             │
                             ▼
                    ┌──────────────────────────────┐
                    │ Data Transformation & Joins  │
                    │ (e.g., aggregate monthly     │
                    │  sales, join with customer   │
                    │ demographics, calc. metrics) │
                    └────────┬─────────────────────┘
                             │
                             ▼
                ┌──────────────────────────────────────┐
                │ Segmentation & Insight Extraction    │
                │ (e.g., group customers by behavior,  │
                │   region-wise sales trend, churn     │
                │   indicator flags)                   │
                └──────────────────┬───────────────────┘
                                   │
                                   ▼
                        ┌────────────────────┐
                        │ Final Output Views │
                        │ (for reporting, BI │
                        │ dashboards, or     │
                        │ export to CSV)     │
                        └────────────────────┘

Brief steps like: Raw Data → Cleaning → Transformation → Segmentation → Output

## Sample Queries (Optional but impressive!)

SELECT customer_id, COUNT(order_id) 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(order_id) > 5;

## Key Results

Include metrics or outcomes like:
“40% of revenue comes from 20% of customers (Pareto pattern)”
“Identified top 5 products with highest repeat sales”

## Conclusion / Recommendations

Summarize the impact:
“This analysis supports more targeted promotions and inventory forecasting.”