DAY 18: SQL - CASE Statements & PIVOT/UNPIVOT

Exercise 1: Categorize Sales Using Simple CASE Statement

Goal: Learn to use simple CASE expressions to categorize sales amounts into performance tiers.

Step-by-Step Instructions:

1. Open your SQL editor and connect to your database
2. Start with a basic query to see the sales data:

   SELECT OrderID, CustomerID, Sales, OrderDate
   FROM Orders
   ORDER BY Sales DESC;

   This shows your raw sales data
3. Add a CASE statement to categorize sales:

   SELECT
       OrderID,
       CustomerID,
       Sales,
       OrderDate,
       CASE
           WHEN Sales < 100 THEN 'Low'
           WHEN Sales >= 100 AND Sales < 500 THEN 'Medium'
           WHEN Sales >= 500 AND Sales < 1000 THEN 'High'
           WHEN Sales >= 1000 THEN 'Premium'
           ELSE 'Unknown'
       END AS SalesCategory
   FROM Orders
   ORDER BY Sales DESC;

4. Execute the query (press F5 or click Execute)
5. Analyze the results: Each order now has a category label based on sales amount
6. Add a count summary:

   SELECT
       CASE
           WHEN Sales < 100 THEN 'Low'
           WHEN Sales >= 100 AND Sales < 500 THEN 'Medium'
           WHEN Sales >= 500 AND Sales < 1000 THEN 'High'
           WHEN Sales >= 1000 THEN 'Premium'
       END AS SalesCategory,
       COUNT(*) AS OrderCount,
       SUM(Sales) AS TotalSales,
       AVG(Sales) AS AvgSales
   FROM Orders
   GROUP BY
       CASE
           WHEN Sales < 100 THEN 'Low'
           WHEN Sales >= 100 AND Sales < 500 THEN 'Medium'
           WHEN Sales >= 500 AND Sales < 1000 THEN 'High'
           WHEN Sales >= 1000 THEN 'Premium'
       END
   ORDER BY TotalSales DESC;

   This groups orders by category and calculates metrics

💡 Tips:

• Order matters: CASE evaluates conditions top-to-bottom, stops at first TRUE condition
• Always include ELSE: Catches any unexpected values (returns NULL if omitted)
• Use meaningful names: Alias your CASE column with AS for clarity
• Grouping with CASE: When using GROUP BY, repeat the entire CASE expression (or use a CTE)
• Keyboard shortcut (SSMS): Ctrl+R toggles results pane
• Business logic: Adjust thresholds (100, 500, 1000) based on your actual business tiers

✅ Expected Result: A result set with each order showing its sales category (Low/Medium/High/Premium), and a summary table showing count and totals per category

🔄 Connection to Previous Learning: This is similar to Excel's IF statements from Day 3, but more powerful with multiple conditions in one expression.

Exercise 2: Customer Segmentation with Searched CASE

Goal: Learn to use searched CASE for complex multi-column conditions to segment customers.

Step-by-Step Instructions:

1. Understand the goal: Segment customers as Active, VIP, At Risk, or Inactive based on multiple criteria
2. Create a customer segmentation query:

   SELECT
       CustomerID,
       CustomerName,
       TotalOrders,
       TotalSpent,
       LastOrderDate,
       CASE
           WHEN TotalOrders >= 10 AND TotalSpent >= 5000 THEN 'VIP'
           WHEN LastOrderDate >= DATEADD(DAY, -90, GETDATE()) THEN 'Active'
           WHEN LastOrderDate >= DATEADD(DAY, -180, GETDATE())
                AND LastOrderDate < DATEADD(DAY, -90, GETDATE()) THEN 'At Risk'
           WHEN LastOrderDate < DATEADD(DAY, -180, GETDATE()) THEN 'Inactive'
           ELSE 'New'
       END AS CustomerSegment
   FROM (
       SELECT
           C.CustomerID,
           C.CustomerName,
           COUNT(O.OrderID) AS TotalOrders,
           ISNULL(SUM(O.Sales), 0) AS TotalSpent,
           MAX(O.OrderDate) AS LastOrderDate
       FROM Customers C
       LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
       GROUP BY C.CustomerID, C.CustomerName
   ) AS CustomerMetrics
   ORDER BY TotalSpent DESC;

