Table of Contents

1. Challenge

   Step 1: Foundations of Aggregation

   Step 1: Foundations of Aggregation

   Raw data can be overwhelming, thousands of rows, dozens of columns, and no immediate insights. That’s where aggregate functions come in. These tools help you summarize and explore large tables by turning row-level details into meaningful metrics.

   
   

   In this step, you’ll use basic aggregate functions like COUNT, SUM, AVG, MIN, and MAX to generate insights from the dbo.SalesOrderHeader table. This table contains all sales orders in the system, including dates, customers, total revenue, and more.

   
   

   Each task will help you build comfort with a specific aggregate:

   • Counting all records or only non-null values
   • Counting unique customers
   • Calculating total and average revenue
   • Identifying earliest and latest order dates

   
   

   By the end of this step, you’ll be able to summarize raw transactional data into clean, high-level statistics, a crucial skill for any data analyst or BI developer working in SQL.

   
   

   What You’ll Learn in This Step

   You’ll learn how to:

   • Use COUNT(*) to measure total rows in a table.
   • Use COUNT(column) to count only non-null values.
   • Use COUNT(DISTINCT column) to measure uniqueness.
   • Apply SUM() and AVG() to calculate revenue insights.
   • Use MIN() and MAX() to find earliest/latest dates.

   Note: These queries will run against a preloaded SQL Server database named ProductsDB. The dbo.SalesOrderHeader table contains one row per sales order.

   
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 1-step-one.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 1-step-one.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Understanding COUNT(*): Total Row Counts

   The COUNT() function in SQL is used to return the number of rows that match a specific condition. When used as COUNT(*), it counts all rows in a table, regardless of whether any columns contain NULL values.

   This function is useful when you want to measure the size of a dataset or verify that records exist. It performs a simple scan of the table and returns a single integer.

   Syntax

   SELECT COUNT(*) 
   FROM table_name;
   GO
   

   • COUNT(*): Counts every row in the result set
   • FROM table_name: Specifies the table you want to count rows from
   • GO: Signals the end of the batch when using SQL Server tools (like sqlcmd or the Code Lab environment)

   Key Notes

   • COUNT(*) is different from COUNT(column). The former counts all rows, even if some values are null. The latter ignores rows where the specified column is null.
   • It's a common technique to quickly gauge table size or validate that data loaded correctly.

   In this task, you'll apply COUNT(*) to count every sales order in the database.

   Understanding COUNT(column): Count Only Non-NULL Values

   The COUNT() function can also be used to count rows where a specific column is not null. When written as COUNT(column_name), SQL will ignore any rows where the given column contains a null value.

   This is useful for measuring how many entries have valid data in a specific field, such as dates, IDs, or amounts, and is a core technique in quality checks or conditional summaries.

   Syntax

   SELECT COUNT(column_name)
   FROM table_name;
   GO
   

   • COUNT(column_name): Returns the number of rows where the column is not null
   • GO: Marks the end of the batch and is required for execution in SQL Server environments

   In this task, you'll use a field called ShipDate, which has the datatype DATETIME. This column records when an order was shipped—but not every order will have a shipping date if it hasn’t shipped yet. Using COUNT(ShipDate) will exclude any rows where the shipping date is null. ### Using COUNT(DISTINCT column): Measuring Unique Values

   In SQL, the DISTINCT keyword is used to eliminate duplicate values from a result set. When combined with COUNT(), it allows you to count the number of unique, non-null entries in a column.

   This is useful when you need to understand variety or uniqueness in your data—such as the number of different customers, products, or regions represented.

   Syntax

   SELECT COUNT(DISTINCT column_name)
   FROM table_name;
   GO
   

   • DISTINCT column_name: Removes duplicate values before applying the count
   • COUNT(...): Returns the number of distinct, non-null values
   • GO: Required to complete the batch execution in SQL Server

   In this task, you’ll apply COUNT(DISTINCT ...) to the CustomerID column, which is an INT representing the customer who placed each order. This will help you determine how many unique customers have made purchases. ### Using SUM(): Calculating Total Values

   The SUM() function in SQL returns the total of all values in a given numeric column. It adds together each non-null value in the specified column and returns a single result.

   This function is commonly used to measure revenue, quantities, or any other cumulative metric in a dataset.

   Syntax

   SELECT SUM(column_name)
   FROM table_name;
   GO
   

   • SUM(column_name): Returns the total of all non-null values in that column
   • GO: Ends the command batch in SQL Server environments

   In this task, you'll use the SUM() function on the TotalDue column from the dbo.SalesOrderHeader table. The TotalDue column is a DECIMAL field representing the total amount due for each sales order. Your goal is to calculate the total revenue from all sales orders. ### Using AVG(): Calculating the Mean of Values

   The AVG() function in SQL calculates the average of all non-null values in a numeric column. It adds the values together and divides the total by the number of non-null entries.

   This function is often used to understand trends, spot outliers, or report typical transaction sizes.

   Syntax

   SELECT AVG(column_name)
   FROM table_name;
   GO
   

   • AVG(column_name): Calculates the mean value of the column, ignoring nulls
   • GO: Signals the end of the batch and allows the query to execute

   In this task, you'll apply the AVG() function to the TotalDue column in the dbo.SalesOrderHeader table. This column is a DECIMAL value that represents the total revenue for each order. The goal is to find the average amount customers are charged per order. ### Using MIN() and MAX(): Finding Extremes in a Column

   The MIN() and MAX() functions in SQL return the lowest and highest non-null values from a column, respectively. These are commonly used to identify boundaries in time, quantity, pricing, or any sortable metric.

   Both functions return a single value, and they can be used together in the same query to compare extremes.

   Syntax

   SELECT MIN(column_name), MAX(column_name)
   FROM table_name;
   GO
   

   • MIN(column_name): Returns the smallest non-null value
   • MAX(column_name): Returns the largest non-null value
   • GO: Completes the query batch for execution

   In this task, you’ll use MIN() and MAX() on the OrderDate column from the dbo.SalesOrderHeader table. This column is of type DATETIME and stores the date that each order was placed. By finding the earliest and latest values, you can determine the full time range covered by the sales data.

