with

price_{{Blokchain}} as (

select

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

avg(PRICE) as avg_Price

from

{{Blokchain}}.price.ez_hourly_token_prices

where

1=1

and TOKEN_ADDRESS is not NULL

and symbol in (case when '{{Blokchain}}' = 'Avalanche' then 'WAVAX'

when '{{Blokchain}}' = 'Bsc' then 'WBNB' else 'WETH' end)

group by

1

),

platforms as (

select

tx_hash

from {{Blokchain}}.defi.ez_dex_swaps

where 1=1

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

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

),

tab1 as (

block_timestamp,

tx_hash,

TX_FEE * avg_Price as TX_FEE_USD

from

{{Blokchain}}.core.fact_transactions a

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

where

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

and tx_hash in (select tx_hash from platforms)