advanced_conditional_examples.sql

/*
Advanced SQL Conditional Examples
-------------------------------
This file contains advanced examples of SQL conditional expressions and their applications
in real-world scenarios. Each section demonstrates different aspects of conditional logic
in SQL.
*/

-- 1. Advanced Data Classification
-- Example: Customer Segmentation based on multiple factors
SELECT 
    customer_id,
    customer_name,
    total_purchases,
    avg_order_value,
    CASE 
        WHEN total_purchases > 100 AND avg_order_value > 1000 THEN 'Platinum'
        WHEN total_purchases > 50 AND avg_order_value > 500 THEN 'Gold'
        WHEN total_purchases > 20 OR avg_order_value > 250 THEN 'Silver'
        ELSE 'Bronze'
    END as customer_tier,
    
    -- Loyalty Score Calculation
    CASE 
        WHEN months_active > 24 THEN 3
        WHEN months_active > 12 THEN 2
        ELSE 1
    END * 
    CASE 
        WHEN return_rate < 0.05 THEN 3
        WHEN return_rate < 0.10 THEN 2
        ELSE 1
    END as loyalty_score;

-- 2. Dynamic Date Handling
SELECT 
    event_date,
    event_name,
    -- Fiscal Quarter Calculation
    CASE 
        WHEN MONTH(event_date) BETWEEN 1 AND 3 THEN 'Q1'
        WHEN MONTH(event_date) BETWEEN 4 AND 6 THEN 'Q2'
        WHEN MONTH(event_date) BETWEEN 7 AND 9 THEN 'Q3'
        ELSE 'Q4'
    END + '-' + CAST(YEAR(event_date) AS VARCHAR) as fiscal_quarter,
    
    -- Season Classification
    CASE 
        WHEN MONTH(event_date) IN (12, 1, 2) THEN 'Winter'
        WHEN MONTH(event_date) IN (3, 4, 5) THEN 'Spring'
        WHEN MONTH(event_date) IN (6, 7, 8) THEN 'Summer'
        ELSE 'Fall'
    END as season;

-- 3. Advanced Statistical Calculations
SELECT 
    department,
    AVG(salary) as avg_salary,
    -- Salary Variance Categories
    CASE 
        WHEN STDEV(salary) > 10000 THEN 'High Variance'
        WHEN STDEV(salary) > 5000 THEN 'Medium Variance'
        ELSE 'Low Variance'
    END as salary_variance,
    
    -- Percentile-based Categories
    CASE 
        WHEN salary > PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) 
            OVER (PARTITION BY department) THEN 'Top 25%'
        WHEN salary > PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
            OVER (PARTITION BY department) THEN 'Top 50%'
        ELSE 'Bottom 50%'
    END as salary_percentile;

-- 4. Complex Business Rules
-- Example: Insurance Premium Calculation
SELECT 
    policy_id,
    customer_age,
    coverage_amount,
    -- Base Premium Calculation
    CASE 
        WHEN customer_age < 25 THEN coverage_amount * 0.05
        WHEN customer_age < 35 THEN coverage_amount * 0.04
        WHEN customer_age < 45 THEN coverage_amount * 0.035
        WHEN customer_age < 55 THEN coverage_amount * 0.045
        ELSE coverage_amount * 0.055
    END +
    -- Risk Adjustment
    CASE risk_factor
        WHEN 'Low' THEN -500
        WHEN 'Medium' THEN 0
        WHEN 'High' THEN 1000
        ELSE 0
    END as calculated_premium;

-- 5. Advanced String Manipulation
SELECT 
    email,
    -- Email Provider Classification
    CASE 
        WHEN LOWER(email) LIKE '%@gmail.com' THEN 'Google'
        WHEN LOWER(email) LIKE '%@outlook.com' OR 
             LOWER(email) LIKE '%@hotmail.com' OR 
             LOWER(email) LIKE '%@live.com' THEN 'Microsoft'
        WHEN LOWER(email) LIKE '%@yahoo.com' THEN 'Yahoo'
        ELSE 'Other'
    END as email_provider,
    
    -- Phone Number Formatting
    CASE 
        WHEN LEN(phone) = 10 THEN 
            '(' + SUBSTRING(phone, 1, 3) + ') ' +
            SUBSTRING(phone, 4, 3) + '-' +
            SUBSTRING(phone, 7, 4)
        ELSE phone
    END as formatted_phone;

-- 6. Hierarchical Data Processing
WITH EmployeeHierarchy AS (
    SELECT 
        employee_id,
        manager_id,
        salary,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id,
        e.manager_id,
        e.salary,
        eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id,
    level,
    CASE 
        WHEN level = 1 THEN 'CEO'
        WHEN level = 2 THEN 'VP'
        WHEN level = 3 THEN 'Director'
        WHEN level = 4 THEN 'Manager'
        ELSE 'Staff'
    END as position_level;

-- 7. Dynamic Pivot Tables
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @columns = STRING_AGG(
    QUOTENAME(category),
    ',')
FROM (SELECT DISTINCT category FROM products) as categories;

SET @sql = N'
SELECT *
FROM (
    SELECT 
        department,
        category,
        CASE 
            WHEN units_sold > 1000 THEN ''High''
            WHEN units_sold > 500 THEN ''Medium''
            ELSE ''Low''
        END as sales_volume
    FROM sales
) as SourceTable
PIVOT (
    COUNT(sales_volume)
    FOR category IN (' + @columns + ')
) as PivotTable;
';

EXEC sp_executesql @sql;

-- 8. Geographic Data Processing
SELECT 
    location_id,
    latitude,
    longitude,
    -- Hemisphere Classification
    CASE 
        WHEN latitude > 0 AND longitude > 0 THEN 'Northeast'
        WHEN latitude > 0 AND longitude < 0 THEN 'Northwest'
        WHEN latitude < 0 AND longitude > 0 THEN 'Southeast'
        ELSE 'Southwest'
    END as hemisphere,
    
    -- Time Zone Approximation
    CASE 
        WHEN longitude BETWEEN -180 AND -135 THEN 'GMT-10'
        WHEN longitude BETWEEN -134 AND -90 THEN 'GMT-8'
        WHEN longitude BETWEEN -89 AND -45 THEN 'GMT-5'
        WHEN longitude BETWEEN -44 AND 0 THEN 'GMT'
        WHEN longitude BETWEEN 1 AND 45 THEN 'GMT+2'
        WHEN longitude BETWEEN 46 AND 90 THEN 'GMT+5'
        WHEN longitude BETWEEN 91 AND 135 THEN 'GMT+8'
        ELSE 'GMT+10'
    END as approximate_timezone;