-- Original table with default distribution
CREATE TABLE sales_facts (
    sale_id BIGINT,
    customer_id BIGINT,
    product_id BIGINT,
    sale_date DATE,
    amount DECIMAL(10,2)
) DISTSTYLE AUTO;

-- Optimized version with strategic distribution key
CREATE TABLE sales_facts_optimized (
    sale_id BIGINT,
    customer_id BIGINT DISTKEY,
    product_id BIGINT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
COMPOUND SORTKEY (customer_id, sale_date);

-- Customer dimension table with matching distribution
CREATE TABLE customers (
    customer_id BIGINT DISTKEY,
    customer_name VARCHAR(255),
    segment VARCHAR(50)
)
COMPOUND SORTKEY (customer_id);

-- Create materialized view for monthly sales summary
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    c.segment as customer_segment,
    p.product_category,
    COUNT(*) as total_orders,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM sales_facts sf
JOIN customers c ON sf.customer_id = c.customer_id
JOIN products p ON sf.product_id = p.product_id
GROUP BY 1, 2, 3;

-- Refresh strategy for near real-time dashboards
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- Dashboard query now hits pre-computed results
SELECT 
    month,
    customer_segment,
    total_revenue,
    avg_order_value
FROM monthly_sales_summary 
WHERE month >= '2024-01-01'
ORDER BY month DESC, total_revenue DESC;

-- WLM configuration for dashboard workloads
-- Apply via AWS Console or parameter groups
{
  "query_group": "dashboard_queries",
  "memory_percent_to_use": 30,
  "max_execution_time": 30000,
  "query_concurrency": 15,
  "user_group": ["dashboard_users"],
  "query_group_wild_card": 0
}

-- Set query group at session level for dashboard connections
SET query_group TO 'dashboard_queries';

-- Typical dashboard query with optimized execution
SELECT 
    region,
    SUM(revenue) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales_summary_mv 
WHERE report_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region
ORDER BY total_revenue DESC;

-- Dashboard query that benefits from caching
SELECT 
    DATE_TRUNC('week', order_date) as week,
    SUM(order_amount) as weekly_revenue,
    COUNT(*) as weekly_orders,
    AVG(order_amount) as avg_order_size
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE order_date >= CURRENT_DATE - INTERVAL '12 weeks'
    AND c.region = 'North America'
GROUP BY 1
ORDER BY 1 DESC;

-- Original table without strategic sorting
CREATE TABLE sales_transactions (
    transaction_id BIGINT,
    customer_id BIGINT,
    transaction_date TIMESTAMP,
    product_category VARCHAR(50),
    amount DECIMAL(10,2),
    region VARCHAR(50)
);

-- Optimized version with compound sort keys for dashboard patterns
CREATE TABLE sales_transactions_optimized (
    transaction_id BIGINT,
    customer_id BIGINT,
    transaction_date TIMESTAMP,
    product_category VARCHAR(50),
    amount DECIMAL(10,2),
    region VARCHAR(50)
)
COMPOUND SORTKEY (transaction_date, region, product_category);

-- Dashboard query that benefits from zone map pruning
SELECT 
    product_category,
    region,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count
FROM sales_transactions_optimized
WHERE transaction_date >= '2024-01-01'
    AND transaction_date < '2024-04-01'
    AND region IN ('West', 'East')
GROUP BY product_category, region
ORDER BY total_sales DESC;

-- Create external schema pointing to S3 historical data (AWS Glue Data Catalog)
CREATE EXTERNAL SCHEMA historical_data
FROM DATA CATALOG 
DATABASE 'analytics_archive'
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftSpectrumRole';

-- External table for historical sales data in S3
CREATE EXTERNAL TABLE historical_data.sales_archive (
    sale_id BIGINT,
    customer_id BIGINT,
    sale_date DATE,
    amount DECIMAL(10,2),
    product_category VARCHAR(100)
)
STORED AS PARQUET
LOCATION 's3://your-analytics-bucket/sales/archive/';

-- Query combining current cluster data with historical S3 data
SELECT 
    DATE_TRUNC('year', sale_date) as year,
    product_category,
    SUM(amount) as total_revenue
FROM (
    -- Recent data from cluster
    SELECT sale_date, product_category, amount 
    FROM sales_facts 
    WHERE sale_date >= '2024-01-01'
    
    UNION ALL
    
    -- Historical data from S3
    SELECT sale_date, product_category, amount
    FROM historical_data.sales_archive
    WHERE sale_date >= '2020-01-01' AND sale_date < '2024-01-01'
) combined_sales
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- WLM configuration for mixed analytical workloads
-- Configure via AWS Console parameter groups

-- Queue 1: High-priority operational queries
{
  "query_group": "operations",
  "memory_percent_to_use": 40,
  "max_execution_time": 60000,
  "query_concurrency": 10,
  "user_group": ["business_users", "dashboard_service"]
}

-- Queue 2: Medium-priority ad-hoc analytics  
{
  "query_group": "analytics", 
  "memory_percent_to_use": 35,
  "max_execution_time": 300000,
  "query_concurrency": 5,
  "user_group": ["analysts", "data_scientists"]
}

-- Queue 3: Low-priority bulk operations
{
  "query_group": "bulk_operations",
  "memory_percent_to_use": 25, 
  "max_execution_time": 1800000,
  "query_concurrency": 2,
  "user_group": ["etl_service", "data_engineers"]
}

-- Set appropriate query group for analytical sessions
SET query_group TO 'analytics';

-- Complex analytical query with proper queue assignment
WITH customer_cohorts AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', first_order_date) as cohort_month,
        EXTRACT(YEAR FROM first_order_date) as cohort_year
    FROM (
        SELECT 
            customer_id,
            MIN(order_date) as first_order_date
        FROM orders
        GROUP BY customer_id
    ) first_orders
),
monthly_activity AS (
    SELECT 
        c.customer_id,
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        DATEDIFF(month, c.cohort_month, DATE_TRUNC('month', o.order_date)) as period_number,
        SUM(o.order_amount) as monthly_revenue
    FROM customer_cohorts c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY 1, 2, 3, 4
)
SELECT 
    cohort_month,
    period_number,
    COUNT(DISTINCT customer_id) as active_customers,
    SUM(monthly_revenue) as total_revenue,
    AVG(monthly_revenue) as avg_revenue_per_customer
