date(hour) as date,

case

when symbol = 'WBNB' then 'BNB'

when symbol = 'WMATIC' then 'MATIC'

when symbol = 'WETH' then 'ETH'

when symbol = 'WAVAX' then 'AVAX'

end as symbol,

avg(price) as daily_price

from ethereum.core.fact_hourly_token_prices

where symbol in ('WAVAX', 'WBNB', 'WMATIC', 'WETH')

and HOUR >= '2023-02-02'

and HOUR < CURRENT_DATE

group by 1,2

order by 1,2),

all_data as ((with x as (with main as (select

BLOCK_TIMESTAMP,

tx_hash,

ORIGIN_FROM_ADDRESS,

ORIGIN_TO_ADDRESS,

CONTRACT_ADDRESS,

event_index,

substr(data, 515, 128) as adress,

regexp_substr_all (SUBSTR(data, 3, len(data)),'.{64}') as Decoded,

data

from ethereum.core.fact_event_logs

where ORIGIN_TO_ADDRESS = '0xce16f69375520ab01377ce7b88f5ba8c48f8d666'

and CONTRACT_ADDRESS = '0x2d5d7d31f671f86c782533cc367f14109a082712'

and EVENT_name is NULL

and event_inputs is null

order by 6)

select

BLOCK_TIMESTAMP,

tx_hash,

ORIGIN_FROM_ADDRESS,