jqb44077Untitled Query
    Updated 2022-03-07
    with daily as (select
    date_trunc('day',block_timestamp) as day,
    pool_name,
    pool_address as pool_pyco,
    avg(price_0_1) as avg_price_0,
    min(price_0_1) as min_price_0,
    max(price_0_1) as max_price_0,
    avg(price_1_0) as avg_price_1,
    min(price_1_0) as min_price_1,
    max(price_1_0) as max_price_1,
    sum(abs(amount0_usd)*.5+abs(amount1_usd)*.5) as volume
    from uniswapv3.swaps
    where day > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and day <= '2022-02-28 23:00:00.000'
    group by 1,2,3),

    pools as (
    select fee_percent,
    date_trunc('day',block_timestamp) as daya,
    pool_name as fee_pool_name,
    pool_address as adress
    from uniswapv3.positions
    where daya > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and daya <= '2022-02-28 23:00:00.000'
    group by 1,2,3,4),

    tvl as (select
    date_trunc('day',block_timestamp) as tvl_day,
    pool_name as tvl_pool_name,
    avg(token0_balance_usd + token1_balance_usd) as avg_tvl
    from uniswapv3.pool_stats
    where tvl_day > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and tvl_day <= '2022-02-28 23:00:00.000'
    group by 1,2),
    merged_daily as (
    select * from daily
    join tvl on pool_name = tvl_pool_name and day = tvl_day
    inner join pools on pool_pyco = adress and day = daya
    Run a query to Download Data