Querying Data with SQL Server

Database used: AdventureWorks (modified version) Technology: Microsoft SQL Server · Transact-SQL (T-SQL)

Table of Contents

1. Course Overview
2. Selecting Data

• 2.1 Overview and context
• 2.2 AdventureWorks Database
• 2.3 SQL Terminology
• 2.4 Demo: SELECT instruction
• 2.5 Demo: Filtering with WHERE

3. Aggregating Data

• 3.1 Aggregate Functions
• 3.2 Group data (GROUP BY)
• 3.3 Filtering groups (HAVING)
• 3.4 Sort results (ORDER BY)
• 3.5 Module Summary

4. Joining Data

• 4.1 Joins Overview
• 4.2 Cross Join
• 4.3 Inner Join
• 4.4 Demo: Inner Join
• 4.5 Outer Joins (LEFT, RIGHT, FULL)
• 4.6 Self Join
• 4.7 Module Summary

5. Cleaning Data

• 5.1 Text manipulation functions
• 5.2 Demo: Text manipulation functions
• 5.3 Data Types & CAST
• 5.4 NULL value management
• 5.5 Demo: ISNULL, COALESCE, NULLIF
• 5.6 Module Summary

6. Subqueries and CTEs

• 6.1 Subqueries
• 6.2 Demo: Subqueries
• 6.3 Common Table Expressions
• 6.4 Demo: CTEs
• 6.5 Pivoting and unpivoting data (PIVOT / UNPIVOT)
• 6.6 Demo: PIVOT and UNPIVOT
• 6.7 Module Summary

7. Window Functions

• 7.1 Introduction to Window Functions
• 7.2 The OVER clause
• 7.3 Ranking Functions
• 7.4 Demo: ROW_NUMBER()
• 7.5 Demo: RANK() and DENSE_RANK()
• 7.6 Demo: NTILE()
• 7.7 Aggregation functions with windowing
• 7.8 Demo: Window Aggregate Functions
• 7.9 Offset Functions
• 7.10 Demo: LAG, LEAD, FIRST_VALUE, LAST_VALUE
• 7.11 Module Summary

8. Demo files
9. Prerequisites and environment

1. Course Overview

SQL (Structured Query Language) is the programming language used to communicate with relational databases. It has been around for over 50 years and remains omnipresent in all areas related to data.

This course teaches how to retrieve data from relational databases using Transact-SQL (T-SQL), the SQL dialect used by Microsoft SQL Server products and services.

Topics covered

Target audience

• Data Analyst
• Business Analyst
• Product Manager

Note: This course does not cover creating databases or inserting or deleting data.

Prerequisites

• Familiarity with the SQL Server Management Studio (SSMS) interface
• Relational Database Basics

2. Selecting Data

2.1 Overview and context

SQL is a very popular programming language that helps uncover hidden information in data. Companies collect immense volumes of data which they organize and store in relational databases.

Fundamental concepts

• Relational database: organization of data into tables (relations)
• Table: represents a type of entity (e.g.: Product, Customer)
• Columns (attributes): characteristics of the entity (e.g.: ProductName, Price)
• Lines (tuples/rows): an instance of the entity
• RDBMS (RDBMS): software for importing, organizing and managing data
• Examples: Microsoft SQL Server, Oracle Database, MySQL
• Schema: logical container for database objects
• View and Stored Procedure: other objects that can be created in a database

SQL Tools by RDBMS

SQL dialects

• Standard SQL: follows ANSI and ISO standards (latest standard: SQL 2016)
• T-SQL (Transact-SQL): Microsoft SQL Server dialect with additional features
• PL/SQL: Oracle dialect with procedural capabilities

The concepts learned with T-SQL allow you to quickly adapt to other SQL variants.

2.2 AdventureWorks Database

The AdventureWorks database is provided by Microsoft, slightly modified for the educational purposes of this course. It represents a fictitious multinational company that sells bicycles and accessories.

Main tables

Relational diagram (simplified)

Customer (CustomerKey PK)
    |-- 1:N --> Orders (OrderKey PK, CustomerKey FK)
                    |-- 1:N --> OrderDetails (ProductKey FK)
                                    |-- N:1 --> Product (ProductKey PK)

Restore database

The database is provided as a .bak backup file. To restore it in SSMS:

1. Right click on Databases in the Object Explorer
2. Select Restore Database
3. Choose Device and navigate to the AdventureWorksDW.bak file
4. Click OK to restore

2.3 SQL Terminology

SQL statement categories

This course focuses on DML, particularly the SELECT statement.

Logical execution order of a SQL query

Although the code is written in the following syntactic order:

SELECT   -- 5e
FROM     -- 1er
WHERE    -- 2e
GROUP BY -- 3e
HAVING   -- 4e
ORDER BY -- 6e (dernier)

The order of logical processing by the SQL engine is:

1. FROM — Determines the data source (table(s))
2. WHERE — Filter individual rows (before grouping)
3. GROUP BY — Groups rows by common values
4. HAVING — Filter groups (after grouping)
5. SELECT — Selects or calculates the columns to return
6. ORDER BY — Sorts the final result

