with aur_to_near as (

with bridge_from_aurora_to_near as (

select

BLOCK_TIMESTAMP::date as date,

tx_hash,

tx:receipt[1]:outcome:executor_id as contract_in_near,

case

when contract_in_near = 'wrap.near' then 'NEAR'

when contract_in_near = 'aurora' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH

else concat('0x', substring(contract_in_near, 1, CHARINDEX('.', contract_in_near)-1))

end as token_address,

tx:receipt[1]:outcome:logs[0] as log,

substring(log, 1, CHARINDEX(' from aurora to ', log)) as first_part,

regexp_replace(first_part, '[^0-9]', '') as asset_amount,

substring(log, CHARINDEX('from aurora to ', log)+15, len(log)) as receiver

from near.core.fact_transactions n_t

--

where tx_signer = 'relay.aurora'

and tx_receiver = 'aurora'

and (contract_in_near like '%.factory.bridge.near' -- ERC20-Assets

or contract_in_near like 'aurora' -- ETH

or contract_in_near like 'wrap.near' -- NEAR

)

and tx:receipt[1]:outcome:logs[0] like 'Transfer % from aurora %'

-- and receiver = 'mohammadhs.near'

), erc20_prices as (

select hour::date as date, symbol, token_address, avg(price) as price

from ethereum.core.fact_hourly_token_prices

where token_address in (select distinct token_address from bridge_from_aurora_to_near)

group by 1,2,3

), near_prices as (

with swaps as (

select