2. Challenge

   Step 2: Conditional Aggregation with `CASE`

   Step 2: Conditional Aggregation with CASE

   Not all rows in a dataset should be treated the same. Sometimes, you need to break totals into categories, apply different logic based on conditions, or isolate subsets of data inside an aggregation. This is where SQL’s CASE expression becomes powerful.

   
   

   In this step, you'll learn how to write conditional aggregations, where different values are counted or summed based on custom logic. You'll combine CASE with aggregate functions like SUM() and COUNT() to answer questions such as:

   • How much revenue came from North America compared to Europe?
   • How many order lines had discounts versus no discounts?

   
   

   By the end of this step, you’ll be able to use CASE inside aggregate functions to build dynamic, category-based summaries that respond to the data's structure and business logic.

   
   

   What You’ll Learn in This Step

   You’ll learn how to:

   • Use CASE expressions to apply logic inside aggregations.
   • Write conditional SUM() and COUNT() statements.
   • Return multiple custom aggregates in a single query.

   Note: The CASE expression works like an IF-THEN structure. It’s written inline inside SQL queries and can be used in SELECT, WHERE, and ORDER BY clauses.

   
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 2-step-two.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 2-step-two.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Conditional Aggregation Using CASE with SUM()

   The CASE expression can be used inside an aggregate function like SUM() to apply conditional logic. This allows you to total only the rows that meet specific criteria, while ignoring others.

   This pattern is useful when you want to return multiple category-based summaries in the same query, for example, summing values based on region, status, or any grouped condition.

   Syntax

   SELECT
     SUM(CASE WHEN condition THEN value ELSE 0 END)
   FROM table_name;
   GO
   

   • CASE WHEN condition THEN value ELSE 0 END: Evaluates each row and returns a value only if the condition is met
   • SUM(...): Adds all the returned values across the dataset
   • GO: Ends the batch for execution in SQL Server

   The CASE expression must always return a value, so it’s common to include ELSE 0 as the default return.

   In this task, you’ll use this technique to compare total revenue between two different regions. ### Conditional Counting Using CASE with COUNT()

   You can use a CASE expression inside the COUNT() function to create category-based counts. This allows you to count how many rows meet a condition versus how many do not, all within the same query.

   Unlike COUNT(column), which skips nulls, using CASE allows you to explicitly define what should be counted based on row-level logic.

   Syntax

   SELECT
     COUNT(CASE WHEN condition THEN 1 END),
     COUNT(CASE WHEN other_condition THEN 1 END)
   FROM table_name;
   GO
   

   • CASE WHEN condition THEN 1 END: Returns 1 for each matching row and null otherwise
   • COUNT(...): Counts all non-null values returned by the CASE expression
   • GO: Ends the batch for execution

   This technique is useful when comparing binary conditions, such as "with discount" versus "no discount", based on a numeric field.