Important: A clause only has access to information already processed by previous clauses in logical order. This is why aliases defined in SELECT cannot be used in WHERE or HAVING.

Three-valued predicate logic

T-SQL uses ternary logic: an expression can be evaluated to:

• TRUE
• FALSE
• UNKNOWN (when a NULL value is involved)

SQL style rules

• SQL is case-insensitive: SELECT = select
• Ending statements with a semicolon (;) is recommended
• Spaces, newlines and tabs are equivalent for the engine
• Only the SELECT clause is mandatory in a statement

Fully Qualified Name

The complete shape of an object has four parts:

[Server].[Database].[Schema].[Object]
-- Exemple :
[MyServer].[AdventureWorks].[dbo].[Customer]

SQL Server allows top parts to be omitted if using the current connection and database.

2.4 Demo: SELECT Statement

Select specific columns

-- Colonnes spécifiques
SELECT FirstName, LastName, Education, Occupation
FROM Customer;

Select all columns with *

-- Toutes les colonnes (à éviter sur de grandes tables)
SELECT *
FROM Customer;

Best practice: Avoid SELECT * on large tables to improve performance and readability.

Aliasing columns (Aliasing)

-- Concaténation et alias
SELECT FirstName + ' ' + LastName AS FullName, Occupation
FROM Customer;

Limit results with TOP

-- 50% des enregistrements
SELECT TOP 50 PERCENT FirstName + ' ' + LastName AS FullName, Occupation
FROM Customer;

-- Les 10 premiers enregistrements
SELECT TOP 10 FirstName + ' ' + LastName AS FullName, Occupation
FROM Customer;

Eliminate duplicates with DISTINCT

-- Valeurs uniques de la colonne Occupation
SELECT DISTINCT Occupation
FROM Customer;

Without DISTINCT, all rows are returned, including duplicates. The Customer table contains over 80,000 records.

2.5 Demo: Filtering with WHERE

The WHERE clause allows you to filter rows by applying logical conditions.

Comparison operators

-- Différent de 'Sam'
SELECT * FROM Customer WHERE FirstName <> 'Sam';

-- Égal à 'Sam'
SELECT * FROM Customer WHERE FirstName = 'Sam';

-- Commence par 'Sam' (wildcard %)
SELECT * FROM Customer WHERE FirstName LIKE 'Sam%';

Logical operators AND, OR, NOT

-- AND : les deux conditions doivent être vraies
SELECT *
FROM Customer
WHERE FirstName LIKE 'Sam%'
  AND YearlyIncome >= 50000 AND YearlyIncome <= 100000;

-- BETWEEN : équivalent à >= ET <=
SELECT *
FROM Customer
WHERE FirstName LIKE 'Sam%'
  AND YearlyIncome BETWEEN 50000 AND 100000;

-- IN : liste de valeurs spécifiques
SELECT *
FROM Customer
WHERE FirstName LIKE 'Sam%'
  AND YearlyIncome IN (20000, 30000);

-- OR avec parenthèses pour forcer la priorité
SELECT *
FROM Customer
WHERE FirstName LIKE 'Sam%'
  AND (YearlyIncome < 50000 OR YearlyIncome > 100000);

Warning: Without parentheses, OR has lower priority than AND, which may produce unexpected results.

Summary of filter operators

3. Aggregating Data

3.1 Aggregate Functions

Aggregate functions operate on a set of rows and return a single synthetic value.

Main aggregation functions

Important: All aggregate functions except COUNT(*) ignore NULL values. This may impact average calculations.

Impact of NULLs on AVG

Consider 5 products with stocks: 10, NULL, 20, 30, 20

-- Résultat : 16  (SUM = 80, COUNT(*) = 5)
SELECT SUM(Stock) / COUNT(*) AS AvgManual FROM ...

-- Résultat : 20  (SUM = 80, COUNT(Stock) = 4, les NULL sont exclus)
SELECT AVG(Stock) AS AvgBuiltIn FROM ...

Use DISTINCT in aggregations

-- Compter les valeurs distinctes (exclut NULL)
SELECT COUNT(DISTINCT Stock) -- Résultat : 3  (valeurs distinctes : 10, 20, 30)

-- Somme des valeurs distinctes
SELECT SUM(DISTINCT Stock)   -- Résultat : 60  (10 + 20 + 30)

Behavior of MIN / MAX depending on data type

Scalar functions

Unlike aggregate functions, scalar functions operate on each row individually:

• UPPER(str) — Convert to uppercase
• LOWER(str) — Convert to lowercase
• YEAR(date), MONTH(date) — Extract year, month
• FORMAT(value, 'format') — Formats a value for display

T-SQL offers more than 200 scalar functions in the categories: strings, dates/times, mathematics, logic.

Scalar and aggregate functions can be used in SELECT, ORDER BY and HAVING.

3.2 Grouping data (GROUP BY)

