Use Advanced SQL Techniques

Learning Objectives

After completing this unit, you’ll be able to:

• Transform data values using the CASE conditional expression.
  
• Apply string manipulation functions, such as SUBSTRING(), to reformat data.
  
• Check for related data using subqueries with EXISTS and NOT EXISTS.
  
• Simplify complex queries using Common Table Expressions (CTEs).
  

Unlock the Full Potential of Queries

In the previous units, you reviewed the fundamental tools to select, filter, group, and join data. In this unit, you'll learn the techniques that elevate your queries from basic reports to sophisticated, reusable data logic.

These advanced techniques are essential for building multi-step analysis or quickly identifying relationships within massive datasets.

Data Transformation with CASE and String Functions

Raw data rarely arrives perfectly formatted for reporting. You often need to change values, categorize numbers, or clean up text before analysis.

Apply Conditional Logic with CASE

The CASE expression allows you to implement if/then/else logic directly in your SELECT statement. It checks a field's value against one or more conditions and returns a corresponding result, which is perfect for creating business categories.

The CASE expression has the following structure:

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    ELSE result_default
END AS "New_Column_Alias"

To quickly segment accounts into 'High-Value' or 'Standard' based on a revenue field, use the following query to create the segment:

SELECT "ssot__AccountId__c",
       "ssot__AnnualRevenue__c",
       CASE
           WHEN "ssot__AnnualRevenue__c" > 100000 THEN 'High-Value'
           WHEN "ssot__AnnualRevenue__c" IS NULL THEN 'Unqualified'
           ELSE 'Standard'
       END AS "Account_Segment"
FROM "ssot__Account__dlm"

Manipulate Text with String Functions

String manipulation functions are crucial for normalizing and cleaning text data, which ensures accuracy when filtering or grouping.

Use functions like UPPER() and TRIM() to standardize text fields for quality checks:

SELECT TRIM(UPPER("ssot__FirstName__c")) AS "Clean_FirstName"
FROM "ssot__Individual__dlm"

Another powerful function is SUBSTRING(), which extracts a specific sequence of characters from a string.

If you need to pull only the first three characters of a product code to identify its product family, use this query:

SELECT "ProductCode__c",
       SUBSTRING("ProductCode__c", 1, 3) AS "Product_Family"
FROM "ssot__Product__dlm"

The arguments are: the field, the starting position (1 in this case), and the length of the string to extract (3).

Simplify and Optimize Queries

As your analysis becomes more complex, requiring multiple steps of filtering and calculation, your queries can become long and difficult to read. CTEs and subqueries help organize and optimize this complex logic.

Simplify Complex Queries with CTEs

A Common Table Expression (CTE) is a temporary, named result set defined within the execution scope of a single SELECT statement. You define a CTE using the WITH clause.

CTEs are perfect for breaking down a multi-step query into logical, readable parts. They act as temporary virtual tables that you can reference immediately after they are defined.

To find the average order size for high-value accounts (revenue > 100000), first identify those accounts and then find their average order size using a CTE.

WITH HighValueAccounts AS (
  SELECT "ssot__AccountId__c"
  FROM "ssot__Account__dlm"
  WHERE "ssot__AnnualRevenue__c" > 100000
)
SELECT AVG(o."OrderTotalAmount__c") AS "Avg_HighValue_Order_Amount"
FROM "ssot__SalesOrder__dlm" o
INNER JOIN HighValueAccounts h ON o."ssot__AccountId__c" = h."ssot__AccountId__c"

In this example, the HighValueAccounts CTE is calculated first and then treated like a standard DMO in the final SELECT statement.

Check Related Data with Subqueries

A subquery (or inner query) is a query nested inside another SELECT statement. Subqueries are often used in the WHERE clause to filter the main query based on data from a related DMO without performing a full join.

The EXISTS operator checks if a subquery returns any rows. It is highly efficient because the subquery stops running as soon as it finds the first match. Use EXISTS to find all individuals who have at least one related sales order:

SELECT "ssot__FirstName__c",
       "ssot__LastName__c"
FROM "ssot__Individual__dlm" i
WHERE EXISTS (
  SELECT 1
  FROM "ssot__SalesOrder__dlm" o
  WHERE o."ssot__IndividualId__c" = i."ssot__Id__c"
)

Conversely, the NOT EXISTS operator returns TRUE if the subquery returns no rows. Use NOT EXISTS to identify at-risk customers—individuals who have no recorded sales orders:

WHERE NOT EXISTS (
  SELECT 1
  FROM "ssot__SalesOrder__dlm" o
  WHERE o."ssot__IndividualId__c" = i."ssot__Id__c"
)

You’ve successfully navigated creating complex SQL queries in Data 360! Time to get started querying your own data.

Now that you've learned about data manipulation and common table expressions, it's time to check your knowledge in this unit and earn this badge.