3. Challenge

   Step 3: Group Data for Subtotals

   Step 3: Group Data for Subtotals

   When analyzing data, it is often not enough to look at overall totals. To uncover meaningful insights, you need to break data into smaller groups and calculate subtotals. In SQL, this is accomplished with the GROUP BY clause.

   
   

   The GROUP BY clause organizes rows into sets based on one or more columns. Once grouped, you can apply aggregate functions like SUM(), COUNT(), or AVG() to calculate values for each group instead of the entire table.

   
   

   By the end of this step, you will be able to produce detailed, subtotaled reports that highlight trends across different categories of data.

   
   

   What You’ll Learn in This Step

   In this step, you’ll learn how to:

   • Use the GROUP BY clause to calculate totals by territory.
   • Group by date parts such as year and month for time-based summaries.
   • Combine multiple grouping columns to analyze intersections (for example, sales by both territory and product category).
   • Apply aggregate functions like SUM(), COUNT(), and AVG() within grouped results.

   
   > **Note**: When you use `GROUP BY`, every column in the `SELECT` list must either be included in the `GROUP BY` clause or be part of an aggregate function.
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 3-step-three.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 3-step-three.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Grouping Data with GROUP BY

   The GROUP BY clause in SQL arranges rows into groups based on the values of one or more columns. Once grouped, aggregate functions like SUM(), COUNT(), or AVG() are applied to each group, instead of across the entire dataset.

   This is how you create subtotals, such as revenue per territory or orders per customer.

   Syntax

   SELECT column_name, AGGREGATE_FUNCTION(other_column)
   FROM table_name
   GROUP BY column_name;
   GO
   

   • column_name: The field used to group rows
   • AGGREGATE_FUNCTION(...): Calculates a subtotal for each group
   • GROUP BY: Applies the grouping logic
   • GO: Marks the end of the batch for execution in SQL Server

   In this task, you’ll group sales orders by TerritoryID from the dbo.SalesOrderHeader table to calculate the total revenue (TotalDue) per territory. The TerritoryID column is an INT that links to the dbo.SalesTerritory table. ### Grouping by Date Parts

   The GROUP BY clause is not limited to direct column values. It can also use expressions, including functions that extract parts of a date. This allows you to calculate subtotals over time periods such as year, month, or day.

   SQL Server provides date functions such as:

   • YEAR(date_column): Returns the year from a date
   • MONTH(date_column): Returns the month number from a date

   By grouping on these values, you can calculate totals by year, month, or both.

   Syntax

   SELECT YEAR(date_column), MONTH(date_column), AGGREGATE_FUNCTION(other_column)
   FROM table_name
   GROUP BY YEAR(date_column), MONTH(date_column);
   GO
   

   • YEAR(date_column): Extracts the year from each row
   • MONTH(date_column): Extracts the month from each row
   • GROUP BY: Ensures aggregation happens per year and month
   • GO: Marks the end of the batch

   In this task, you will group the sales data from dbo.SalesOrderHeader by year and month of the OrderDate column to calculate total revenue (TotalDue) per month. ### Grouping by Multiple Columns

   The GROUP BY clause can use more than one column at the same time. When multiple columns are listed, SQL creates groups based on the combination of their values. This allows you to calculate subtotals for intersections of categories, such as sales by both region and product category.

   Syntax

   SELECT column1, column2, AGGREGATE_FUNCTION(other_column)
   FROM table_name
   GROUP BY column1, column2;
   GO
   

   • column1, column2: Defines the grouping dimensions
   • AGGREGATE_FUNCTION(...): Calculates subtotals for each combination
   • GROUP BY: Applies grouping across multiple columns
   • GO: Ends the batch for execution

   In this task, you will calculate sales totals for each product category within each sales territory. This requires joining three tables:

   • dbo.SalesOrderHeader (contains orders with TerritoryID)
   • dbo.SalesOrderDetail (contains order lines with ProductID and LineTotal)
   • dbo.ProductCategory (contains product categories)

   The joins allow you to match orders with their territories and categories before grouping the results.

