mhmNEAR -> AURORA
Updated 2022-07-14Copy Reference Fork
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with bridges_from_near_to_aurora as (
select *
from flipside_prod_db.mdao_near.receipts
where status_value:SuccessReceiptId is not null
), flatten_logs as ( -- // Flatten log values
select
block_timestamp::date as date,
txn_hash,
logs.value as log
from
bridges_from_near_to_aurora bridge,
lateral flatten(input => bridge.logs) logs
where log like 'Transfer % from %.near to aurora'
), attach_asset as (
select date, txn_hash, log, TX_RECEIVER, tx_signer
from flatten_logs f left join flipside_prod_db.mdao_near.transactions t using(txn_hash)
), explode_log as (
select
date,
txn_hash,
log,
TX_RECEIVER as contract_in_near,
TX_SIGNER as sender_near_address,
case
when contract_in_near = 'wrap.near' then 'NEAR'
when contract_in_near = 'usn' then 'USN'
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,
substring(log, 1, CHARINDEX(' from ', log)) as first_part,
regexp_replace(first_part, '[^0-9]', '') as asset_amount
from attach_asset
where (contract_in_near like '%.factory.bridge.near' -- ERC20-Assets
or contract_in_near like 'aurora' -- ETH
or contract_in_near like 'wrap.near' -- NEAR
or contract_in_near like 'usn' -- USN
Run a query to Download Data