Ez Lending Ohlc Rates

-- Daily interest rate volatility analysis
SELECT 
    day,
    protocol,
    platform,
    token_symbol,
    -- Supply rate volatility
    (supply_rate_high - supply_rate_low) / NULLIF(supply_rate_low, 0) * 100 AS supply_rate_volatility_pct,
    -- Variable borrow rate volatility
    (variable_borrow_rate_high - variable_borrow_rate_low) / NULLIF(variable_borrow_rate_low, 0) * 100 AS variable_borrow_volatility_pct,
    -- Rate spread
    variable_borrow_rate_close - supply_rate_close AS rate_spread,
    rate_updates_count
FROM <blockchain_name>.defi.ez_ohlc_rates
WHERE day >= CURRENT_DATE - 30
    AND token_symbol IS NOT NULL
ORDER BY day DESC, supply_rate_volatility_pct DESC;

-- Protocol comparison - average rates
SELECT 
    protocol,
    platform,
    token_symbol,
    AVG(supply_rate_close) AS avg_supply_rate,
    AVG(variable_borrow_rate_close) AS avg_variable_borrow_rate,
    AVG(stable_borrow_rate_close) AS avg_stable_borrow_rate,
    AVG(variable_borrow_rate_close - supply_rate_close) AS avg_rate_spread,
    COUNT(*) AS days_with_data
FROM <blockchain_name>.defi.ez_ohlc_rates
WHERE day >= CURRENT_DATE - 90
    AND token_symbol IS NOT NULL
GROUP BY 1, 2, 3
HAVING COUNT(*) > 30
ORDER BY avg_rate_spread DESC;

-- Interest rate trends over time
WITH rate_trends AS (
    SELECT 
        day,
        protocol,
        token_symbol,
        supply_rate_close,
        variable_borrow_rate_close,
        stable_borrow_rate_close,
        LAG(supply_rate_close, 7) OVER (PARTITION BY protocol, token_symbol ORDER BY day) AS supply_rate_week_ago,
        LAG(variable_borrow_rate_close, 7) OVER (PARTITION BY protocol, token_symbol ORDER BY day) AS variable_borrow_rate_week_ago
    FROM <blockchain_name>.defi.ez_ohlc_rates
    WHERE day >= CURRENT_DATE - 30
        AND token_symbol IS NOT NULL
)
SELECT 
    day,
    protocol,
    token_symbol,
    supply_rate_close,
    (supply_rate_close - supply_rate_week_ago) / NULLIF(supply_rate_week_ago, 0) * 100 AS supply_rate_change_7d_pct,
    variable_borrow_rate_close,
    (variable_borrow_rate_close - variable_borrow_rate_week_ago) / NULLIF(variable_borrow_rate_week_ago, 0) * 100 AS variable_borrow_rate_change_7d_pct
FROM rate_trends
WHERE supply_rate_week_ago IS NOT NULL
ORDER BY day DESC, ABS(supply_rate_change_7d_pct) DESC;

-- Most volatile interest rate assets
SELECT 
    protocol,
    platform,
    token_symbol,
    STDDEV(supply_rate_close) AS supply_rate_stddev,
    STDDEV(variable_borrow_rate_close) AS variable_borrow_rate_stddev,
    AVG(supply_rate_close) AS avg_supply_rate,
    AVG(variable_borrow_rate_close) AS avg_variable_borrow_rate,
    COUNT(*) AS days_with_data
FROM <blockchain_name>.defi.ez_ohlc_rates
WHERE day >= CURRENT_DATE - 90
    AND token_symbol IS NOT NULL
GROUP BY 1, 2, 3
HAVING COUNT(*) > 30
ORDER BY supply_rate_stddev DESC
LIMIT 20;

-- Rate update frequency analysis
SELECT 
    protocol,
    platform,
    token_symbol,
    AVG(rate_updates_count) AS avg_daily_updates,
    MAX(rate_updates_count) AS max_daily_updates,
    COUNT(*) AS days_with_data,
    SUM(rate_updates_count) AS total_updates
FROM <blockchain_name>.defi.ez_ohlc_rates
WHERE day >= CURRENT_DATE - 30
    AND token_symbol IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY avg_daily_updates DESC;

-- Asset-specific rate analysis
SELECT 
    day,
    protocol,
    platform,
    token_symbol,
    supply_rate_open,
    supply_rate_high,
    supply_rate_low,
    supply_rate_close,
    variable_borrow_rate_open,
    variable_borrow_rate_high,
    variable_borrow_rate_low,
    variable_borrow_rate_close,
    stable_borrow_rate_open,
    stable_borrow_rate_high,
    stable_borrow_rate_low,
    stable_borrow_rate_close,
    rate_updates_count
FROM <blockchain_name>.defi.ez_ohlc_rates
WHERE token_symbol = 'USDC'
    AND protocol = 'aave'
    AND day >= CURRENT_DATE - 7
ORDER BY day DESC;