4. Challenge

   Step 4: Filter Groups with `HAVING`

   Step 4: Filter Groups with HAVING

   After using GROUP BY to calculate subtotals, you may want to return only certain groups that meet a specific condition. While the WHERE clause filters rows before grouping, the HAVING clause filters groups after aggregation.

   This allows you to restrict results based on totals, averages, or counts that you calculated for each group.

   
   

   In this step, you’ll work with grouped sales data and learn how to:

   • Use HAVING to filter groups by aggregated values.
   • Apply thresholds to totals and counts.
   • Combine WHERE and HAVING in the same query for precise filtering.

   
   

   By the end of this step, you’ll be able to control which groups appear in reports by filtering on aggregated metrics.

   
   

   What You’ll Learn in This Step

   You’ll learn how to:

   • Apply the HAVING clause to grouped queries.
   • Filter groups based on aggregate conditions.
   • Combine WHERE for row-level filtering with HAVING for group-level filtering.

   
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 4-step-four.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 4-step-four.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Filtering Groups with HAVING

   The HAVING clause is used to filter grouped results after aggregate functions are applied. It is similar to the WHERE clause, but WHERE filters rows before grouping, while HAVING filters groups after aggregation.

   This makes HAVING essential when you want to include only groups that meet certain thresholds, such as totals above a value or counts greater than a number.

   Syntax

   SELECT column_name, AGGREGATE_FUNCTION(other_column)
   FROM table_name
   GROUP BY column_name
   HAVING AGGREGATE_FUNCTION(other_column) condition;
   GO
   

   • GROUP BY: Creates groups to aggregate
   • HAVING: Applies conditions to aggregated results
   • GO: Ends the batch for execution

   In this task, you’ll filter grouped sales data to return only territories with high revenue totals. ### Combining WHERE and HAVING

   The WHERE clause filters individual rows before grouping, while the HAVING clause filters groups after aggregation. You can use both in the same query to apply conditions at different stages of the query process.

   • Use WHERE when you want to limit which rows are included in the grouping.
   • Use HAVING when you want to filter the aggregated groups after the grouping has been applied.

   Syntax

   SELECT column_name, AGGREGATE_FUNCTION(other_column)
   FROM table_name
   WHERE row_condition
   GROUP BY column_name
   HAVING AGGREGATE_FUNCTION(other_column) condition;
   GO
   

   In this task, you will first filter orders by year using WHERE, then use HAVING to filter grouped results by count.

