conditional_column_examples.sql

-- Basic conditional column using CASE
SELECT 
    product_name,
    price,
    CASE 
        WHEN price > 1000 THEN 'Premium'
        WHEN price > 500 THEN 'Mid-range'
        ELSE 'Budget'
    END as price_category

FROM products;

-- Multiple conditional columns
SELECT 
    employee_name,
    salary,
    department,
    CASE WHEN salary > 50000 THEN 'High' ELSE 'Standard' END as salary_tier,
    CASE WHEN department = 'Sales' THEN salary * 0.10 
         WHEN department = 'IT' THEN salary * 0.08
         ELSE salary * 0.05 
    END as bonus_amount,
    CASE WHEN hire_date < '2020-01-01' THEN 'Senior' ELSE 'Junior' END as experience_level

FROM employees;

-- Conditional columns with calculations
SELECT 
    order_id,
    order_amount,
    discount_percent,
    order_amount * (1 - CASE 
        WHEN order_amount > 1000 THEN 0.20  -- 20% discount
        WHEN order_amount > 500 THEN 0.10   -- 10% discount
        ELSE 0.05                           -- 5% discount
    END) as final_price,
    CASE 
        WHEN payment_method = 'Credit' THEN order_amount * 0.02  -- 2% cashback
        WHEN payment_method = 'Debit' THEN order_amount * 0.01   -- 1% cashback
        ELSE 0 
    END as cashback_amount

FROM orders;

-- Conditional columns with date logic
SELECT 
    customer_name,
    signup_date,
    CASE 
        WHEN DATEDIFF(year, signup_date, GETDATE()) >= 5 THEN 'Loyal'
        WHEN DATEDIFF(year, signup_date, GETDATE()) >= 2 THEN 'Regular'
        ELSE 'New'
    END as customer_status,
    CASE 
        WHEN MONTH(last_purchase_date) = MONTH(GETDATE()) THEN 'Active'
        WHEN DATEDIFF(month, last_purchase_date, GETDATE()) <= 3 THEN 'Recent'
        ELSE 'Inactive'
    END as activity_status

FROM customers;

-- Conditional columns with aggregate functions
SELECT 
    category,
    COUNT(*) as total_products,
    AVG(price) as avg_price,
    CASE 
        WHEN AVG(price) > 1000 THEN 'Luxury'
        WHEN AVG(price) > 500 THEN 'Premium'
        ELSE 'Standard'
    END as category_tier,
    SUM(CASE 
        WHEN stock_level = 0 THEN 1 
        ELSE 0 
    END) as out_of_stock_items,
    ROUND(AVG(CASE 
        WHEN rating IS NOT NULL THEN rating 
        ELSE 0 
    END), 2) as avg_rating

FROM products
GROUP BY category;

-- Conditional columns with subqueries
SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products) as avg_price,
    CASE 
        WHEN p.price > (SELECT AVG(price) * 1.5 FROM products) THEN 'Above Average'
        WHEN p.price < (SELECT AVG(price) * 0.5 FROM products) THEN 'Below Average'
        ELSE 'Average'
    END as price_comparison,
    CASE 
        WHEN p.sales_count > (SELECT AVG(sales_count) FROM products) THEN 'High Demand'
        ELSE 'Normal Demand'
    END as demand_status

FROM products p;