The GROUP BY clause allows you to group rows sharing common values, then to apply aggregation functions on these groups.

GROUP BY Rules

1. Any column in SELECT that is not an aggregate function must appear in GROUP BY — otherwise an error occurs.
2. The order of results is not guaranteed without ORDER BY.

Example: Statistics by category and color

SELECT Category,
       Color,
       COUNT(*) AS Products,
       FORMAT(MAX(ListPrice), '0') AS Maximum_Price,
       FORMAT(MIN(ListPrice), '0') AS Minimum_Price,
       FORMAT(AVG(ListPrice), '0') AS Average_Price
FROM Product
GROUP BY Category, Color;

The Product table contains 397 products. A group is created for each unique combination of Category and Color.

3.3 Filtering groups (HAVING)

The HAVING clause filters groups created by GROUP BY, usually based on the result of an aggregation.

Difference between WHERE and HAVING

Example: Filter groups with average price > 100

SELECT Category,
       Color,
       COUNT(*) AS Products,
       FORMAT(MAX(ListPrice), '0') AS Maximum_Price,
       FORMAT(MIN(ListPrice), '0') AS Minimum_Price,
       FORMAT(AVG(ListPrice), '0') AS Average_Price
FROM Product
GROUP BY Category, Color
HAVING AVG(ListPrice) > 100 AND Category = 'Bikes';

Warning: You must repeat the calculation (AVG(ListPrice)) in HAVING, because the alias (Average_Price) does not yet exist at this stage of execution. This query returns 5 groups.

3.4 Sort results (ORDER BY)

The ORDER BY clause is the last to be executed in a query. It controls the order in which the result is displayed.

Default behavior

Without ORDER BY, SQL Server is free to return rows in any order (tables represent sets with no intrinsic order).

With ORDER BY, the result becomes an (ordered) cursor.

Syntax and options

-- Tri simple ascendant (par défaut)
SELECT Product, Subcategory, Color, ListPrice
FROM Product
ORDER BY Color;

-- Tri descendant avec multi-colonnes
SELECT Product, Subcategory, Color, ListPrice
FROM Product
ORDER BY Color, ListPrice DESC, Category;

Bad practice: Using ordinal positions (ORDER BY 3, 4 DESC) — it’s fragile and difficult to read.

TOP with ORDER BY

-- Top 10 produits les plus chers de couleur noire
SELECT TOP 10 Product, Subcategory, Color, ListPrice
FROM Product
ORDER BY Color, ListPrice DESC, Category;

-- WITH TIES : inclut les ex-æquos
SELECT TOP 10 WITH TIES Product, Subcategory, Color, ListPrice
FROM Product
ORDER BY Color, ListPrice DESC, Category;

WITH TIES guarantees that all products with the same price as the last one included in the TOP are also returned. This can produce more than 10 lines.

OFFSET FETCH: paging results

-- Ignorer les 5 premières lignes, puis récupérer les 5 suivantes
SELECT Product, Subcategory, Color, ListPrice
FROM Product
ORDER BY Color, ListPrice DESC, Category
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

OFFSET FETCH requires an ORDER BY clause. This is the ANSI standard method for pagination.

Special feature: Alias ​​in ORDER BY

ORDER BY is the only clause where aliases defined in SELECT can be used, because it is executed after SELECT.

3.5 Module Summary

4. Joining Data

4.1 Overview of joins

Joins allow you to create a complete view by combining data from different tables.

Database normalization

Relational databases use normalization to minimize duplication:

• Customer information and orders are in separate tables
• Tables are linked by relationships (e.g.: 1:N between Customer and Orders)

Logical processing phases of a join

During each join, three logical processing phases are applied:

1. Cartesian product — Each row in table A is combined with each row in table B
2. Filtering by join predicate — Only rows satisfying the ON condition are kept
3. Adding outer rows — For outer joins, unmatched rows are added with NULL

The number of phases executed determines the join type obtained.

4.2 Cross Join

The Cross Join only executes phase 1 (Cartesian product). Each row in table A is combined with each row in table B.

Table A (2 lignes) × Table B (3 lignes) = 6 lignes résultantes

SQL-92 syntax (recommended)

SELECT CustomerKey, ProductKey
FROM Customer
CROSS JOIN Product;

SQL-89 Syntax (legacy)

SELECT CustomerKey, ProductKey
FROM Customer, Product;

Both syntaxes produce the same result. SQL-92 syntax is preferred because it is less error prone.

Use cases

• Generate number tables
• Create large volumes of data for testing
• Limited use in common analyzes

Joining Customer and Product in AdventureWorks produces over 7 million rows.

4.3 Inner Join

The Inner Join executes phases 1 and 2: Cartesian product followed by filtering. It only returns rows with matches in both tables.

Venn diagram representation

  [  Customer  ]
        ( intersection )
           [  Orders  ]

The Inner Join only returns the intersection: customers who have placed orders.

SQL-92 syntax (standard)

SELECT *
FROM Customer AS c
INNER JOIN Orders AS o
    ON c.CustomerKey = o.CustomerKey;

