kiacryptoshare of two tokens of each pools
    Updated 2022-11-16
    with current_price as (
    select token_address, avg(price) as price
    from optimism.core.fact_hourly_token_prices
    where hour::date = current_date - 1
    group by 1
    ),
    vol as (
    select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, sum(token0_amount) as tok0_vol, sum(token1_amount) as tok1_vol
    from optimism.velodrome.ez_lp_actions
    where lp_action = 'deposit' and lp_token_amount_usd is not null
    group by 1, 2, 3, 4, 5
    union all
    select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, -sum(token0_amount) as tok0_vol, -sum(token1_amount) as tok1_vol
    from optimism.velodrome.ez_lp_actions
    where lp_action = 'withdraw' and lp_token_amount_usd is not null
    group by 1, 2, 3, 4, 5
    ),
    liq as (
    select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, sum(tok0_vol) as tok0_liquidity, sum(tok1_vol) as tok1_liquidity
    from vol
    group by 1, 2, 3, 4, 5
    )
    select pool_name, token0_symbol as symbol, tok0_liquidity * price as vol_usd, 'token 0' as type
    from liq, current_price
    where token_address = token0_address

    union all
    select pool_name, token1_symbol as symbol, tok1_liquidity * price as vol_usd, 'token 1' as type
    from liq, current_price
    where token_address = token1_address
    Run a query to Download Data