WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(order_amount) AS revenue
    FROM orders
    WHERE order_status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
),
monthly_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    previous_month_revenue,
    (revenue - previous_month_revenue) / previous_month_revenue * 100 AS growth_percentage
FROM monthly_growth
WHERE previous_month_revenue IS NOT NULL
ORDER BY month

SELECT
    product_name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS avg_price_in_category,
    price - AVG(price) OVER (PARTITION BY category) AS price_diff_from_avg,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM

SELECT
    order_id,
    order_amount,
    CASE
        WHEN order_amount < 50 THEN 'Small'
        WHEN order_amount BETWEEN 50 AND 250 THEN 'Medium'
        WHEN order_amount BETWEEN 251 AND 1000 THEN 'Large'
        ELSE 'Enterprise'
    END AS order_size,
    CASE
        WHEN EXTRACT(DOW FROM order_date) IN (0, 6) THEN 'Weekend'
        WHEN EXTRACT(HOUR FROM order_time) BETWEEN 9 AND 17 THEN 'Business Hours'
        ELSE 'After Hours'
    END AS order_timing,
    shipping_cost,
    CASE
        WHEN shipping_cost = 0 THEN 'Free Shipping'
        WHEN shipping_cost/order_amount > 0.15 THEN 'High Shipping Ratio'
        ELSE 'Normal Shipping'
    END AS shipping_category
FROM

SELECT
    c.customer_id,
    c.customer_name,
    recent_orders.order_date,
    recent_orders.order_amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_date, order_amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 3
) AS

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: start with the CEO
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE job_title = 'CEO'
    
    UNION ALL
    
    -- Recursive case: add direct reports
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    level,
    REPEAT('    ', level - 1) || employee_name AS org_chart
FROM employee_hierarchy
ORDER BY level,

-- Extracting values from JSON
SELECT
    user_id,
    preferences->>'theme' AS user_theme,
    preferences->>'notifications' AS notification_setting,
    JSON_ARRAY_LENGTH(preferences->'favorite_categories') AS num_favorite_categories
FROM users;

-- Working with arrays
SELECT
    product_id,
    product_name,
    UNNEST(categories) AS category
FROM products
WHERE 'limited-edition' = ANY(tags)

-- Pivoting data (rows to columns)
SELECT
    category,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) AS suspended_count
FROM accounts
GROUP BY category;

-- Unpivoting data (columns to rows)
SELECT
    year,
    'Q1' AS quarter,
    q1_revenue AS revenue
FROM financial_results
UNION ALL
SELECT
    year,
    'Q2' AS quarter,
    q2_revenue AS revenue
FROM financial_results
UNION ALL
SELECT
    year,
    'Q3' AS quarter,
    q3_revenue AS revenue
FROM financial_results
UNION ALL
SELECT
    year,
    'Q4' AS quarter,
    q4_revenue AS revenue
FROM financial_results
ORDER BY year,

SELECT
    product_name,
    -- Extract numbers from product descriptions
    REGEXP_EXTRACT(description, '(\d+(\.\d+)?)', 1) AS extracted_number,
    
    -- Standardize inconsistent category names
    CASE
        WHEN LOWER(category) LIKE '%elect%' THEN 'Electronics'
        WHEN LOWER(category) LIKE '%furn%' THEN 'Furniture'
        WHEN LOWER(category) LIKE '%cloth%' OR LOWER(category) LIKE '%apparel%' THEN 'Clothing'
        ELSE 'Other'
    END AS standardized_category,
    
    -- Create slug for URLs
    LOWER(REGEXP_REPLACE(product_name, '[^a-zA-Z0-9]+', '-')) AS product_slug
FROM

SELECT
    order_id,
    order_date,
    -- Extract components
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(QUARTER FROM order_date) AS order_quarter,
    
    -- Calculate business days between order and shipment
    (SELECT COUNT(*) 
     FROM generate_series(order_date, ship_date, '1 day'::interval) AS dt
     WHERE EXTRACT(DOW FROM dt) BETWEEN 1 AND 5
    ) AS business_days_to_ship,
    
    -- Flag holiday season orders
    CASE 
        WHEN EXTRACT(MONTH FROM order_date) = 12 AND EXTRACT(DAY FROM order_date) > 15 THEN 'Holiday Rush'
        ELSE 'Normal'
    END AS season_flag,
    
    -- Calculate fiscal year (assuming July start)
    CASE
        WHEN EXTRACT(MONTH FROM order_date) >= 7 THEN EXTRACT(YEAR FROM order_date)
        ELSE EXTRACT(YEAR FROM order_date) - 1
    END AS fiscal_year
FROM

-- Use EXISTS instead of IN for better performance with large datasets
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id 
    AND o.order_amount > 1000
);

-- Use window functions instead of self-joins
SELECT
    product_id,
    product_name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg_price
FROM products;

-- Use CTEs for query clarity and optimization
WITH high_value_customers AS (
    SELECT 
        customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amount) > 10000
)
SELECT
    c.customer_id,
    c.customer_name,
    c.signup_date,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN high_value_customers hvc ON c.customer_id = hvc.customer_id
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name,