FROM monthly_activity
WHERE period_number BETWEEN 0 AND 12
GROUP BY 1, 2
ORDER BY 1, 2;

-- Original complex query with multiple JOINs
-- This approach often leads to suboptimal execution plans
SELECT 
    c.customer_segment,
    p.product_category,
    s.store_region,
    COUNT(*) as order_count,
    SUM(o.order_amount) as total_revenue,
    AVG(o.order_amount) as avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id  
JOIN stores s ON o.store_id = s.store_id
JOIN promotions pr ON o.promotion_id = pr.promotion_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
    AND c.customer_segment IN ('Enterprise', 'Mid-Market')
    AND p.product_category IN ('Software', 'Hardware')
GROUP BY 1, 2, 3;

-- Optimized approach using staging table strategy
-- Step 1: Create filtered staging table
CREATE TEMP TABLE order_staging AS
SELECT 
    o.order_id,
    o.customer_id,
    o.product_id,
    o.store_id,
    o.promotion_id,
    o.order_amount,
    o.order_date
FROM orders o
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Step 2: Build final result with simpler JOINs
SELECT 
    c.customer_segment,
    p.product_category,
    s.store_region,
    COUNT(*) as order_count,
    SUM(os.order_amount) as total_revenue,
    AVG(os.order_amount) as avg_order_value
FROM order_staging os
JOIN customers c ON os.customer_id = c.customer_id
    AND c.customer_segment IN ('Enterprise', 'Mid-Market')
JOIN products p ON os.product_id = p.product_id
    AND p.product_category IN ('Software', 'Hardware')  
JOIN stores s ON os.store_id = s.store_id
GROUP BY 1, 2, 3;

-- Create optimized table with strategic compression
CREATE TABLE customer_transactions_optimized (
    transaction_id BIGINT ENCODE DELTA32K,
    customer_id BIGINT ENCODE DELTA32K,
    transaction_date DATE ENCODE DELTA32K,
    product_sku VARCHAR(50) ENCODE LZO,
    category VARCHAR(30) ENCODE BYTEDICT,
    subcategory VARCHAR(50) ENCODE BYTEDICT,
    amount DECIMAL(12,2) ENCODE DELTA32K,
    discount_pct DECIMAL(5,2) ENCODE BYTEDICT,
    payment_method VARCHAR(20) ENCODE BYTEDICT,
    channel VARCHAR(20) ENCODE BYTEDICT,
    region VARCHAR(30) ENCODE BYTEDICT,
    store_id INTEGER ENCODE DELTA32K,
    sales_rep_id INTEGER ENCODE DELTA32K,
    promotion_code VARCHAR(20) ENCODE LZO
)
DISTKEY(customer_id)
COMPOUND SORTKEY(transaction_date, customer_id);

-- Copy data with automatic compression analysis
INSERT INTO customer_transactions_optimized
SELECT * FROM customer_transactions;

-- Analytical query benefiting from optimized compression
SELECT 
    category,
    region,
    DATE_TRUNC('month', transaction_date) as month,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_transaction,
    COUNT(DISTINCT customer_id) as unique_customers
FROM customer_transactions_optimized
WHERE transaction_date >= '2024-01-01'
    AND region IN ('Northeast', 'Southeast', 'West')
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

