A SQL portfolio can easily become a collection of unrelated queries.
One query demonstrates a common table expression. Another uses a window function. A third ranks a list of products. Each example might be technically correct, but together they don’t show how the person writing them approaches a real analytical problem.
This series takes a different approach. I’m going to use SQL to build a sales analytics solution from an operational database, starting with the business questions and working through the decisions that make the eventual analysis trustworthy.
The project will cover data discovery, data quality, reusable analytical structures, customer and product analysis, regional performance, time-series analysis, query optimisation, and dimensional modelling.
The queries matter, but they’re only part of the work.
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 ← You are here
- 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
The Business Scenario
Adventure Works is a fictional manufacturer and retailer whose data covers products, customers, sales orders, employees, purchasing, and manufacturing.
For this project, I’ll treat the business as though its leadership wants a clearer view of sales performance but doesn’t yet have a single analytical layer designed to answer its questions.
The broad questions are familiar:
How is sales performance changing over time?- Which products and categories contribute most to revenue?
- Which customers buy repeatedly, and how does their behaviour differ?
- Where is the business performing well geographically?
- Are there trends or concentrations that deserve attention?
- Can decision-makers trust the underlying data?
Those questions sound straightforward. Answering them well requires more than joining a few tables and adding up sales values.
Before producing recommendations, I need to understand how the business process appears in the database, check the quality of the relevant data, establish consistent definitions, and make the analysis reproducible.
That sequence is deliberate. A polished chart doesn’t rescue a poorly understood metric.
Why Start with an Operational Database?
The first part of the series uses the AdventureWorks2025 OLTP database on SQL Server.
OLTP stands for online transaction processing. The database is designed to support day-to-day business activity such as recording customers, products, and sales orders. It isn’t arranged primarily for convenient analytical reporting.
That makes it a useful starting point.
In real projects, analysts often receive access to data shaped around operational processes rather than the questions they need to answer. Relevant information may be spread across several schemas and tables. Business meaning has to be reconstructed from keys, relationships, column definitions, and conversations with people who understand the process.
Starting with OLTP data lets this series demonstrate that work rather than beginning with an analysis-ready star schema where many of the difficult decisions have already been made.
Later in the series, I’ll explore how the operational model could be transformed into a dimensional model. AdventureWorksDW2025 gives us a useful reference point, but I want to reason through the design before comparing it with Microsoft’s prepared data warehouse.
Why AdventureWorks2025?
AdventureWorks is widely used, which comes with an obvious risk: it can feel like another tutorial database.
It also shouldn’t be treated as a blueprint for contemporary database design. The model has roots in an older generation of SQL Server samples, and the 2025 edition updates the database for the current SQL Server release without fundamentally redesigning its operational schema.
That limitation is part of the reason it works for this project. I am treating AdventureWorks as an inherited operational system: useful business data exists, but analysts still need to untangle relationships, clarify definitions, protect the grain of their queries, and create a more convenient analytical interface. Those are realistic responsibilities even when the source database isn’t one I would design today.
I’m using it because it supports the practical goals of the project:
It represents a recognisable manufacturing and retail business.- Its sales process is detailed enough to support meaningful analysis.
- Readers can download the same database and reproduce the work.
- The data is public, so the project doesn't depend on confidential client information.
- The OLTP and data warehouse variants create a natural path from operational analysis to dimensional modelling.
Microsoft provides AdventureWorks2025, AdventureWorksDW2025, and a smaller AdventureWorksLT2025 database. Its documentation recommends starting with the OLTP version when you’re unsure which model you need. The different editions make the sample easier to use with current SQL Server releases, but the version number shouldn’t be mistaken for a newly designed schema.
You can download and restore the database using Microsoft’s AdventureWorks installation guide. The underlying samples are available in Microsoft’s SQL Server samples repository, which is published under the MIT licence.
The implementation in this series will use T-SQL and SQL Server Management Studio. Some details will naturally be specific to SQL Server, but most of the analytical reasoning will transfer to other SQL platforms.
What This Project Will Produce
The main output will be a connected series of business-focused case studies rather than a long SQL tutorial split into arbitrary parts.
Each article will concentrate on a particular stage of the work and explain:
- the business problem being addressed
- why the analysis is useful
- how the relevant data is structured
- the reasoning behind the SQL approach
- what the results tell us
- what the business could do with those findings
The articles will contain the SQL needed to understand the approach. When a complete script, diagram, or supporting document would be useful but unnecessarily interrupt the article, I’ll publish it separately and link directly to it.
I don’t plan to duplicate every article in a GitHub repository. The SQL Sales Analytics Portfolio repository supports the analysis with complete scripts and diagrams rather than becoming a second version of the website.
Project Roadmap
The work is organised into five broad stages.
| Stage | Focus | What It Establishes |
|---|---|---|
| 1. Understand and trust the data | Explore the sales landscape and assess data quality | A clear view of the business process, key entities, relationships, and data risks |
| 2. Prepare for analysis | Create reusable views or reporting structures | Consistent joins, calculations, and business definitions |
| 3. Analyse business performance | Investigate customers, products, regions, and time-based trends | Evidence about purchasing behaviour, sales contribution, growth, and seasonality |
| 4. Deepen and improve the analysis | Apply advanced SQL and optimise analytical workloads | More sophisticated insight and measurable query improvements |
| 5. Design the next analytical layer | Build an analytics-friendly dimensional model and review the project | A foundation for future work in data warehousing, Microsoft Fabric, and Power BI |
The sequence is deliberate:
- Customer segmentation is much more convincing after the customer and order relationships have been understood.
- Performance tuning is more useful when there are realistic analytical queries to measure.
- Dimensional modelling makes more sense once the reporting needs and recurring calculations are visible.
How I’ll Approach the Analysis
Several principles will guide the work.
Business meaning comes before query complexity
A complicated query isn’t automatically valuable. I would rather use straightforward SQL to answer a relevant question than introduce an advanced feature without a practical reason.
CTEs, window functions, percentiles, running totals, and execution plans will appear when the problem calls for them.
Data quality is part of the analysis
Missing values, duplicate records, unexpected relationships, and inconsistent definitions can all change a result. These checks shouldn’t be treated as a brief technical obstacle before the interesting work begins.
Understanding whether the data supports a conclusion is part of producing that conclusion responsibly.
Findings need context
A list of top-selling products is descriptive, but it doesn’t yet tell the business what to do.
Useful analysis may need to consider contribution, trend, order frequency, product category, customer mix, discounting, or regional concentration. The right context will depend on the question, and some findings will lead to further investigation rather than an immediate recommendation.
The analytical layer should evolve from repeated needs
I don’t want to design a reporting model based only on assumptions made at the start. As the analysis develops, repeated joins, calculations, dimensions, and measures will show us what an analytics-friendly model needs to support.
That gives the later dimensional-modelling work a clear basis in actual reporting requirements.
What Success Looks Like
By the end of the series, I want the project to demonstrate more than familiarity with T-SQL.
It should show a complete line of reasoning:
- 1Understand the business problem.
- 2Learn how the operational data represents it.
- 3Test whether the data is trustworthy enough to use.
- 4Prepare consistent analytical structures.
- 5Apply SQL to answer relevant questions.
- 6Communicate findings with appropriate caveats.
- 7Improve the technical implementation.
- 8Design a better foundation for future analytics.
That is much closer to how SQL is used in practical analytics work. Syntax is necessary, but judgement is what turns a query into something useful.
Next in the Series
The next article will explore the AdventureWorks2025 sales data landscape.
I’ll identify the main business entities, trace how a sale moves through the operational schema, and establish which tables and relationships will support the questions in this project. That will give us the map we need before assessing data quality or writing analytical queries.