SQL-89 syntax (old, predicate in WHERE)

SELECT *
FROM Customer AS c, Orders AS o
WHERE c.CustomerKey = o.CustomerKey;

Important points

• INNER is optional: JOIN alone is equivalent to INNER JOIN
• The order of the tables in FROM does not impact the result
• It is possible to join more than two tables simultaneously
• The ON condition can use different logical operators (=, <>, >, etc.)

4.4 Demo: Inner Join

Inspect tables

-- Inspecter les données
SELECT TOP 5 * FROM Customer;
SELECT TOP 5 * FROM Orders;

Basic join

-- Effectuer l'inner join
SELECT *
FROM Customer AS c
INNER JOIN Orders AS o
    ON c.CustomerKey = o.CustomerKey;

Without aliases, an ambiguity error occurs for columns with the same name in both tables (CustomerKey). The c and o aliases solve this problem.

Aggregation after join

-- Nombre de commandes et montant par client
SELECT c.CustomerKey, c.FirstName,
       COUNT(o.OrderKey) AS Orders,
       SUM(o.SalesAmount) AS Amount
FROM Customer AS c
INNER JOIN Orders AS o
    ON c.CustomerKey = o.CustomerKey
GROUP BY c.CustomerKey, c.FirstName
ORDER BY Orders DESC;

The customer with ID 11566 (April) has placed the most orders: 25 orders.

4.5 Outer Joins (LEFT, RIGHT, FULL)

The Outer Joins execute the 3 phases of logical processing. In addition to common rows, they include unmatched rows from the preserved table(s).

Types of Outer Joins

The OUTER keyword is optional. LEFT JOIN = LEFT OUTER JOIN.

Example: Customers without orders

-- Liste des clients qui n'ont passé aucune commande
SELECT c.CustomerKey, c.FirstName, o.OrderKey, o.SalesAmount
FROM Customer AS c
LEFT OUTER JOIN Orders AS o
    ON c.CustomerKey = o.CustomerKey
WHERE o.OrderKey IS NULL;

Comparison COUNT(*) vs COUNT(column)

-- Impact du COUNT avec les outer joins
SELECT c.CustomerKey, c.FirstName,
       COUNT(*) AS Count_All_Rows,
       COUNT(o.OrderKey) AS Count_Orders
FROM Customer AS c
LEFT OUTER JOIN Orders AS o
    ON c.CustomerKey = o.CustomerKey
WHERE o.OrderKey IS NULL
GROUP BY c.CustomerKey, c.FirstName;

COUNT(*) includes NULL rows (returns 1 per customer with no orders), while COUNT(o.OrderKey) returns 0 because OrderKey is NULL for customers with no orders.

4.6 Self Join

A Self Join consists of joining a table with itself. It supports all types of joins (CROSS, INNER, OUTER).

Mandatory rule

Table aliasing is mandatory in a self join to distinguish the two instances of the same table.

Typical use case: Employee/manager hierarchy

-- Liste des employés avec le nom de leur manager
SELECT e1.FirstName, e1.LastName, e2.FirstName, e2.LastName
FROM Employee e1
LEFT OUTER JOIN Employee e2
    ON e1.EmployeeKey = e2.ManagerKey;

The Employee table stores both employees and their managers (ManagerKey). The left outer join includes employees without a manager.

4.7 Module Summary

5. Cleaning Data

5.1 Text manipulation functions

Data quality is an essential prerequisite for any reliable analysis. A quality dataset has several characteristics:

Common Data Quality Issues

• Duplicates: same record present several times
• Inconsistent data: typos, upper/lower case, unnecessary spaces
• Incorrect data: wrong data types
• Missing data: NULL or absent values

T-SQL provides built-in functions to improve the appearance of data in results without changing the underlying data.

String manipulation functions

Wildcards for LIKE and PATINDEX

5.2 Demo: Text manipulation functions

-- Nettoyage de données textuelles : codes pays et noms de pays
SELECT
    UPPER(LTRIM(t.CountryCode)) AS CountryCode,
    REPLACE(TRIM(BOTH '*' FROM t.CountryName), 'Cansda', 'Canada') AS CountryName,
    COUNT(c.CustomerKey) AS Customers
FROM SalesTerritory AS t
    JOIN Customer AS c
    ON t.GeographyKey = c.GeographyKey
GROUP BY LTRIM(t.CountryCode), t.CountryName;

This script corrects: superfluous spaces (LTRIM), upper/lower case inconsistency (UPPER), asterisks at the beginning and end (TRIM), and the spelling mistake “Cansda” → “Canada” (REPLACE).

-- Concaténation, extraction et recherche de motifs
SELECT
    FirstName + ' ' + LastName AS FullName,
    CONCAT(FirstName, ' ', LastName) AS FullNameConcat,
    CONCAT_WS(' ', FirstName, LastName) AS FullNameConcatWS,
    LEFT(LastName, 1) AS Initial,
    RIGHT(LastName, 1) AS LastLetter,
    SUBSTRING(LastName, 3, 2) AS Subset