5. Challenge

   Step 5: Analyze Data with Window Functions

   Step 5: Analyze Data with Window Functions

   Aggregate functions like SUM() and COUNT() summarize data at the group level, but they remove row-level detail. Window functions solve this by allowing you to apply aggregates across partitions of data while still returning individual rows.

   Window functions use the OVER() clause, which defines how rows are partitioned and ordered for the calculation. This makes it possible to compare each row against group totals, calculate running sums, or assign rankings without losing detail.

   
   

   In this step, you’ll work with window functions to:

   • Display totals alongside individual rows.
   • Calculate running totals based on order dates.
   • Rank products within categories based on sales.

   
   

   By the end of this step, you’ll be able to enhance queries with analytical calculations that preserve row-level detail.

   
   

   What You’ll Learn in This Step

   You’ll learn how to:

   • Use OVER(PARTITION BY ...) to compare rows against group totals.
   • Use OVER(ORDER BY ...) to calculate running totals.
   • Apply ranking functions like DENSE_RANK() to order results within groups.

   
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 5-step-five.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 5-step-five.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Using SUM() with OVER(PARTITION BY ...)

   A window function applies an aggregate calculation across a set of rows related to the current row, while still returning each row in the result. The OVER() clause defines the "window" of rows used in the calculation.

   When combined with PARTITION BY, the dataset is divided into groups (partitions), and the function is applied to each group. Unlike GROUP BY, window functions do not collapse rows, so detail is preserved.

   Syntax

   SELECT column_name,
          AGGREGATE_FUNCTION(value) OVER(PARTITION BY column_name)
   FROM table_name;
   GO
   

   • AGGREGATE_FUNCTION(value): The calculation to apply
   • OVER(PARTITION BY column_name): Defines the group each row belongs to
   • GO: Ends the batch for execution

   In this task, you’ll use SUM(TotalDue) OVER(PARTITION BY TerritoryID) on the dbo.SalesOrderHeader table to display each order alongside the total revenue for its territory.

   Using SUM() with OVER(ORDER BY ...)

   Window functions can also be used with the ORDER BY clause inside OVER() to calculate running totals. A running total adds values in sequence, based on the defined order, so each row shows the cumulative sum up to that point.

   This is commonly used in time-based analysis, where you want to see totals accumulated across dates.

   Syntax

   SELECT column_name,
          AGGREGATE_FUNCTION(value) OVER(ORDER BY column_name)
   FROM table_name;
   GO
   

   • AGGREGATE_FUNCTION(value): The calculation to apply
   • OVER(ORDER BY column_name): Defines the order in which the cumulative calculation is performed
   • GO: Ends the batch for execution

   In this task, you’ll use SUM(TotalDue) OVER(ORDER BY OrderDate) on the dbo.SalesOrderHeader table to calculate a running total of sales across order dates.

   Ranking with DENSE_RANK() and OVER(PARTITION BY ...)

   SQL provides ranking functions that assign a rank to each row within a partition. The DENSE_RANK() function gives consecutive ranks without gaps, even when multiple rows share the same value.

   When combined with PARTITION BY, you can restart the ranking for each group, such as per category. Adding ORDER BY inside the OVER() clause defines the order used to assign ranks.

   Syntax

   SELECT column_name,
          DENSE_RANK() OVER(PARTITION BY group_column ORDER BY value_column DESC) AS Rank
   FROM table_name;
   GO
   

   • DENSE_RANK(): Assigns ranking values without gaps
   • PARTITION BY: Restarts the rank for each group
   • ORDER BY value_column DESC: Determines ranking order (highest first)
   • GO: Ends the batch for execution

   In this task, you’ll calculate total sales per product, then rank products by sales within each product category. This requires joining order details, products, and product categories.

