Overview
Adventure Works is Microsoft's fictional bicycle manufacturing company — and the gold standard sample database for learning SQL. It contains realistic relational data across sales, products, customers, employees, and purchasing, giving you a real-world schema to query against.
This guide focuses on the analytical side: understanding the schema, writing queries that answer business questions, building KPI dashboards in SQL, and creating reusable stored procedures. The skills here apply directly to roles in data analysis, business intelligence, and data engineering.
Prerequisites
Data model
All tables are in the Sales, Production, Person, and HumanResources schemas. Use USE AdventureWorks2019; to set the context.
Tutorial steps
Restore and explore the database
Download the Adventure Works backup from GitHub, restore it to SQL Server, then explore the schema using INFORMATION_SCHEMA to understand what tables and columns are available.
-- Restore from backup
RESTORE DATABASE AdventureWorks2019
FROM DISK = 'C:\backups\AdventureWorks2019.bak'
WITH MOVE 'AdventureWorks2019' TO 'C:\data\AW2019.mdf',
MOVE 'AdventureWorks2019_log' TO 'C:\data\AW2019.ldf';
-- Explore tables in the Sales schema
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'Sales'
ORDER BY TABLE_NAME;
Revenue analysis — monthly and yearly trends
Calculate total revenue over time. Use DATETRUNC or FORMAT to group by month or year. Compare year-over-year growth using LAG().
SELECT
YEAR(OrderDate) AS year,
MONTH(OrderDate) AS month,
COUNT(SalesOrderID) AS order_count,
SUM(TotalDue) AS revenue,
LAG(SUM(TotalDue)) OVER (
PARTITION BY MONTH(OrderDate)
ORDER BY YEAR(OrderDate)
) AS prev_year_revenue,
ROUND(
(SUM(TotalDue) - LAG(SUM(TotalDue)) OVER (
PARTITION BY MONTH(OrderDate)
ORDER BY YEAR(OrderDate)
)) / NULLIF(LAG(SUM(TotalDue)) OVER (
PARTITION BY MONTH(OrderDate)
ORDER BY YEAR(OrderDate)
), 0) * 100, 2
) AS yoy_growth_pct
FROM Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 0
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY year, month;
Top products and categories
Join order details to the product and category hierarchy to find best-sellers by revenue and quantity. Use RANK() to identify top performers.
SELECT
pc.Name AS category,
p.Name AS product,
SUM(sod.OrderQty) AS units_sold,
ROUND(SUM(sod.LineTotal), 2) AS revenue,
RANK() OVER (
PARTITION BY pc.Name
ORDER BY SUM(sod.LineTotal) DESC
) AS rank_in_category
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p ON sod.ProductID = p.ProductID
JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name, p.Name
ORDER BY revenue DESC;
Sales rep performance dashboard
Build a per-sales-representative summary showing revenue, order count, average deal size, and rank within their territory.
SELECT
p.FirstName + ' ' + p.LastName AS sales_rep,
st.Name AS territory,
COUNT(DISTINCT soh.SalesOrderID) AS orders,
ROUND(SUM(soh.TotalDue), 0) AS total_revenue,
ROUND(AVG(soh.TotalDue), 0) AS avg_order_value,
RANK() OVER (
PARTITION BY soh.TerritoryID
ORDER BY SUM(soh.TotalDue) DESC
) AS territory_rank
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID
JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE soh.SalesPersonID IS NOT NULL
GROUP BY p.FirstName, p.LastName, st.Name, soh.TerritoryID
ORDER BY total_revenue DESC;
Customer segmentation with RFM analysis
RFM (Recency, Frequency, Monetary) is a classic segmentation technique. Score each customer on how recently they bought, how often they buy, and how much they spend.
WITH rfm_base AS (
SELECT
CustomerID,
DATEDIFF(DAY, MAX(OrderDate), '2014-07-01') AS recency,
COUNT(SalesOrderID) AS frequency,
ROUND(SUM(TotalDue), 0) AS monetary
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT
CustomerID,
recency,
frequency,
monetary,
NTILE(5) OVER (ORDER BY recency) AS r_score,
NTILE(5) OVER (ORDER BY frequency) AS f_score,
NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm_base
ORDER BY monetary DESC;
Package reports as stored procedures
Wrap your most useful queries in stored procedures so they can be called easily from reporting tools or scheduled jobs. Add parameters for date range filtering.
CREATE PROCEDURE report.MonthlyRevenue
@StartDate DATE = '2012-01-01',
@EndDate DATE = '2014-12-31'
AS
BEGIN
SELECT
FORMAT(OrderDate, 'yyyy-MM') AS month,
COUNT(SalesOrderID) AS orders,
ROUND(SUM(TotalDue), 0) AS revenue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND OnlineOrderFlag = 0
GROUP BY FORMAT(OrderDate, 'yyyy-MM')
ORDER BY month;
END;
-- Call it
EXEC report.MonthlyRevenue @StartDate = '2013-01-01', @EndDate = '2013-12-31';