FROM Customer
WHERE PATINDEX('Sam%', FirstName) > 0
  AND PATINDEX('[wy]%', LastName) > 0;

PATINDEX returns the position (1-based) of the first occurrence of the pattern. If the pattern is not found, returns 0.

5.3 Data types and conversion (Data Types & CAST)

Categories of data types in SQL Server

Implicit vs explicit conversion

• Implicit conversion: SQL Server automatically converts according to data type precedence rules (e.g.: 'text' + 123 → 'texte123')
• Explicit conversion: use of dedicated functions

Conversion functions

Example: Correct incorrect sorting due to data type

-- Sans CAST : tri alphabétique incorrect (800 > 2000 comme chaîne)
SELECT Product, StandardCost
FROM Product
WHERE Subcategory = 'Road Bikes'
ORDER BY StandardCost DESC;

-- Avec CAST : tri numérique correct
SELECT
    Product,
    CAST(StandardCost AS FLOAT) AS StandardCost
FROM Product
WHERE Subcategory = 'Road Bikes'
ORDER BY StandardCost DESC;

5.4 Handling NULL values

NULL represents absence of value and should not be confused with 0 or an empty string.

Identify NULLs

Due to ternary logic, we cannot use = NULL:

-- INCORRECT
WHERE Status = NULL

-- CORRECT
WHERE Status IS NULL
WHERE Status IS NOT NULL

Options to handle NULLs

1. Exclude NULLs from analysis (with IS NOT NULL)
2. Replace NULLs with other values

NULL handling functions

5.5 Demo: ISNULL, COALESCE, NULLIF

-- ISNULL() : remplacer NULL par 'Past' dans le statut
SELECT TOP 10
    Product,
    EndDate,
    Status,
    ISNULL(Status, 'Past') AS Stage
FROM Product;

-- NULLIF() : remplacer 0 par NULL dans StartDate
SELECT
    Product,
    NULLIF(StartDate, 0) AS StartDate
FROM Product
WHERE StartDate = 0;

-- COALESCE() : utiliser le premier nom non-NULL disponible
SELECT TOP 10
    p.ProductKey,
    p.Product,
    COALESCE(Product, Model, SKU) AS ProductName,
    SUM(o.OrderQuantity) AS OrderQuantity
FROM OrderDetails o
    LEFT JOIN Product p
    ON o.ProductKey = p.ProductKey
GROUP BY p.ProductKey, p.Product, COALESCE(Product, Model, SKU)
ORDER BY OrderQuantity DESC;

COALESCE evaluates arguments from left to right and returns the first that is not NULL. If all are NULL, return NULL.

5.6 Module Summary

6. Subqueries and CTEs

6.1 Subqueries

A subquery is a query nested inside another query. It offers an alternative approach to combining data and performing intermediate manipulations.

Terminology

Classification by result type

self-contained subqueries are easier to debug because they can be executed independently.

6.2 Demo: Subqueries

Scalar subquery in WHERE

-- Filtrer les commandes dont la quantité dépasse la moyenne
-- Méthode statique (déconseillée)
SELECT OrderKey, Quantity
FROM Orders
WHERE Quantity > 2;

-- Méthode avec sous-requête (recommandée)
SELECT OrderKey, Quantity
FROM Orders
WHERE Quantity > (SELECT AVG(Quantity) FROM Orders)
  AND CustomerKey IN (SELECT CustomerKey FROM Customer WHERE Occupation = 'Management')
ORDER BY OrderKey;

The IN operator is required (and not =) when the subquery returns multiple values.

Correlated subquery for running total (Running Total)

-- Calcul du total cumulatif (running total) avec une sous-requête corrélée
SELECT
    OrderKey,
    Quantity,
    (SELECT SUM(Quantity)
     FROM Orders AS o2
     WHERE o2.OrderKey <= o1.OrderKey
       AND Quantity > (SELECT AVG(Quantity) FROM Orders)
       AND CustomerKey IN (SELECT CustomerKey FROM Customer WHERE Occupation = 'Management')) AS RunningTotal
FROM Orders AS o1
WHERE Quantity > (SELECT AVG(Quantity) FROM Orders)
  AND CustomerKey IN (SELECT CustomerKey FROM Customer WHERE Occupation = 'Management')
ORDER BY OrderKey;

Important: In a correlated subquery, all filters in the external query must be repeated in the subquery to work on the same data set.

6.3 Common Table Expressions

CTEs (Common Table Expressions) create a virtual temporary table defined before the main query with the WITH clause.

Advantages of CTEs vs subqueries

CTE Rules

1. Sorting impossible: the result of a CTE cannot be sorted (ORDER BY prohibited in the CTE)
2. Unique column names: each column must have a distinct name
3. No nesting: a CTE cannot contain other CTEs
4. Multiple CTEs: several CTEs can be declared with a single WITH, separated by commas
5. Cross-referencing: CTEs can reference each other

Syntax

