price_{{Blokchain}} as (

select

date_trunc({{Granularity}}, HOUR) as date,

TOKEN_ADDRESS,

avg(PRICE) as avg_Price

from

{{Blokchain}}.price.ez_hourly_token_prices

where 1=1

and TOKEN_ADDRESS is not NULL

group by

1,

2

)

date::date as date,

POOL_NAME,

round("Swap Volume ($)", 2) as "Swap Volume ($)",

round(sum("Swap Volume ($)") over (partition by POOL_NAME

order by

date

), 2) as "Cum. Swap Volume ($)"

(

SELECT

date_trunc({{Granularity}}, block_timestamp) as date,

POOL_NAME,

sum(AMOUNT_IN * avg_Price) as "Swap Volume ($)"

from

{{Blokchain}}.defi.ez_dex_swaps a

join price_{{Blokchain}} b on date_trunc({{Granularity}}, block_timestamp) = b.date

and a.TOKEN_IN = b.TOKEN_ADDRESS

where

BLOCK_TIMESTAMP::date between '{{Start_date}}' and '{{End_date}}'

and platform in ('trader-joe-v1', 'trader-joe-v2')

group by