Introduction
Welcome to this extensive SQL knowledge hub focused on well-known Microsoft sample databases: AdventureWorks, AdventureWorksDW, and Northwind. Beyond presenting queries, we delve into reasoning, performance considerations, indexing strategies, and advanced patterns.
We start with fundamental queries, then gradually step into advanced techniques like CTEs, window functions, and advanced filtering. Additionally, we will explore indexing strategies, performance tuning, and the nuances of dimension/fact models.
Keep an eye out for resources that link to official documentation. Use the search below, bookmark queries you find important, and switch languages for a global learning experience.
AdventureWorks Queries (Basic)
Top 10 Employees by Last Year's Sales
This query aggregates last year's sales by employee, identifying top performers.
Explanation
Group by EmployeeID and filter for the previous year using a standard WHERE clause. Sort by total sales to rank employees.
Solution:
SELECT TOP 10 EmployeeID, SUM(Sales) AS TotalSales
FROM Sales
WHERE YEAR(SaleDate) = YEAR(GETDATE()) - 1
GROUP BY EmployeeID
ORDER BY TotalSales DESC;
AdventureWorks Queries (Advanced)
Year-over-Year Sales Growth by Region
Uses a CTE and window functions to compare current year sales to previous year by region.
Explanation
The CTE aggregates per year and region, then LAG() is used to find previous year's totals. Growth is calculated as a percentage. Performance can be improved with indexed views or columnstore indexes.
Consider adding a nonclustered index on (Region, SaleDate) to speed up grouping by year. Also consider query hints or indexing strategies discussed in official SQL tuning guides.
Solution:
WITH YearlySales AS (
SELECT Region, YEAR(SaleDate) AS SaleYear, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region, YEAR(SaleDate)
)
SELECT Region, SaleYear, TotalSales,
LAG(TotalSales,1,0) OVER (PARTITION BY Region ORDER BY SaleYear) AS PrevYearSales,
CASE WHEN LAG(TotalSales,1,0) OVER (PARTITION BY Region ORDER BY SaleYear) = 0 THEN NULL
ELSE ((TotalSales - LAG(TotalSales,1,0) OVER (PARTITION BY Region ORDER BY SaleYear))
/ (1.0 * LAG(TotalSales,1,0) OVER (PARTITION BY Region ORDER BY SaleYear))) * 100 END AS GrowthPercent
FROM YearlySales
ORDER BY Region, SaleYear;
Region-Category Drilldown with Partitioned Aggregates
Aggregates data by Region and ProductCategory, using window functions to show cumulative sales.
Explanation
This query partitions by Region and sorts by Category to compute cumulative totals. Useful for building funnel reports.
Consider columnstore indexes for large Fact tables. This can drastically improve aggregation performance in DW scenarios.
Solution:
SELECT Region, ProductCategory, SUM(SalesAmount) AS CategorySales,
SUM(SUM(SalesAmount)) OVER (PARTITION BY Region ORDER BY ProductCategory) AS CumulativeSales
FROM Sales
GROUP BY Region, ProductCategory
ORDER BY Region, CumulativeSales;
AdventureWorksDW Queries (Basic)
Total Sales by Product Category for the Current Year
Summarizes total sales amount by category.
Explanation
Filters by current year. Index on (SaleDate, ProductCategory) can help. Dimension tables join might be needed for more detail.
If ProductCategory is a dimension attribute, consider joining DimProductCategory for more descriptive names. Consider a covering index.
Solution:
SELECT ProductCategory, SUM(SalesAmount) AS TotalSales
FROM FactSales
WHERE YEAR(SaleDate) = YEAR(GETDATE())
GROUP BY ProductCategory
ORDER BY TotalSales DESC;
AdventureWorksDW Queries (Advanced)
Seasonal Sales Trends Using Window Functions
Compares each month's sales to a 3-month moving average.
Explanation
A CTE gets monthly sales. Then a window function computes a 3-month trailing average. Identify seasonal spikes or dips.
To handle large Fact tables, consider partitioned tables and incremental refresh strategies. Performance may hinge on proper indexing of SaleDate and pre-aggregated tables.
Solution:
WITH MonthlySales AS (
SELECT YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, SUM(SalesAmount) AS MonthlyTotal
FROM FactSales
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
)
SELECT SaleYear, SaleMonth, MonthlyTotal,
AVG(MonthlyTotal) OVER (ORDER BY SaleYear, SaleMonth ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS Prev3MoAvg,
CASE WHEN MonthlyTotal > AVG(MonthlyTotal) OVER (ORDER BY SaleYear, SaleMonth ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
THEN 'Above Trend' ELSE 'Below or On Trend' END AS TrendStatus
FROM MonthlySales
ORDER BY SaleYear, SaleMonth;
Northwind Queries (Basic)
Top 5 Customers by Number of Orders
Highlights frequent customers, useful for loyalty programs.
Explanation
Counting orders per customer and sorting by that count.
Consider indexing on CustomerID in the Orders table. For further analysis, join with Customer metadata to get full profiles.
Solution:
SELECT TOP 5 CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID
ORDER BY OrderCount DESC;
Northwind Queries (Advanced)
Supplier Order Distribution by Quarter (Pivot)
Uses PIVOT to analyze distribution of orders across quarters per supplier.
Explanation
After grouping orders by quarter, PIVOT transforms rows into columns for Q1-Q4. Useful for quick visual comparison in reports.
For large datasets, pivoting can be expensive. Consider pre-computing aggregates or using modern BI tools for pivot transformations.
Solution:
WITH SupplierQuarterly AS (
SELECT s.SupplierName, DATENAME(QUARTER, o.OrderDate) AS OrderQtr, COUNT(o.OrderID) AS QtrCount
FROM Suppliers s
JOIN Products p ON s.SupplierID = p.SupplierID
JOIN Orders o ON p.ProductID = o.ProductID
GROUP BY s.SupplierName, DATENAME(QUARTER, o.OrderDate)
)
SELECT SupplierName, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
FROM SupplierQuarterly
PIVOT (
SUM(QtrCount)
FOR OrderQtr IN ([1],[2],[3],[4])
) AS PivotTable
ORDER BY SupplierName;
Further Insights & Reading
From basic aggregations to CTEs and PIVOTs, these queries build your SQL fluency. Explore advanced indexing strategies, windowing, and ETL patterns. For more:
This query can be optimized by adding an index on SaleDate and EmployeeID. Consider partitioning large tables by year for better performance.