WITH NomDuCTE AS
(
    -- Définition de la requête CTE
    SELECT ...
    FROM ...
),
DeuxiemeCTE AS
(
    SELECT ...
    FROM ...
)
-- Requête principale qui utilise les CTEs
SELECT ...
FROM NomDuCTE
    LEFT JOIN DeuxiemeCTE ON ...;

6.4 Demo: CTEs

-- CTE pour calculer le pourcentage des ventes par client par rapport au total de l'employé
WITH EmployeeSales AS
(
    SELECT EmployeeKey, SUM(SalesAmount) AS TotalEmployeeSales
    FROM Orders
    GROUP BY EmployeeKey
),
EmployeeCustomerSales AS
(
    SELECT EmployeeKey, CustomerKey, SUM(SalesAmount) AS EmployeeCustomerSales
    FROM Orders
    GROUP BY EmployeeKey, CustomerKey
)

SELECT
    cte1.EmployeeKey,
    CustomerKey,
    FORMAT(EmployeeCustomerSales, 'N0') AS CustomerSales,
    FORMAT(TotalEmployeeSales, 'N0') AS TotalEmployeeSales,
    FORMAT(EmployeeCustomerSales / TotalEmployeeSales, 'P2') AS PrctSales
FROM EmployeeCustomerSales AS cte1
    LEFT JOIN EmployeeSales AS cte2
    ON cte1.EmployeeKey = cte2.EmployeeKey
ORDER BY PrctSales DESC;

FORMAT(value, 'N0') formats with thousands separators, 0 decimal places. FORMAT(value, 'P2') formats as a percentage with 2 decimal places.

6.5 Pivoting and unpivoting of data (PIVOT / UNPIVOT)

PIVOT: Transform rows into columns

PIVOT summarizes data by transforming row values ​​into columns. The three internal stages are:

1. Data grouping
2. Dispersion on the columns
3. Aggregation of values

Syntax PIVOT

SELECT col1, [val1], [val2], [val3]
FROM source_table
PIVOT(
    AGGREGATE_FUNCTION(valeur_column)
    FOR colonne_à_pivoter IN ([val1], [val2], [val3])
) AS alias_table_pivotée;

If the target column names are irregular (start with a number, contain spaces), surround them with brackets [].

UNPIVOT: Transform columns into rows

UNPIVOT performs the opposite operation: transforms column headers into row values.

UNPIVOT Syntax

SELECT col1, colonne_valeurs, colonne_noms
FROM source_table
UNPIVOT(
    colonne_valeurs FOR colonne_noms IN ([col1], [col2], [col3])
) AS alias;

PIVOT and UNPIVOT are operators specific to T-SQL (not available in all SQL dialects).

6.6 Demo: PIVOT and UNPIVOT

-- CTE de base : quantité par territoire et par année
WITH TerritoryYearQuantity AS
(
    SELECT
        o.SalesTerritoryKey,
        d.CalendarYear,
        SUM(Quantity) AS Quantity
    FROM Orders o
        LEFT JOIN Date d
        ON o.OrderDateKey = d.DateKey
    GROUP BY o.SalesTerritoryKey, d.CalendarYear
)

-- PIVOT : transformer les années en colonnes
SELECT
    SalesTerritoryKey,
    [2010], [2011], [2012], [2013], [2014]
FROM TerritoryYearQuantity
    PIVOT(SUM(Quantity) FOR CalendarYear IN ([2010], [2011], [2012], [2013], [2014])) AS PivotedData;

-- UNPIVOT : retransformer les colonnes d'années en lignes
WITH PivotedData AS
(
    SELECT SalesTerritoryKey,
           [2010], [2011], [2012], [2013], [2014]
    FROM (
        SELECT o.SalesTerritoryKey, d.CalendarYear, SUM(Quantity) AS Quantity
        FROM Orders o
        LEFT JOIN Date d ON o.OrderDateKey = d.DateKey
        GROUP BY o.SalesTerritoryKey, d.CalendarYear
    ) AS SourceTable
    PIVOT (SUM(Quantity) FOR CalendarYear IN ([2010], [2011], [2012], [2013], [2014])) AS PivotTable
)

SELECT *
FROM PivotedData
    UNPIVOT(Quantity FOR CalendarYear IN ([2010], [2011], [2012], [2013], [2014])) AS unpvt;

6.7 Module Summary

7. Window Functions

7.1 Introduction to Window Functions

Window Functions allow you to perform calculations on a set of lines (window) without losing the individual detail of each line — unlike GROUP BY which aggregates and makes the detail disappear.

GROUP BY limitation

With GROUP BY, it is impossible to display simultaneously:

• Detailed values of each command
• Total per customer
• The grand total

Solution with Window Functions

-- Sans window functions : complex et redondant
-- Avec window functions : simple et efficace
SELECT CustomerKey, OrderKey, SalesAmount,
    SUM(SalesAmount) OVER() AS GrandTotal,
    SUM(SalesAmount) OVER(PARTITION BY CustomerKey) AS CustomerTotal