3. Execute and analyze: See how customers are categorized
4. Create a segment summary:

   WITH CustomerSegments AS (
       SELECT
           C.CustomerID,
           COUNT(O.OrderID) AS TotalOrders,
           ISNULL(SUM(O.Sales), 0) AS TotalSpent,
           MAX(O.OrderDate) AS LastOrderDate,
           CASE
               WHEN COUNT(O.OrderID) >= 10 AND ISNULL(SUM(O.Sales), 0) >= 5000 THEN 'VIP'
               WHEN MAX(O.OrderDate) >= DATEADD(DAY, -90, GETDATE()) THEN 'Active'
               WHEN MAX(O.OrderDate) >= DATEADD(DAY, -180, GETDATE()) THEN 'At Risk'
               WHEN MAX(O.OrderDate) < DATEADD(DAY, -180, GETDATE()) THEN 'Inactive'
               ELSE 'New'
           END AS Segment
       FROM Customers C
       LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
       GROUP BY C.CustomerID
   )
   SELECT
       Segment,
       COUNT(*) AS CustomerCount,
       SUM(TotalSpent) AS Revenue,
       AVG(TotalSpent) AS AvgSpending
   FROM CustomerSegments
   GROUP BY Segment
   ORDER BY Revenue DESC;

5. Verify the logic: Check that VIP customers have 10+ orders AND $5000+ spending

💡 Tips:

• Searched CASE vs Simple CASE: Searched CASE uses WHEN condition (more flexible), Simple CASE uses WHEN value
• AND/OR operators: Combine multiple conditions in one WHEN clause
• Date functions: DATEADD and GETDATE() make dynamic date-based segments
• NULL handling: Use ISNULL() or COALESCE() to handle customers with no orders
• CTE for clarity: Use WITH (CTE) to make complex queries more readable
• Business application: Use this for targeted marketing campaigns by segment

✅ Expected Result: A customer list with segments (VIP, Active, At Risk, Inactive, New) and a summary showing count and revenue per segment

🔄 Connection to Previous Learning: Combines Day 12's date functions with conditional logic, similar to Excel's nested IFs from Day 3.

Exercise 3: PIVOT Sales Data from Rows to Columns

Goal: Learn to use PIVOT operator to transform row data into a matrix format with years as columns.

Step-by-Step Instructions:

1. First, see the data in row format:

   SELECT
       Category,
       YEAR(OrderDate) AS OrderYear,
       SUM(Sales) AS TotalSales
   FROM Orders
   WHERE YEAR(OrderDate) BETWEEN 2021 AND 2024
   GROUP BY Category, YEAR(OrderDate)
   ORDER BY Category, OrderYear;

   This shows sales by category and year in rows
2. Now PIVOT to put years as columns:

   SELECT Category, [2021], [2022], [2023], [2024]
   FROM (
       SELECT
           Category,
           YEAR(OrderDate) AS OrderYear,
           Sales
       FROM Orders
       WHERE YEAR(OrderDate) BETWEEN 2021 AND 2024
   ) AS SourceData
   PIVOT (
       SUM(Sales)
       FOR OrderYear IN ([2021], [2022], [2023], [2024])
   ) AS PivotTable
   ORDER BY Category;

3. Execute and compare: Notice how years are now column headers instead of rows
4. Add year-over-year growth calculation:

   WITH SalesPivot AS (
       SELECT Category, [2021], [2022], [2023], [2024]
       FROM (
           SELECT Category, YEAR(OrderDate) AS OrderYear, Sales
           FROM Orders
           WHERE YEAR(OrderDate) BETWEEN 2021 AND 2024
       ) AS SourceData
       PIVOT (
           SUM(Sales)
           FOR OrderYear IN ([2021], [2022], [2023], [2024])
       ) AS PivotTable
   )
   SELECT
       Category,
       [2021],
       [2022],
       [2023],
       [2024],
       CAST((([2024] - [2023]) / NULLIF([2023], 0) * 100) AS DECIMAL(5,2)) AS GrowthPercent_2024
   FROM SalesPivot
   ORDER BY [2024] DESC;

5. Format results: Verify that categories are rows and years are columns

