with near_to_ethereum as ( --=============================================>>>>>>>>>>>>>>>> NEAR to ETH <<<<<<<<<<<<<<<<<<<<<<==================================================

with bridge_ERC20_from_near_to_ethereum as (

select block_timestamp::date as date, txn_hash::string as txn_hash, (parse_json(trim(args))):amount as asset_amount, (parse_json(args)):recipient as eth_address

from flipside_prod_db.mdao_near.actions_events_function_call

where action_name = 'FunctionCall'

and method_name = 'withdraw' -- ERC20 tokens

and args::string like '{"%'

and (parse_json(trim(args))):amount::string is not null

and (parse_json(trim(args))):recipient::string is not null

), bridge_NEAR_from_near_to_ethereum as (

select block_timestamp::date as date, txn_hash::string as txn_hash, deposit as asset_amount, (parse_json(args)):eth_recipient as eth_address

from flipside_prod_db.mdao_near.actions_events_function_call

where action_name = 'FunctionCall'

and method_name = 'migrate_to_ethereum' -- NEAR

and args::string like '{"%'

and (parse_json(args)):eth_recipient is not null

), bridge_all_from_near_to_ethereum as (

select * from bridge_ERC20_from_near_to_ethereum

union all

select * from bridge_NEAR_from_near_to_ethereum

), join_with_txs as (

select date, txn_hash, asset_amount, eth_address, TX_RECEIVER as contract_in_near, TX_SIGNER as sender_near_address,

case

when contract_in_near = 'e-near.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

from bridge_all_from_near_to_ethereum left join flipside_prod_db.mdao_near.transactions using(txn_hash)

), erc20_prices as ( -- ============== Prices ==============

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 join_with_txs)

group by 1,2,3

), near_prices as (