-- Base analytical query with common pattern
CREATE TEMP TABLE monthly_customer_metrics AS
SELECT 
    customer_id,
    DATE_TRUNC('month', transaction_date) as month,
    COUNT(*) as transaction_count,
    SUM(amount) as total_spent,
    AVG(amount) as avg_transaction,
    MAX(amount) as max_transaction,
    COUNT(DISTINCT product_category) as category_diversity
FROM customer_transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY customer_id, DATE_TRUNC('month', transaction_date);

-- Analysts can now build variations without rescanning base data
-- Customer segmentation analysis
SELECT 
    month,
    CASE 
        WHEN total_spent >= 10000 THEN 'High Value'
        WHEN total_spent >= 1000 THEN 'Medium Value'
        ELSE 'Low Value'
    END as customer_segment,
    COUNT(DISTINCT customer_id) as customer_count,
    SUM(total_spent) as segment_revenue,
    AVG(avg_transaction) as avg_transaction_size
FROM monthly_customer_metrics
GROUP BY month, customer_segment
ORDER BY month DESC, segment_revenue DESC;

-- Transaction frequency analysis using same base data
SELECT 
    month,
    CASE
        WHEN transaction_count >= 20 THEN 'Frequent'
        WHEN transaction_count >= 5 THEN 'Regular'
        ELSE 'Occasional'
    END as frequency_tier,
    COUNT(DISTINCT customer_id) as customer_count,
    AVG(total_spent) as avg_monthly_spend
FROM monthly_customer_metrics
GROUP BY month, frequency_tier
ORDER BY month DESC, customer_count DESC;

-- Suboptimal COPY approach (single large file)
COPY sales_transactions 
FROM 's3://data-bucket/sales/sales_data_2024.csv.gz'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftLoadRole'
CSV
GZIP;

-- Optimized COPY with multiple files and parallel processing
COPY sales_transactions_optimized 
FROM 's3://data-bucket/sales/partitioned/'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftLoadRole'
FORMAT AS PARQUET
COMPUPDATE OFF
STATUPDATE OFF;

-- Advanced COPY with manifest file for precise control
COPY customer_transactions
FROM 's3://data-bucket/manifest/daily_load_manifest.json'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftLoadRole'
MANIFEST
FORMAT AS PARQUET
COMPUPDATE OFF
STATUPDATE OFF;

-- Manifest file structure for optimal loading
{
  "entries": [
    {"url": "s3://data-bucket/transactions/dt=2024-01-01/part-00000.parquet", "mandatory": true},
    {"url": "s3://data-bucket/transactions/dt=2024-01-01/part-00001.parquet", "mandatory": true},
    {"url": "s3://data-bucket/transactions/dt=2024-01-01/part-00002.parquet", "mandatory": true}
  ]
}

-- Strategic VACUUM approach for different table patterns
-- For tables with frequent INSERT/DELETE patterns
VACUUM DELETE ONLY sales_transactions;

-- For tables requiring sort order maintenance  
VACUUM SORT ONLY customer_activity;

-- Full vacuum for tables with both issues (use sparingly)
VACUUM FULL customer_transactions;

-- Use WLM query group for maintenance operations
SET query_group TO 'maintenance';

-- Create staging table for incremental changes
CREATE TEMP TABLE customer_updates_staging (
    customer_id BIGINT,
    customer_name VARCHAR(255),
    email VARCHAR(255),
    status VARCHAR(50),
    last_updated TIMESTAMP,
    change_type VARCHAR(10) -- INSERT, UPDATE, DELETE
);

-- Load incremental data from S3
COPY customer_updates_staging
FROM 's3://data-bucket/incremental/customers/dt=2024-01-15/'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftLoadRole'
FORMAT AS PARQUET;

-- Implement UPSERT pattern for incremental updates
BEGIN TRANSACTION;

-- Handle deletions first
DELETE FROM customers 
WHERE customer_id IN (
    SELECT customer_id 
    FROM customer_updates_staging 
    WHERE change_type = 'DELETE'
);

-- Handle updates and inserts using staging merge pattern
DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM customer_updates_staging 
    WHERE change_type IN ('UPDATE', 'INSERT')
);

-- Insert updated and new records
INSERT INTO customers (
    customer_id,
    customer_name, 
    email,
    status,
    last_updated
)
SELECT 
    customer_id,
    customer_name,
    email, 
    status,
    last_updated
FROM customer_updates_staging
WHERE change_type IN ('UPDATE', 'INSERT');

COMMIT;

-- Alternative approach using MERGE statement (when available)
MERGE INTO customers 
USING customer_updates_staging s
ON customers.customer_id = s.customer_id
WHEN MATCHED AND s.change_type = 'UPDATE' THEN
    UPDATE SET 
        customer_name = s.customer_name,
        email = s.email,
        status = s.status,
        last_updated = s.last_updated
WHEN NOT MATCHED AND s.change_type = 'INSERT' THEN
    INSERT (customer_id, customer_name, email, status, last_updated)
    VALUES (s.customer_id, s.customer_name, s.email, s.status, s.last_updated);