SQL Data Analyst Portfolio Project: Advanced Business Analytics & Reporting
A comprehensive guide to performing advanced business analytics, metric aggregation, data segmentation, and production-grade database reporting using T-SQL. This project transitions basic query mechanics into complex, production-ready analytical workflows designed for real-world business decision-making.
1. The Three Pillars of SQL Data Projects & Environment Architecture
Data projects executed within enterprise environments generally fall into three distinct analytical pillars. Understanding where a task sits within this framework dictates the query architecture, performance expectations, and database optimization techniques required.
Database Initialization Methods
To execute advanced analytics, you must establish an analytical schema (gold) containing fact and dimension tables (fact_sales, dim_customers, dim_products). There are three standardized methods to populate the local SQL Server instance:
Note: Executing the automated initialization script (
init_database.sql) ensures strict preservation of primary/foreign key relationships and implicit data type definitions acrossfact_sales,dim_customers, anddim_products.
2. Analyzing Trends & Seasonality: Change Over Time
Change Over Time analysis tracks how numerical business metrics evolve across continuous date dimensions. It is fundamental for identifying historical growth trajectories, macro trends, and periodic seasonal spikes.
The Mental Model & General Formula
To evaluate changes over time, aggregate a numerical metric while grouping by a date dimension truncated to a specific granularity (Year, Quarter, Month, Week).
Date Parsing & Formatting Functions
SQL Server (T-SQL) offers multiple methods to group and format dates, each carrying performance and sorting trade-offs:
SQL Implementation: Yearly & Monthly Aggregations
The following script extracts yearly macro metrics while explicitly handling missing operational dates:
-- Macro Level: Yearly Sales, Customer Acquisition, and Volume Trends
SELECT
YEAR(order_date) AS order_year,
SUM(sales_amount) AS total_sales,
COUNT(DISTINCT customer_key) AS total_customers,
SUM(quantity) AS total_quantity
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY YEAR(order_date)
ORDER BY order_year ASC;
To evaluate intra-year seasonality without year boundary constraints, group strictly by the calendar month:
-- Operational Level: Monthly Seasonality Analysis across all years
SELECT
MONTH(order_date) AS order_month,
SUM(sales_amount) AS total_sales,
COUNT(DISTINCT customer_key) AS total_customers
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY MONTH(order_date)
ORDER BY order_month ASC;
To retain both temporal continuity and month granularity across multiple years, use DATE_TRUNC:
-- Continuous Monthly Time-Series Analysis
SELECT
DATE_TRUNC('month', order_date) AS order_date_month,
SUM(sales_amount) AS total_sales,
COUNT(DISTINCT customer_key) AS total_customers
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_date_month ASC;
3. Progress Tracking: Cumulative & Windowed Aggregations
Cumulative Analysis aggregates data progressively over sequential periods. Instead of viewing each time bucket in isolation, cumulative aggregations maintain a continuous running tally to measure trajectory and overall growth.
Concept Mechanics: Standard vs. Cumulative Aggregation
The standard frame implicit in windowed cumulative aggregations spans from the start of the partition up to the active evaluation row:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SQL Implementation: Running Totals & Moving Averages
Using Window Functions (SUM() OVER(...) and AVG() OVER(...)), we compute continuous running metrics:
-- Monthly Running Total Sales and Moving Average Price Development
WITH MonthlyAggregations AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(sales_amount) AS monthly_sales,
AVG(sales_amount) AS avg_price
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
order_month,
monthly_sales,
-- Running Total across all historical months
SUM(monthly_sales) OVER (
ORDER BY order_month ASC
) AS running_total_sales,
-- Moving Average of transaction prices over time
AVG(avg_price) OVER (
ORDER BY order_month ASC
) AS moving_avg_price
FROM MonthlyAggregations
ORDER BY order_month ASC;
Resetting Cumulative Totals via Partitions
When evaluating annual performance targets, running totals must reset at the start of each fiscal year. Adding a PARTITION BY clause alters the window evaluation boundary:
-- Annual Resetting Running Total Sales
WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(sales_amount) AS total_sales
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
order_month,
total_sales,
SUM(total_sales) OVER (
PARTITION BY YEAR(order_month)
ORDER BY order_month ASC
) AS annual_running_total_sales
FROM MonthlySales
ORDER BY order_month ASC;
4. Benchmarking & Comparative Metrics: Performance Analysis
Performance Analysis compares an observed entity value (e.g., current product revenue) against a target baseline or historical benchmark (e.g., historical product average or prior year revenue).
Benchmarking Framework
Standard Windowing Techniques for Performance
- Aggregate Window Functions:
AVG(metric) OVER (PARTITION BY entity)computes structural benchmarks without forcing tabular re-aggregation. - Value Window Functions:
LAG(metric, offset) OVER (PARTITION BY entity ORDER BY date)retrieves historical row values relative to the current evaluation point.
SQL Implementation: Product Year-over-Year (YoY) & Benchmark Analysis
This query evaluates product sales against their historical averages while computing period-over-period delta flags:
WITH YearlyProductSales AS (
SELECT
YEAR(f.order_date) AS order_year,
p.product_name,
SUM(f.sales_amount) AS current_sales
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p
ON f.product_key = p.product_key
WHERE f.order_date IS NOT NULL
GROUP BY YEAR(f.order_date), p.product_name
)
SELECT
order_year,
product_name,
current_sales,
-- 1. Historical Average Performance for the product
AVG(current_sales) OVER (PARTITION BY product_name) AS avg_product_sales,
current_sales - AVG(current_sales) OVER (PARTITION BY product_name) AS diff_from_avg,
CASE
WHEN current_sales > AVG(current_sales) OVER (PARTITION BY product_name) THEN 'Above Avg'
WHEN current_sales < AVG(current_sales) OVER (PARTITION BY product_name) THEN 'Below Avg'
ELSE 'Average'
END AS avg_performance_flag,
-- 2. Prior Year Benchmark Performance (YoY)
LAG(current_sales, 1) OVER (
PARTITION BY product_name
ORDER BY order_year ASC
) AS prior_year_sales,
current_sales - LAG(current_sales, 1) OVER (
PARTITION BY product_name
ORDER BY order_year ASC
) AS diff_prior_year,
CASE
WHEN current_sales > LAG(current_sales, 1) OVER (PARTITION BY product_name ORDER BY order_year ASC) THEN 'Increase'
WHEN current_sales < LAG(current_sales, 1) OVER (PARTITION BY product_name ORDER BY order_year ASC) THEN 'Decrease'
ELSE 'No Change'
END AS yoy_change_flag
FROM YearlyProductSales
ORDER BY product_name ASC, order_year ASC;
5. Proportional Impact: Part-to-Whole Analysis
Part-to-Whole Analysis measures how much an individual business segment contributes to an overall metric. Expressing absolute values as relative percentages allows stakeholders to quickly isolate dominant revenue drivers or operational bottlenecks.
Mathematical Definition
Structural Pattern & Integer Division Prevention
In SQL Server, dividing two integers results in integer truncation (e.g., ). To return correct floating-point percentages, explicitly cast the numerator to DECIMAL or FLOAT.
SQL Implementation: Category Sales Breakdown
This query computes product category contributions against grand total sales using an empty window clause OVER():
WITH CategorySales AS (
SELECT
p.category,
SUM(f.sales_amount) AS total_sales
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p
ON f.product_key = p.product_key
GROUP BY p.category
)
SELECT
category,
total_sales,
-- Empty OVER() clause evaluates the aggregate across the entire dataset
SUM(total_sales) OVER() AS overall_sales,
ROUND(
(CAST(total_sales AS DECIMAL(18, 4)) / SUM(total_sales) OVER()) * 100, 2
) AS sales_percentage_numeric,
CONCAT(
CAST(ROUND((CAST(total_sales AS DECIMAL(18, 4)) / SUM(total_sales) OVER()) * 100, 2) AS VARCHAR),
'%'
) AS sales_percentage_formatted
FROM CategorySales
ORDER BY total_sales DESC;
Business Insight: If a single category (e.g.,
Bikes) generates ~69% of aggregate sales revenue while adjacent categories (Accessories,Clothing) produce negligible contributions, the firm faces over-reliance risk. Strategic decisions must focus on diversifying adjacent category revenue or fortifying core product supply chains.
6. Derived Dimensions: Advanced Data Segmentation
Data Segmentation groups underlying entities (e.g., products, customers) into custom categorical buckets based on underlying measure ranges. This transforms continuous metrics into categorical dimensions for high-level aggregation.
Pattern 1: Simple Single-Measure Product Cost Segmentation
WITH ProductCostSegments AS (
SELECT
product_key,
product_name,
cost,
CASE
WHEN cost < 100 THEN 'Below 100'
WHEN cost BETWEEN 100 AND 499.99 THEN '100 - 500'
WHEN cost BETWEEN 500 AND 999.99 THEN '500 - 1000'
ELSE 'Above 1000'
END AS cost_range
FROM gold.dim_products
)
SELECT
cost_range,
COUNT(product_key) AS total_products
FROM ProductCostSegments
GROUP BY cost_range
ORDER BY total_products DESC;
Pattern 2: Multi-Measure Customer Behavioral Segmentation
Segmenting customers by combining purchasing history span (lifespan in months) and aggregate monetary spend creates actionable behavioral cohorts:
WITH CustomerBehaviorMetrics AS (
SELECT
f.customer_key,
SUM(f.sales_amount) AS total_spend,
DATEDIFF(month, MIN(f.order_date), MAX(f.order_date)) AS lifespan_months
FROM gold.fact_sales f
GROUP BY f.customer_key
),
CustomerSegmentDefinitions AS (
SELECT
customer_key,
total_spend,
lifespan_months,
CASE
WHEN lifespan_months >= 12 AND total_spend > 5000 THEN 'VIP Customer'
WHEN lifespan_months >= 12 AND total_spend <= 5000 THEN 'Regular Customer'
ELSE 'New Customer'
END AS customer_segment
FROM CustomerBehaviorMetrics
)
SELECT
customer_segment,
COUNT(customer_key) AS total_customers,
SUM(total_spend) AS segment_total_spend,
ROUND(AVG(total_spend), 2) AS segment_avg_spend
FROM CustomerSegmentDefinitions
GROUP BY customer_segment
ORDER BY total_customers DESC;
7. Enterprise Reporting Architecture: Modular CTE Pipeline Strategy
Writing production-grade analytical queries requires strict structural modularity. Bundling calculations, joins, dynamic windows, and filters into a single monolithic block makes debugging difficult and reduces maintenance efficiency.
The 3-Layer Common Table Expression (CTE) Architecture
Structural Comparison
-- BAD PRACTICE: Monolithic Query with Embedded Logic
SELECT
c.customer_key,
SUM(s.sales_amount) / COUNT(DISTINCT s.order_number) AS aov -- Fragile & Unreadable
FROM gold.fact_sales s
JOIN gold.dim_customers c ON s.customer_key = c.customer_key
GROUP BY c.customer_key;
-- GOOD PRACTICE: Modular Multi-Layer CTE Pipeline
WITH base_query AS ( ... ),
customer_aggregations AS ( ... )
SELECT ... FROM customer_aggregations; -- Clean, Maintainable, Scalable
8. Comprehensive Customer 360 Report Implementation
The Customer 360 report consolidates transaction histories, demography, behavioral segmentation, and core operational KPIs into an enterprise-ready analytical view.
Required Key Performance Indicators (KPIs)
- Recency: Months elapsed between the customer's most recent order date and the current system date (
GETDATE()). - Average Order Value (AOV): Total dollar spend divided by total orders.
- Average Monthly Spend: Total dollar spend divided by active lifespan duration.
Divide-by-Zero Protection Rules
Analytical queries must gracefully handle zero values in denominators (e.g., customers with lifespan months or total orders):
Complete Production T-SQL Script
CREATE VIEW gold.report_customers AS
WITH base_query AS (
/*---------------------------------------------------------------------------
Step 1: Base Data Extraction & Atomic Calculations
---------------------------------------------------------------------------*/
SELECT
f.order_number,
f.product_key,
f.order_date,
f.sales_amount,
f.quantity,
c.customer_key,
c.customer_number,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
DATEDIFF(year, c.birthdate, GETDATE()) AS age
FROM gold.fact_sales f
LEFT JOIN gold.dim_customers c
ON f.customer_key = c.customer_key
WHERE f.order_date IS NOT NULL
),
customer_aggregations AS (
/*---------------------------------------------------------------------------
Step 2: Entity-Level Aggregations
---------------------------------------------------------------------------*/
SELECT
customer_key,
customer_number,
customer_name,
age,
COUNT(DISTINCT order_number) AS total_orders,
SUM(sales_amount) AS total_sales,
SUM(quantity) AS total_quantity,
COUNT(DISTINCT product_key) AS total_products,
MAX(order_date) AS last_order_date,
DATEDIFF(month, MIN(order_date), MAX(order_date)) AS lifespan_months
FROM base_query
GROUP BY
customer_key,
customer_number,
customer_name,
age
)
/*---------------------------------------------------------------------------
Step 3: Final KPI Derived Calculations & Business Segmentations
---------------------------------------------------------------------------*/
SELECT
customer_key,
customer_number,
customer_name,
age,
-- Age Demographics Segmentation
CASE
WHEN age < 20 THEN 'Under 20'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-39'
WHEN age BETWEEN 40 AND 49 THEN '40-49'
ELSE '50 and Above'
END AS age_group,
-- Customer Behavioral Segmentation
CASE
WHEN lifespan_months >= 12 AND total_sales > 5000 THEN 'VIP'
WHEN lifespan_months >= 12 AND total_sales <= 5000 THEN 'Regular'
ELSE 'New'
END AS customer_segment,
total_orders,
total_sales,
total_quantity,
total_products,
last_order_date,
lifespan_months,
-- KPI 1: Recency (Months since last activity)
DATEDIFF(month, last_order_date, GETDATE()) AS recency_months,
-- KPI 2: Average Order Value (AOV) with Divide-by-Zero Guard
CASE
WHEN total_orders = 0 THEN 0
ELSE ROUND(CAST(total_sales AS DECIMAL(18,2)) / total_orders, 2)
END AS avg_order_value,
-- KPI 3: Average Monthly Spend with Divide-by-Zero Guard
CASE
WHEN lifespan_months = 0 THEN total_sales
ELSE ROUND(CAST(total_sales AS DECIMAL(18,2)) / lifespan_months, 2)
END AS avg_monthly_spend
FROM customer_aggregations;
GO
9. Comprehensive Product Performance Report Implementation
The Product Performance Report delivers actionable product portfolio intelligence by merging cost metrics, volume contributions, pricing metrics, and revenue velocity into a database view.
Key Analytical Metrics
- Revenue Segmentation: Ranks products into performance tiers (
High Performer,Mid-Range,Low Performer). - Average Selling Price (ASP): Evaluates realized price per unit after discounts (
Total Revenue / Total Quantity). - Average Monthly Revenue: Measures sales velocity per product across its active lifespan.
Complete Production T-SQL Script
CREATE VIEW gold.report_products AS
WITH base_query AS (
/*---------------------------------------------------------------------------
Step 1: Core Base Selection & Joins
---------------------------------------------------------------------------*/
SELECT
f.order_number,
f.order_date,
f.customer_key,
f.sales_amount,
f.quantity,
p.product_key,
p.product_name,
p.category,
p.subcategory,
p.cost
FROM gold.fact_sales f
LEFT JOIN gold.dim_products p
ON f.product_key = p.product_key
WHERE f.order_date IS NOT NULL
),
product_aggregations AS (
/*---------------------------------------------------------------------------
Step 2: Product Aggregation Layer
---------------------------------------------------------------------------*/
SELECT
product_key,
product_name,
category,
subcategory,
cost,
COUNT(DISTINCT order_number) AS total_orders,
COUNT(DISTINCT customer_key) AS total_customers,
SUM(sales_amount) AS total_sales,
SUM(quantity) AS total_quantity,
MAX(order_date) AS last_order_date,
DATEDIFF(month, MIN(order_date), MAX(order_date)) AS product_lifespan_months,
-- Average Selling Price (ASP)
CASE
WHEN SUM(quantity) = 0 THEN 0
ELSE ROUND(CAST(SUM(sales_amount) AS DECIMAL(18,2)) / SUM(quantity), 2)
END AS avg_selling_price
FROM base_query
GROUP BY
product_key,
product_name,
category,
subcategory,
cost
)
/*---------------------------------------------------------------------------
Step 3: Final Analytical Transformations & Tiers
---------------------------------------------------------------------------*/
SELECT
product_key,
product_name,
category,
subcategory,
cost,
-- Product Revenue Tiers
CASE
WHEN total_sales > 50000 THEN 'High Performer'
WHEN total_sales BETWEEN 10000 AND 50000 THEN 'Mid-Range'
ELSE 'Low Performer'
END AS product_performance_tier,
total_orders,
total_customers,
total_sales,
total_quantity,
avg_selling_price,
last_order_date,
product_lifespan_months,
-- Recency: Months since product was last ordered
DATEDIFF(month, last_order_date, GETDATE()) AS recency_months,
-- Average Order Revenue
CASE
WHEN total_orders = 0 THEN 0
ELSE ROUND(CAST(total_sales AS DECIMAL(18,2)) / total_orders, 2)
END AS avg_order_revenue,
-- Average Monthly Revenue Velocity
CASE
WHEN product_lifespan_months = 0 THEN total_sales
ELSE ROUND(CAST(total_sales AS DECIMAL(18,2)) / product_lifespan_months, 2)
END AS avg_monthly_revenue
FROM product_aggregations;
GO
Consuming Analytical Views
Once database views are established within the database schema (gold.report_customers and gold.report_products), end-user analytical queries and BI dashboards (Power BI / Tableau) can query pre-calculated metrics directly without executing costly multi-table joins:
-- Direct Analytical Extraction from Persistent Reporting View
SELECT
customer_segment,
age_group,
COUNT(customer_key) AS customer_count,
SUM(total_sales) AS total_revenue,
ROUND(AVG(avg_order_value), 2) AS group_aov
FROM gold.report_customers
GROUP BY customer_segment, age_group
ORDER BY total_revenue DESC;
Summary and Key Takeaways
- Three Pillars of SQL Projects: Advanced business analytics relies on prior data warehousing and exploratory work, emphasizing complex window functions, dynamic grouping, conditional segmentations, and view persistence.
- Granularity Control in Change-Over-Time: Truncating dates via
DATE_TRUNCpreserves temporal continuity for time-series charts, while grouping by discreteYEAR()orMONTH()isolates structural trends and operational seasonality. - Dynamic Framing for Cumulative Metrics: Cumulative running totals rely on window frame mechanics (
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). AddingPARTITION BYenables annual metric resets. - Comparative Analysis via Window Benchmarks: Combining aggregate window functions (
AVG() OVER()) and positional value functions (LAG() OVER()) enables side-by-side performance flags (Above Avg,Increase,Decrease) without loss of row-level detail. - Risk Mitigation via Part-to-Whole Ratios: Evaluating segment contribution percentages (
SUM(Category) / SUM(Grand Total) OVER()) highlights operational dependencies and category risk (e.g., single-category revenue reliance). - Derived Dimensions from Measures: Custom
CASE WHENlogic allows numeric variables (cost, lifespan, historical spend) to be re-encoded into categorical analytical dimensions (age brackets, cost tiers, VIP behavioral cohorts). - Modularity via 3-Layer CTE Pipelines: Enterprise reporting queries should follow a structured execution path: Base Selection/Cleaning Layer Entity Aggregation Layer Derived KPI/View Layer.
- Defensive Query Construction: Production metrics requiring division (AOV, ASP, Monthly Spend) must encapsulate denominators within
CASE WHENchecks to prevent divide-by-zero database errors. - Reporting Persistence via Views: Abstracting modular reporting pipelines into optimized SQL Database Views (
CREATE VIEW) provides direct, low-latency access layers for downstream analytics tools and business stakeholders.