💡 Tips:

• PIVOT syntax: PIVOT (aggregate_function(column) FOR pivot_column IN (value1, value2, ...))
• Source query must have: (1) row labels, (2) pivot column, (3) values to aggregate
• Square brackets required: Column names in IN clause need [brackets], especially for numbers
• Aggregate function: Use SUM, AVG, COUNT, MIN, MAX - only one at a time
• Static columns: You must specify column values explicitly (2021, 2022, etc.) - can't be dynamic in simple PIVOT
• NULL values: PIVOT converts missing data to NULL; use ISNULL() to replace with 0

✅ Expected Result: A table with categories as rows and years (2021-2024) as columns, showing sales for each category-year combination

🔄 Connection to Previous Learning: Similar to Excel Pivot Tables from Day 7, but in SQL format for reporting databases.

Exercise 4: UNPIVOT to Normalize Column Data into Rows

Goal: Learn to use UNPIVOT to transform wide format data (columns) into long format (rows).

Setup: Assume you have quarterly sales data in columns: Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales

Step-by-Step Instructions:

1. First, see the wide format data:

   -- Sample data in wide format
   SELECT
       ProductID,
       ProductName,
       Q1_Sales,
       Q2_Sales,
       Q3_Sales,
       Q4_Sales
   FROM QuarterlySales;

   This has separate columns for each quarter
2. UNPIVOT to convert columns to rows:

   SELECT
       ProductID,
       ProductName,
       Quarter,
       SalesAmount
   FROM QuarterlySales
   UNPIVOT (
       SalesAmount
       FOR Quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
   ) AS UnpivotTable
   ORDER BY ProductID, Quarter;

3. Clean up the quarter names:

   SELECT
       ProductID,
       ProductName,
       REPLACE(Quarter, '_Sales', '') AS Quarter,
       SalesAmount
   FROM QuarterlySales
   UNPIVOT (
       SalesAmount
       FOR Quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
   ) AS UnpivotTable
   ORDER BY ProductID, Quarter;

4. Use the unpivoted data for analysis:

   WITH UnpivotedData AS (
       SELECT
           ProductID,
           ProductName,
           REPLACE(Quarter, '_Sales', '') AS Quarter,
           SalesAmount
       FROM QuarterlySales
       UNPIVOT (
           SalesAmount
           FOR Quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
       ) AS UnpivotTable
   )
   SELECT
       Quarter,
       COUNT(DISTINCT ProductID) AS ProductCount,
       SUM(SalesAmount) AS TotalSales,
       AVG(SalesAmount) AS AvgSales,
       MAX(SalesAmount) AS MaxSales
   FROM UnpivotedData
   GROUP BY Quarter
   ORDER BY Quarter;

