-- Common Table Expressions (CTEs) for readable complex queries
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as monthly_total,
        COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),
growth_metrics AS (
    SELECT
        month,
        monthly_total,
        LAG(monthly_total) OVER (ORDER BY month) as previous_month,
        monthly_total - LAG(monthly_total) OVER (ORDER BY month) as growth
    FROM monthly_sales
)
SELECT
    month,
    monthly_total,
    ROUND((growth / previous_month) * 100, 2) as growth_percentage
FROM growth_metrics
WHERE previous_month IS NOT NULL
ORDER BY month;

-- Ranking and analytical functions
SELECT
    product_name,
    category,
    price,
    -- Ranking within each category
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank,
    -- Running total
    SUM(price) OVER (PARTITION BY category ORDER BY price ROWS UNBOUNDED PRECEDING) as running_total,
    -- Moving average (3-row window)
    AVG(price) OVER (PARTITION BY category ORDER BY price ROWS 2 PRECEDING) as moving_avg,
    -- Percentage of category total
    ROUND(price / SUM(price) OVER (PARTITION BY category) * 100, 2) as pct_of_category
FROM products
ORDER BY category, price DESC;

-- Working with JSON data
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    profile_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create GIN index for JSON queries
CREATE INDEX idx_profile_data_gin ON user_profiles USING GIN (profile_data);

-- JSON queries and operations
INSERT INTO user_profiles (user_id, profile_data) VALUES
(1, '{"name": "John Doe", "age": 30, "skills": ["Java", "Python"], "address": {"city": "New York", "country": "USA"}}');

SELECT
    user_id,
    profile_data->>'name' as name,
    profile_data->'address'->>'city' as city,
    jsonb_array_length(profile_data->'skills') as skill_count
FROM user_profiles
WHERE profile_data @> '{"address": {"country": "USA"}}';

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- Create optimized indexes
CREATE INDEX CONCURRENTLY idx_users_created_at ON users (created_at);
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders (user_id, order_date);