theericstoneTVL by Hour by Pool V3
    Updated 2021-07-16
    WITH hourly as (
    SELECT
    DISTINCT date_trunc('hour',pstat.block_timestamp) as block_hour,
    pstat.pool_address,
    pool_name,
    last_value( VIRTUAL_RESERVES_TOKEN0_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN1_ADJUSTED,-1) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as native_price0,
    last_value( VIRTUAL_RESERVES_TOKEN1_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN0_ADJUSTED,-1) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as native_price1,
    last_value(tph0.price) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as price0,
    last_value(tph1.price) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as price1,
    last_value(token0_balance_adjusted) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as gross_reserves_token0_adjusted,
    last_value(token1_balance_adjusted) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as gross_reserves_token1_adjusted,

    last_value( sqrt(virtual_reserves_token0_usd * virtual_reserves_token1_usd) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as virtual_liquidity,
    price0 * gross_reserves_token0_adjusted as token0_gross_usd,
    price1 * gross_reserves_token1_adjusted as token1_gross_usd,

    CASE WHEN price0 IS NULL and price1 IS NULL THEN 'no prices'
    WHEN price0 IS NULL and price1 IS NOT NULL THEN 'price1'
    WHEN price1 IS NULL and price0 IS NOT NULL THEN 'price0'
    ELSE 'both prices'
    END AS price_status
    FROM uniswapv3.pool_stats pstat
    LEFT JOIN ethereum.token_prices_hourly tph0
    ON tph0.hour = date_trunc('hour',pstat.block_timestamp)
    AND pstat.token0_address = tph0.token_address
    LEFT JOIN ethereum.token_prices_hourly tph1
    ON tph1.hour = date_trunc('hour',pstat.block_timestamp)
    AND pstat.token1_address = tph1.token_address
    WHERE pstat.block_timestamp >= getdate() - interval '1 days'
    Run a query to Download Data