I began this series with an operational sales database and a simple objective: demonstrate practical SQL through an end-to-end analytics project rather than a collection of disconnected syntax examples.
The project now includes eleven working stages. It explores and tests the source, creates a reusable analytical layer, investigates customer, product, regional, and time-based performance, applies more advanced analytical SQL, measures query improvements, and builds a validated dimensional model.
This final article reviews what that work produced, what the evidence suggests, and what still needs to happen before it resembles a production analytics solution.
This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.
SQL Sales Analytics Project Series
- Building a Sales Analytics Solution with SQL: Project Overview
- Understanding the Sales Data Landscape with SQL
- Assessing Sales Data Quality with SQL
- Preparing Sales Data for Analysis with SQL
- Analysing Customer Purchasing Behaviour with SQL
- Measuring Product Performance with SQL
- Analysing Regional Sales Performance with SQL
- Time-Series Analysis with SQL
- Using Advanced SQL to Generate Business Insights
- Optimising SQL for Analytical Workloads
- Designing an Analytics-Friendly Data Model
- Completing a SQL Sales Analytics Project ← You are here
Explore the Complete Project
The SQL sales analytics portfolio repository contains the complete runnable scripts and diagrams for the series. The implementation uses AdventureWorks2025 on SQL Server and was tested through SQL Server Management Studio.
There is no separate Article 12 SQL script. This article introduces no new calculation, so duplicating earlier queries would make the evidence harder to maintain. Findings link back to the exact supporting SQL that originally produced them.
What the Project Built
The work developed through five connected layers.
| Layer | Main outcome |
|---|---|
| Source understanding | Documented entities, relationships, grains, customer shapes, channels, and financial measures |
| Data trust | Reusable checks for completeness, uniqueness, validity, reconciliation, and referential integrity |
| Analytical access | Customer, order, and order-line views in a separate Analytics schema |
| Business analysis | Customer, product, regional, time-series, concentration, cohort, and performance investigations |
| Warehouse foundation | A project-owned AnalyticsDW star schema with dimensions, an order-line fact, validation, and diagrams |
The progression matters. I didn’t begin by imposing a warehouse structure on a database I hadn’t explored. Repeated analytical requirements revealed which grains, attributes, measures, and business definitions the dimensional model needed to support.
The website carries the narrative and interpretation. GitHub carries the complete scripts and assets required to reproduce the work. That division keeps the articles readable without hiding the implementation.
The Strongest Business Findings
AdventureWorks is sample data, so these findings aren’t recommendations for a real bicycle company. They show how I would move from SQL results to questions and priorities in a real engagement.
Customer value is highly concentrated
Only 39.07% of ordering customer accounts purchase more than once, but they account for nearly 94% of net sales value. Store accounts represent 3.32% of ordering accounts while generating almost three quarters of value. The repeat-purchase query and active-customer summary supply those findings.
The advanced analysis makes the concentration more explicit. It takes 1,368 accounts, or 7.85% of ordering accounts through the common cutoff, to reach 80% of net sales. The customer Pareto query calculates the threshold.
This supports careful account monitoring, particularly for high-value stores. It doesn’t support treating every account outside the leading group as unimportant. Customer type, margin, service cost, recency, and future potential remain missing from a simple value ranking.
Product roles differ depending on the measure
Bikes dominate sales value, while accessories appear on more orders than any other category. The ten highest-value SKUs contribute $31.01 million, or 28.23% of net sales, but the highest-volume product is the AWC Logo Cap rather than a bike. The category query, top-ten contribution query, and quantity ranking preserve those different perspectives.
The portfolio also contains 29 finished goods with no recorded sales. That result is a prompt for operational review, not an automatic deletion list. The unsold-product query keeps selling dates and availability context visible.
Geographic totals are shaped by customer mix
The United States contributes $63.00 million, or 57.35% of net sales, but concentration also exists below country and territory level. California supplies 64.00% of Southwest sales, while Washington supplies 58.58% of Northwest sales. The shipping-country query and state and province concentration query calculate those results.
Territory comparisons need separate customer and channel context because every individual order is online and every store order is salesperson-assisted in this sample. A change in regional customer mix can therefore look like a geographic performance change.
Recent growth is real in the data, but its cause isn’t established
Between the two complete calendar years, net sales rise 38.18% from 2023 to 2024. April 2025 reaches $5.22 million, the highest comparable month, and exceeds April 2024 by 106.18%. The annual comparison, monthly ranking, and latest-month comparison provide the evidence.
The result still needs qualification. Store data ends on 30 April 2025, while individual orders continue through June. April is therefore the final common company-wide cutoff. The history is also too short and commercially unstable to establish seasonality or forecast continued growth.
The analysis can locate concentration, growth, inactivity, and unusual cohort behaviour. Decisions still need margin, inventory, campaign, contract, service, and operational context that AdventureWorks doesn’t provide.
The Most Important Analytical Lesson
The recurring lesson is that grain and context matter more than query complexity.
Several plausible mistakes appeared throughout the project:
- Calling all 701 store-linked accounts without orders inactive businesses, even though 635 stores also have another account containing their orders
- Summing order-level values after joining them to multiple product lines
- Comparing stores and individuals without acknowledging that customer type and channel are perfectly aligned
- Treating May and June 2025 as comparable company-wide periods after store orders stop
- Calling three observations per calendar month a reliable seasonal pattern
- Treating an 80% Pareto threshold as a natural customer segment
- Reading a sample-data cohort discontinuity as evidence of improved retention
None of these problems is solved by adding a more advanced window function. They are solved by inspecting the source, declaring the population and grain, validating boundaries, retaining limitations, and asking whether the business interpretation follows from the data.
That is the kind of SQL work I wanted this portfolio to demonstrate.
What the Technical Work Demonstrates
The series uses SQL features because they help solve specific problems rather than to create a checklist.
Data exploration and validation
System catalogue queries expose table sizes, keys, indexes, constraints, and relationships. Aggregations and anti-joins test completeness, uniqueness, referential integrity, ranges, dates, and financial reconciliation.
Reusable analytical SQL
The Analytics schema centralizes customer classification, dates, channel labels, geography, product hierarchy, and financial measures across three declared grains. Validation confirms row preservation and reconciliation before downstream analysis depends on the views.
Analytical techniques
CTEs, temporary tables, LAG, running totals, moving averages, ranking, NTILE, cumulative contribution, and cohort windows support customer, product, regional, and time-based questions. Each technique is tied to an analytical purpose and an explicit limitation.
Measured performance work
A covering date index reduces one annual customer-summary query from 686 to 95 logical reads. Rewriting a function-wrapped date filter as a searchable range reduces another comparison from 139 to 13 reads. Querying at order grain avoids 274 unnecessary detail-page reads. The exact benchmark queries are in the performance script.
These are controlled local measurements, not universal index recommendations. The demonstration index is removed after the test.
Dimensional modelling
The AnalyticsDW model contains one fact row per source sales-order line and dimensions for date, customer, product, sales territory, and channel. It preserves all 121,317 source lines, resolves every fact to known dimension members, and reconciles $109,846,381.40 in net line value with no difference. The grain checks and financial reconciliation validate the load.
The articles explain why a decision was made and what the result means. The repository provides complete scripts, validation, and diagrams. Readers can understand the work without leaving the website and reproduce it without extracting fragments from the prose.
What Remains Unresolved
The project has a sound analytical foundation, but it shouldn’t be mistaken for a complete production platform.
- Profitability: Net sales value excludes product cost, service cost, returns, and margin.
- Historical dimensions: Customer and product attributes are current-state rather than effective-dated versions.
- Incremental ingestion: The dimensional model performs a protected initial load, not change capture or scheduled processing.
- Identity: Customer accounts aren’t verified unique people or businesses across channels and systems.
- Completeness contracts: The source doesn’t document why store orders end earlier or when each period should be considered closed.
- Operational metadata: Promotions, inventory availability, campaigns, contracts, and account-management actions aren’t sufficiently captured for causal explanations.
- Scale: AdventureWorks is too small to reproduce the concurrency, data volume, and operational pressure of a production warehouse.
- Governance: Ownership, access control, lineage, certification, retention, and monitoring need an organizational context.
These aren’t defects that should be hidden to make the portfolio look cleaner. They define the work required to move from a credible SQL project to a dependable analytics product.
The Next Phase: Fabric and Power BI
The dimensional model provides a natural bridge into the broader Microsoft Data and AI portfolio.
A practical next phase could include:
- 1Ingest AdventureWorks source data into Microsoft Fabric using repeatable pipelines.
- 2Implement incremental loading, audit records, rejected-row handling, and data-quality monitoring.
- 3Materialize the star schema in a Fabric Warehouse or Lakehouse rather than inside the OLTP database.
- 4Compare the custom model with AdventureWorksDW2025 and document meaningful design differences.
- 5Build a Power BI semantic model with explicit measures, date logic, hierarchies, formatting, and governed relationships.
- 6Create customer, product, regional, and executive report pages that retain the limitations discovered here.
- 7Add deployment, testing, lineage, security, and observability appropriate to a production-oriented solution.
The SQL work remains relevant throughout that progression. Fabric changes where ingestion, transformation, storage, and orchestration run. Power BI changes how governed measures and findings reach users. Neither removes the need to understand grain, source quality, business definitions, or query behaviour.
Final Recommendations
Protect high-value customer relationships without reducing the rest to a long tail
Monitor leading store accounts, lapsed value, product dependency, and regional concentration. Use customer-type-specific benchmarks and add margin before allocating service or retention investment.
Manage products using several measures
Value, quantity, order reach, customer reach, discounting, availability, and recent direction describe different roles. Add profitability, inventory, substitution, and strategic-range context before rationalising products.
Publish complete and segmented trends
Use explicit reporting cutoffs and separate store and individual views. Preserve both sales territory and shipping geography so commercial ownership isn’t confused with customer location.
Make validation part of every pipeline
Carry row counts, uniqueness checks, unknown-member checks, financial reconciliation, and period-completeness rules into the next platform rather than treating validation as a one-off SQL exercise.
Keep the dimensional model governed and extensible
Add slowly changing history, order-grain facts, incremental processing, and business-specific calendar attributes only when their definitions and consumers are clear.
Closing the SQL Phase
This project began with questions about a sales schema and ended with a working analytical foundation. The useful part wasn’t any single query. It was the sequence of understanding the source, testing assumptions, defining reusable logic, analysing the business, measuring performance, and reshaping the data for future reporting.
The result is reproducible and technically complete for its stated scope. It also remains honest about what the sample can’t establish.
That feels like the right place to close the SQL phase and begin the next one.