Ez Prices Hourly

-- Calculate USD value for token transfers
SELECT 
    t.block_timestamp,
    t.tx_hash,
    t.symbol,
    t.from_address,
    t.to_address,
    t.amount,
    p.price,
    t.amount * p.price AS usd_value,
    p.is_imputed
FROM <blockchain_name>.core.ez_token_transfers t
JOIN <blockchain_name>.price.ez_prices_hourly p
    ON t.contract_address = p.token_address
    AND DATE_TRUNC('hour', t.block_timestamp) = p.hour
WHERE t.block_timestamp >= CURRENT_DATE - 7
    AND t.amount > 0
    AND p.price IS NOT NULL
ORDER BY usd_value DESC
LIMIT 100;

-- 24-hour price volatility for major tokens
WITH price_stats AS (
    SELECT 
        symbol,
        token_address,
        DATE(hour) AS date,
        MIN(price) AS daily_low,
        MAX(price) AS daily_high,
        AVG(price) AS daily_avg,
        STDDEV(price) AS daily_stddev,
        (MAX(price) - MIN(price)) / MIN(price) * 100 AS daily_range_pct
    FROM <blockchain_name>.price.ez_prices_hourly
    WHERE hour >= CURRENT_DATE - 30
        AND symbol IN ('WETH', 'USDC', 'USDT', 'WBTC', 'DAI')
        AND NOT is_imputed
    GROUP BY 1, 2, 3
)
SELECT 
    symbol,
    AVG(daily_range_pct) AS avg_daily_volatility,
    MAX(daily_range_pct) AS max_daily_volatility,
    AVG(daily_stddev / daily_avg) * 100 AS avg_cv_pct
FROM price_stats
GROUP BY 1
ORDER BY 2 DESC;

-- Track native asset prices across chains
SELECT 
    blockchain,
    symbol,
    hour,
    price,
    price / LAG(price, 24) OVER (PARTITION BY blockchain ORDER BY hour) - 1 AS change_24h,
    price / LAG(price, 168) OVER (PARTITION BY blockchain ORDER BY hour) - 1 AS change_7d
FROM <blockchain_name>.price.ez_prices_hourly
WHERE is_native = TRUE
    AND hour >= CURRENT_DATE - 8
    AND hour = DATE_TRUNC('hour', CURRENT_TIMESTAMP) - INTERVAL '1 hour'
ORDER BY blockchain;

-- Monitor stablecoin deviations from $1
SELECT 
    symbol,
    hour,
    price,
    ABS(price - 1.0) AS deviation,
    CASE 
        WHEN ABS(price - 1.0) > 0.05 THEN 'Severe Depeg'
        WHEN ABS(price - 1.0) > 0.01 THEN 'Mild Depeg'
        ELSE 'Stable'
    END AS peg_status
FROM <blockchain_name>.price.ez_prices_hourly
WHERE symbol IN ('USDC', 'USDT', 'DAI', 'BUSD', 'FRAX', 'LUSD')
    AND hour >= CURRENT_DATE - 7
    AND ABS(price - 1.0) > 0.005
ORDER BY hour DESC, deviation DESC;

-- Analyze price data completeness and imputation rates
SELECT 
    symbol,
    COUNT(*) AS total_hours,
    SUM(CASE WHEN is_imputed THEN 1 ELSE 0 END) AS imputed_hours,
    ROUND(100.0 * SUM(CASE WHEN is_imputed THEN 1 ELSE 0 END) / COUNT(*), 2) AS imputation_rate,
    MIN(hour) AS first_price,
    MAX(hour) AS last_price,
    COUNT(DISTINCT DATE(hour)) AS days_with_data
FROM <blockchain_name>.price.ez_prices_hourly
WHERE hour >= CURRENT_DATE - 30
GROUP BY 1
HAVING COUNT(*) > 100
ORDER BY imputation_rate DESC;