5. Alternative: Create from pivoted data (PIVOT then UNPIVOT):

   -- This demonstrates the inverse relationship
   WITH Pivoted AS (
       SELECT ProductID, [Q1], [Q2], [Q3], [Q4]
       FROM (
           SELECT ProductID, Quarter, Sales FROM SalesData
       ) AS Src
       PIVOT (SUM(Sales) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS Pvt
   )
   SELECT ProductID, Quarter, SalesAmount
   FROM Pivoted
   UNPIVOT (
       SalesAmount FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
   ) AS Unpvt;

💡 Tips:

• UNPIVOT syntax: UNPIVOT (value_column FOR name_column IN (col1, col2, col3))
• When to use: Converting Excel-style "wide" data to proper database "long" format
• No brackets needed: Column names in IN clause don't need brackets (unlike PIVOT)
• NULL values disappear: UNPIVOT automatically excludes rows where value is NULL
• Data normalization: This is proper database design - one row per observation
• Alternative approach: Can use UNION ALL instead: SELECT ProductID, 'Q1', Q1_Sales UNION ALL SELECT ProductID, 'Q2', Q2_Sales...

✅ Expected Result: Data transformed from wide format (one row per product, columns Q1-Q4) to long format (multiple rows per product, one per quarter)

🔄 Connection to Previous Learning: The inverse of Day 7's Pivot Tables - converting summary format back to detail format.

Exercise 5: Conditional Aggregation Using CASE

Goal: Learn to use CASE within aggregate functions for conditional summaries without GROUP BY.

Step-by-Step Instructions:

1. Basic conditional aggregation:

   SELECT
       COUNT(*) AS TotalOrders,
       COUNT(CASE WHEN Sales >= 1000 THEN 1 END) AS HighValueOrders,
       COUNT(CASE WHEN Sales < 1000 THEN 1 END) AS LowValueOrders,
       SUM(CASE WHEN Sales >= 1000 THEN Sales ELSE 0 END) AS HighValueRevenue,
       SUM(CASE WHEN Sales < 1000 THEN Sales ELSE 0 END) AS LowValueRevenue
   FROM Orders;

   This creates multiple metrics in one query
2. Percentage calculations:

   SELECT
       COUNT(*) AS TotalOrders,
       COUNT(CASE WHEN Sales >= 1000 THEN 1 END) AS HighValueOrders,
       CAST(COUNT(CASE WHEN Sales >= 1000 THEN 1 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS HighValuePercent,
       SUM(Sales) AS TotalRevenue,
       SUM(CASE WHEN Sales >= 1000 THEN Sales ELSE 0 END) AS HighValueRevenue,
       CAST(SUM(CASE WHEN Sales >= 1000 THEN Sales ELSE 0 END) * 100.0 / SUM(Sales) AS DECIMAL(5,2)) AS HighValueRevenuePercent
   FROM Orders;

3. By category conditional aggregation:

   SELECT
       Category,
       COUNT(*) AS TotalOrders,
       SUM(CASE WHEN YEAR(OrderDate) = 2024 THEN Sales ELSE 0 END) AS Sales_2024,
       SUM(CASE WHEN YEAR(OrderDate) = 2023 THEN Sales ELSE 0 END) AS Sales_2023,
       SUM(CASE WHEN YEAR(OrderDate) = 2022 THEN Sales ELSE 0 END) AS Sales_2022,
       AVG(CASE WHEN Sales >= 1000 THEN Sales END) AS AvgHighValue,
       AVG(CASE WHEN Sales < 1000 THEN Sales END) AS AvgLowValue
   FROM Orders
   GROUP BY Category
   ORDER BY Sales_2024 DESC;

4. Complex business logic:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       COUNT(*) AS TotalOrders,
       -- Count orders by category
       COUNT(CASE WHEN Category = 'Electronics' THEN 1 END) AS Electronics_Count,
       COUNT(CASE WHEN Category = 'Clothing' THEN 1 END) AS Clothing_Count,
       COUNT(CASE WHEN Category = 'Books' THEN 1 END) AS Books_Count,
       -- Sum sales by category
       SUM(CASE WHEN Category = 'Electronics' THEN Sales ELSE 0 END) AS Electronics_Sales,
       SUM(CASE WHEN Category = 'Clothing' THEN Sales ELSE 0 END) AS Clothing_Sales,
       SUM(CASE WHEN Category = 'Books' THEN Sales ELSE 0 END) AS Books_Sales,
       -- Calculate percentages
       CAST(SUM(CASE WHEN Category = 'Electronics' THEN Sales ELSE 0 END) * 100.0 / SUM(Sales) AS DECIMAL(5,2)) AS Electronics_Percent
   FROM Orders
   GROUP BY YEAR(OrderDate)
   ORDER BY OrderYear;

5. Compare with PIVOT approach: Notice how this is more flexible than PIVOT for complex calculations

💡 Tips:

• CASE in aggregates: COUNT(CASE WHEN condition THEN 1 END) counts matching rows
• SUM trick: SUM(CASE WHEN condition THEN value ELSE 0 END) sums conditionally
• vs PIVOT: More flexible - can use different aggregates and complex conditions in one query
• NULL vs 0: COUNT ignores NULL, SUM treats NULL as 0 - use appropriately
• Performance: Usually faster than multiple queries or subqueries
• Business dashboards: Perfect for creating summary reports with multiple metrics

✅ Expected Result: A single row (or one per group) showing multiple conditional aggregates like high-value order count, revenue by category, and percentages

🔄 Connection to Previous Learning: Similar to Excel's SUMIF and COUNTIF from Day 9, but more powerful with multiple conditions in one formula.