6. Challenge

   Step 6: Build a Multi-layered Query

   Step 6: Build a Multi-layered Query

   Individually, aggregates, grouping, filtering, and window functions are powerful. Combined, they allow you to build advanced queries that reveal deep insights from data. One of the most effective ways to structure multi-step logic in SQL is with a Common Table Expression (CTE).

   A CTE, created with the WITH keyword, lets you build a temporary result set that can be referenced within a larger query. This makes complex logic easier to read, maintain, and extend.

   
   

   In this step, you’ll bring together everything you’ve learned to:

   • Join multiple tables into a single dataset.
   • Apply grouping and aggregation across categories and territories.
   • Filter results using both WHERE and HAVING.
   • Use a CTE to simplify complex queries.
   • Apply ranking and percentage calculations with window functions.

   
   

   By the end of this step, you’ll be able to design queries that combine multiple analytical techniques into a single, clear solution.

   
   

   What You’ll Learn in This Step

   You’ll learn how to:

   • Create a Common Table Expression (CTE) with WITH.
   • Combine multiple tables into one analytical dataset.
   • Filter data at both the row and group levels.
   • Apply ranking and percentage calculations in the final result.

   
   

   ---

   
   

   info> You will write all of your T-SQL answers directly in the 6-step-six.sql file provided in this environment.

   When you are ready to run the query, simply press Run in the Terminal tab.
   This will automatically execute the contents of 6-step-six.sql against the database and display the results.
   You do not need to type any commands in the terminal manually.

   
   

   Optional: How to Log Into the Database Manually

   You don’t need to do this for this lab, as it will not be able to be validated. However, if you’d like to explore connecting to the database directly using the mssql-cli, here’s how to do it using the sqlcmd tool.

   
   

   Database Login and Basics

   To connect to the SQL Server manually, run this command in the Terminal tab:

   sqlcmd -S localhost -U SA -P 'P@ssword1'
   

   
   #### What Happens Next

   • Once connected, you’ll see a prompt that looks like this:

     1>
     

     This means SQL Server is ready to accept commands.

   • Every query you type must end with GO on its own line. Example:

     SELECT @@VERSION;
     GO
     

   • To exit, type:

     QUIT
     

   Again, this is purely optional and not needed to complete the tasks, it’s just here if you’d like to explore running SQL queries directly.

   
   

   ---

   
   ### Using WITH to Create a Common Table Expression (CTE)

   A Common Table Expression (CTE) is a temporary result set that can be referenced within a larger query. It is defined using the WITH keyword followed by a query. CTEs make it easier to organize complex queries into logical building blocks.

   CTEs are often used when a query requires multiple layers of aggregation, filtering, or joins. They allow you to separate the logic of creating an intermediate dataset from the logic of the final output.

   Syntax

   WITH cte_name AS (
       SELECT column_name, AGGREGATE_FUNCTION(other_column)
       FROM table_name
       WHERE condition
       GROUP BY column_name
       HAVING AGGREGATE_FUNCTION(other_column) condition
   )
   SELECT *
   FROM cte_name;
   GO
   

   • WITH cte_name AS (...): Defines the CTE
   • Query inside the CTE: Can include joins, grouping, and filtering
   • Outer query: Selects from the CTE
   • GO: Ends the batch for execution

   In this task, you will create a CTE that joins orders, products, and territories, then applies grouping and filtering before returning aggregated results. ### Ranking and Percentages with Window Functions

   Once you have aggregated results in a CTE or subquery, you can extend the analysis by applying ranking and percentage calculations. This is done using window functions in the outer query.

   • DENSE_RANK() assigns consecutive ranks within each partition, ensuring there are no gaps in rank values.
   • A windowed SUM() can be used to calculate percentages by dividing a group’s value by the total for its partition.

   Syntax

   SELECT column_name,
          DENSE_RANK() OVER(PARTITION BY group_column ORDER BY value_column DESC) AS Rank,
          value_column * 1.0 / SUM(value_column) OVER(PARTITION BY group_column) AS Percentage
   FROM cte_name;
   GO
   

   • DENSE_RANK(): Ranks rows within each group
   • OVER(PARTITION BY group_column ORDER BY value_column DESC): Defines partitions and rank order
   • SUM(value_column) OVER(PARTITION BY group_column): Calculates the partition total
   • Multiplying by 1.0: Ensures accurate decimal division
   • GO: Ends the batch for execution

   In this task, you’ll apply ranking and percentage calculations to the results of the CTE you created in the previous task.