daily_volume as (

select

Date(block_timestamp) as on_date

,pool_address

,pool_name

,LEAST(sum(abs(amount0_usd)), sum(abs(amount1_usd))) as swap_usd_volume

from ethereum.uniswapv3.ez_swaps

where amount0_usd is not null and amount1_usd is not null

group BY 1, 2, 3)

, daily_stats as (

select

on_date

,pool_address

,pool_name

,swap_usd_volume as swap_usd_volume_on_date

,sum(swap_usd_volume) over(partition by pool_address order by on_date rows between 6 preceding and current row) as swap_usd_volume_in_7_days

,sum(swap_usd_volume) over(partition by pool_address order by on_date rows between 29 preceding and current row) as swap_usd_volume_in_30_days

,sum(swap_usd_volume) over(partition by pool_address order by on_date rows between 89 preceding and current row) as swap_usd_volume_in_90_days

,sum(swap_usd_volume) over(partition by pool_address order by on_date rows between 179 preceding and current row) as swap_usd_volume_in_180_days

,sum(swap_usd_volume) over(partition by pool_address order by on_date rows between 364 preceding and current row) as swap_usd_volume_in_365_days

from daily_volume

select * from daily_stats

where on_date = {{select_date}}

order by SWAP_USD_VOLUME_ON_DATE desc