materialized_optimization.sql

-- CREATE MATERIALIZED VIEW sales_by_region AS
SELECT
    -- Dimension (Grouping Column)
    region,

    -- Total Sales per Region
    SUM(amount) AS total_sales,

    -- Number of Transactions per Region
    COUNT(*) AS transaction_count,

    -- Average Transaction Value per Region
    AVG(amount) AS avg_transaction_value
FROM sales
-- Aggregation Logic
GROUP BY region;

-- if MATERIALIZED VIEW is not supported, create a physical table instead
-- Create a physical table instead of a Materialized View
CREATE OR REPLACE TABLE sales_by_region_summary AS
SELECT 
    region,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count,
    AVG(amount) as avg_transaction_value
FROM sales
GROUP BY region;

-- Query the pre-calculated summary table
SELECT * FROM sales_by_region_summary
ORDER BY total_sales DESC;

-- Compare performance of original aggregation vs summary table
SELECT 
    CASE 
        WHEN query_text LIKE '%FROM sales%' AND query_text NOT LIKE '%sales_by_region_summary%' THEN 'Original Query (Slow)'
        WHEN query_text LIKE '%FROM sales_by_region_summary%' THEN 'Summary Table (Fast)'
    END as query_type,
    ROUND(total_elapsed_time / 1000, 2) as seconds,
    ROUND(bytes_scanned / 1024 / 1024, 2) as mb_scanned,
    rows_produced
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE (query_text LIKE '%SUM(amount)%' OR query_text LIKE '%FROM sales_by_region_summary%')
    AND query_text NOT LIKE '%QUERY_HISTORY%'
    AND query_text NOT LIKE '%CREATE%'
    AND execution_status = 'SUCCESS'
ORDER BY start_time DESC
LIMIT 5;