FROM Orders;

Advantages of Window Functions

1. Simpler code and easy to debug
2. More efficient than subqueries (initial window = full query result)
3. Detail preserved: each line remains visible

Categories of Window Functions in SQL Server

Window Functions are executed after all clauses except ORDER BY. They cannot therefore be used directly in WHERE — you must use a CTE or a subquery.

7.2 The OVER clause

The OVER clause is the essential component of Window Functions. It defines the window specification.

Three elements of the OVER clause

fonction() OVER(
    [PARTITION BY colonne(s)]   -- 1. Partitionnement
    [ORDER BY colonne(s)]       -- 2. Ordonnancement
    [ROWS/RANGE BETWEEN ...]    -- 3. Cadrage (Framing)
)

1. PARTITION BY — Window partitioning

Divides the result into distinct partitions. The function is applied independently in each partition.

-- Fenêtre = toutes les lignes avec le même CustomerKey
SUM(SalesAmount) OVER(PARTITION BY CustomerKey)

-- Fenêtre = tout le résultat (OVER vide)
SUM(SalesAmount) OVER()

2. ORDER BY — Order in the window

Establishes the logical order of rows within each partition. Required for ranking functions and framing.

3. ROWS BETWEEN — Window framing

Refines the score lines by setting start and end points.

-- Total cumulatif : depuis le début jusqu'à la ligne courante
SUM(SalesAmount) OVER(
    PARTITION BY CustomerKey
    ORDER BY OrderKey
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

WINDOW clause (reuse)

To avoid repeating the same window specification, the WINDOW clause allows you to give it a name:

SELECT ProductKey, Product, Category, ListPrice,
    ROW_NUMBER() OVER W1 AS RowNumber,
    RANK()       OVER W1 AS Rnk,
    DENSE_RANK() OVER W1 AS Dense_Rnk
FROM Product
WINDOW W1 AS (PARTITION BY Category ORDER BY ListPrice DESC)

7.3 Ranking Functions

Ordering functions require an ORDER BY in the OVER clause.

Comparison of the four functions

Example with products sorted by descending price (price: 100, 100, 80, 70, 70):

ROW_NUMBER()

• Assign a unique number to each line, sequential, with no ties
• If the scheduling values are not unique → result non-deterministic
• For a deterministic result, add an additional column in ORDER BY

RANK()

• Assigns the same rank to ties
• Next row skips occupied positions (1, 1, 3, 4, 4, 6)
• RANK = number of lower values + 1

DENSE_RANK()

• Assigns the same rank to ties
• No skip in numbering (1, 1, 2, 3, 3, 4)
• DENSE_RANK = number of lower distinct values + 1

NTILE(n)

• Divides lines into n groups numbered 1 to n
• If the number of rows is not divisible by n, the first tiles receive an extra row

7.4 Demo: ROW_NUMBER()

-- Créer une liste avec le premier ordre de chaque client (via CTE)
WITH OrdersNumbered AS
(
    SELECT CustomerKey, OrderKey, SalesAmount,
        ROW_NUMBER() OVER(PARTITION BY CustomerKey ORDER BY SalesAmount) AS Row_Number
    FROM Orders
)

-- Premier ordre de chaque client
SELECT * FROM OrdersNumbered WHERE Row_Number = 1;

-- Deuxième ordre de chaque client
SELECT * FROM OrdersNumbered WHERE Row_Number = 2;

-- Lignes entre les positions 5 et 10 (pagination)
SELECT * FROM OrdersNumbered WHERE Row_Number BETWEEN 5 AND 10;

Important: ROW_NUMBER cannot be filtered directly in WHERE because Window Functions execute in SELECT. You must use a CTE or a subquery to then filter.

7.5 Demo: RANK() and DENSE_RANK()

-- Top 3 des produits les plus chers par catégorie (en tenant compte des ex-æquos)
WITH cte AS
(
    SELECT ProductKey, Product, Category, ListPrice,
        ROW_NUMBER() OVER W1 AS RowNumber,
        RANK()       OVER W1 AS Rnk,
        DENSE_RANK() OVER W1 AS Dense_Rnk
    FROM Product
    WINDOW W1 AS (PARTITION BY Category ORDER BY ListPrice DESC)
)

SELECT *
FROM cte
WHERE Dense_Rnk <= 3;

Using DENSE_RANK <= 3 ensures that all products in the top 3 distinct prices are included, even if multiple products share the same price.

7.6 Demo: NTILE()

-- Diviser les commandes d'un employé en 9 groupes selon le montant
SELECT
    OrderKey,
    SalesAmount,
    NTILE(9) OVER(ORDER BY EmployeeKey) AS Groups
FROM Orders
WHERE EmployeeKey = 292;

For employee 292 with 902 orders and 9 tiles requested, the first 2 groups contain 101 rows instead of 100 (902 = 9×100 + 2, so the first 2 tiles receive an extra row).

7.7 Aggregation functions with windowing

Window Aggregate Functions are the same functions as grouping aggregates (SUM, COUNT, AVG, MAX, MIN), but used with OVER instead of GROUP BY.

Key Difference

Support for OVER elements

Window Aggregate Functions support:

• PARTITION BY: restricts the window to identical lines
• ORDER BY: used for framing
• Framing (ROWS BETWEEN): determines the start and end points

Aggregate nesting

It is possible to nest a group aggregate function in a Window Function:

-- Agrégat de groupe : total par client
-- Window function : grand total de tous les clients
SELECT CustomerKey,
    SUM(SalesAmount) AS CustomerTotal,        -- Group aggregate
    SUM(SUM(SalesAmount)) OVER() AS GrandTotal -- Window aggregate sur les groupes
FROM Orders
GROUP BY CustomerKey;

7.8 Demo: Window Aggregate Functions

-- Prix moyen par sous-catégorie (PARTITION BY)
SELECT
    ProductKey,
    Product,
    Subcategory,
    ListPrice,
    AVG(ListPrice) OVER(PARTITION BY Subcategory) AS AvgPrice
FROM Product
ORDER BY Subcategory DESC;

-- Grand total, total client et total cumulatif
SELECT CustomerKey, OrderKey, SalesAmount,
    -- Grand total de toutes les commandes
    SUM(SalesAmount) OVER() AS GrandTotal,
    -- Total par client
    SUM(SalesAmount) OVER(PARTITION BY CustomerKey) AS CustomerGrandTotal,
    -- Total cumulatif par client (running total)
    SUM(SalesAmount) OVER(
        PARTITION BY CustomerKey
        ORDER BY OrderKey
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CustomerRunningTotal,
    -- Valeur de la commande précédente
    MAX(SalesAmount) OVER(
        PARTITION BY CustomerKey
        ORDER BY OrderKey
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS PreviousValue,
    -- Valeur de la commande suivante
    MAX(SalesAmount) OVER(
        PARTITION BY CustomerKey
        ORDER BY OrderKey
        ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
    ) AS NextValue
FROM Orders
ORDER BY CustomerKey, OrderKey;

• OVER() empty → window = all query result (grand total)
• OVER(PARTITION BY CustomerKey) → window = all orders from the same customer
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → cumulative total since the first row of the customer

7.9 Offset Functions

Offset Functions return line values ​​located at a defined distance from the current line in the window.

LAG and LEAD

• Offset: number of lines before/after (default: 1)
• Default: value returned if the requested line does not exist (default: NULL)

FIRST_VALUE and LAST_VALUE

Note: Without explicit framing, LAST_VALUE uses by default ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which returns the current value rather than the last one. You must always specify the framing for LAST_VALUE.

7.10 Demo: LAG, LEAD, FIRST_VALUE, LAST_VALUE

-- Valeurs précédente, suivante, première et dernière par client
SELECT
    CustomerKey,
    OrderKey,
    SalesAmount AS CurrentValue,
    -- 2 lignes avant la ligne courante, 0 si absent
    LAG(SalesAmount, 2, 0) OVER(PARTITION BY CustomerKey ORDER BY OrderKey) AS PreviousValue,
    -- 1 ligne après la ligne courante (offset par défaut = 1)
    LEAD(SalesAmount) OVER(PARTITION BY CustomerKey ORDER BY OrderKey) AS NextValue,
    -- Première valeur dans la partition du client
    FIRST_VALUE(SalesAmount) OVER(
        PARTITION BY CustomerKey
        ORDER BY OrderKey
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS FirstValue,
    -- Dernière valeur dans la partition du client
    LAST_VALUE(SalesAmount) OVER(
        PARTITION BY CustomerKey
        ORDER BY OrderKey
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS LastValue
FROM Orders
WHERE CustomerKey IN ('11566', '11242')
ORDER BY CustomerKey, OrderKey;

• LAG produces NULL for the first line (no previous line)
• LEAD produces NULL for last line (no next line)
• Specify an offset > 1 to access more distant lines

7.11 Module Summary

8. Demo files

Module 02 — Selecting Data

Module 03 — Aggregating Data

Module 04 — Joining Data

Module 05 — Cleaning Data

Module 06 — Subqueries and CTEs

Module 07 — Window Functions

9. Prerequisites and environment

Required software

Restoring the demo database

The AdventureWorksDW database is provided as a AdventureWorksDW.bak file.

Restore steps in SSMS:

1. Open SSMS and connect to SQL server
2. In the Object Explorer, right click on Databases
3. Select Restore Database…
4. In the restore window, choose Device as the source
5. Add the AdventureWorksDW.bak file via the … button
6. Click OK to start the restoration

Useful SSMS Shortcuts

Connection to server

• Server name is often auto-populated for a local instance
• Authentication: Windows Authentication for a local instance, or SQL Server Authentication for a remote instance

Search Terms

querying · data · sql · server · microsoft · databases · functions · window · syntax · join · columns · order · subqueries · ctes · database · pivot · unpivot · aggregate · aggregation · categories · clause · comparison · conversion · group