CryptoIcicleAurora -> NEAR Bridge Txns
Updated 2022-08-16
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
›
⌄
-- NEAR Tournament Round IV: The Journeymen
-- The foundations of a great city are built by its citizens; where they’ve been, where they’ll go, what they do along the way.
-- Payout 42 NEAR
-- Grand Prize 126 NEAR
-- Payout Network Near
-- Level Intermediate
-- Difficulty Hard
-- Analyze the journey of active NEAR users.
-- After bridging to NEAR, where do they head first?
-- How many users are day trading in the financial district,
-- and how many are buyers and sellers on the NFT marketplaces of the Arts district?
-- Who are the whales, and is their activity any different from that of smaller fish?
-- Top submissions should provide an in-depth analysis of the typical NEAR user journey, from small fish to the biggest whales.
-- SQL Credit https://app.flipsidecrypto.com/velocity/queries/3625b5e6-ceea-4e93-8e48-28fc1897c370
-- Dashbord: https://app.flipsidecrypto.com/dashboard/near-citizens-fQRyBU
with bridge_from_aurora_to_near as (
select
BLOCK_TIMESTAMP::date as date,
txn_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 flipside_prod_db.mdao_near.transactions n_t
Run a query to Download Data