CryptoIcicleNEAR -> Ethereum
Updated 2022-08-07
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
›
⌄
-- Provide and explore key health metrics, as well as detailed data on at least 1 key project.
-- Payout 17.123 NEAR
-- Grand Prize 51.37 NEAR
-- Payout Network Near
-- Level Intermediate
-- Difficulty Hard
-- SQL Credit https://app.flipsidecrypto.com/velocity/queries/1d4b4746-da57-4448-a41f-d5a87f22671a
-- Dashbord: https://app.flipsidecrypto.com/dashboard/near-citizens-fQRyBU
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))
Run a query to Download Data