dan-smithUntitled Query
    Updated 2022-09-07
    WITH usdc AS (
    SELECT DISTINCT
    BLOCK_HOUR AS time
    ,RESERVE_NAME AS token
    ,AVG(SUPPLY_RATE) AS supply_rate
    ,AVG(BORROW_RATE_VARIABLE) AS borrow_rate
    FROM ethereum.aave.ez_market_stats
    WHERE BLOCK_HOUR <= CURRENT_DATE
    AND AAVE_VERSION = 'Aave V2'
    AND token = 'USDC'
    GROUP BY 2, 1
    ORDER BY 1 DESC
    ),

    eth AS (
    SELECT DISTINCT
    BLOCK_HOUR AS time
    ,RESERVE_NAME AS token
    ,AVG(SUPPLY_RATE) AS supply_rate
    ,AVG(BORROW_RATE_VARIABLE) AS borrow_rate
    FROM ethereum.aave.ez_market_stats
    WHERE BLOCK_HOUR <= CURRENT_DATE
    AND AAVE_VERSION = 'Aave V2'
    AND token = 'WETH'
    GROUP BY 2, 1
    ORDER BY 1 DESC
    ),

    timeseries AS (
    SELECT
    TIMEADD('hour', row_number() over (order by 1), DATE_TRUNC('hour', CURRENT_TIMESTAMP()+ interval '3h') - interval '365d')::timestamp AS timeseries
    FROM TABLE(GENERATOR(ROWCOUNT => 365*24))
    ORDER BY 1 DESC
    ),

    aave AS (
    Run a query to Download Data