YinkaTop 10 Pools by 24hr Volume copy
    -- forked from metcalfe / Top 10 Pools by 24hr Volume @ https://flipsidecrypto.xyz/metcalfe/q/Kn326bPiIUF5/top-10-pools-by-24hr-volume

    with daily_volume as(select
    pool_name,
    .5*(sum(abs(amount0_usd))+sum(abs(amount1_usd))) as "24hr_volume"
    from uniswapv3.swaps
    where block_timestamp >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
    group by 1),

    weekly_volume as(select
    pool_name,
    .5*(sum(abs(amount0_usd))+sum(abs(amount1_usd))) as "7day_volume"
    from uniswapv3.swaps
    where block_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP())
    group by 1),

    pool_stats as (select
    distinct pool_name,
    pool_address,
    token0_address,
    token1_address,
    last_value(token0_balance_usd+token1_balance_usd) over (partition by pool_name,pool_address order by block_timestamp) as tvl,
    last_value(tick) over (partition by pool_name,pool_address order by block_timestamp) as tick,
    last_value(price_0_1) over (partition by pool_name,pool_address order by block_timestamp) as price_0_1,
    last_value(price_1_0) over (partition by pool_name,pool_address order by block_timestamp) as price_1_0
    from uniswapv3.pool_stats),

    penult_pool_stats as (select
    pool_stats.pool_name,
    pool_stats.tick,
    pool_stats.price_0_1,
    pool_stats.price_1_0,
    "24hr_volume",
    "7day_volume",
    tvl,
    pool_address,