nitsUsers who used Shuttle less (in Average amt transferred)but atleast used shuttle once after using wormhole
Updated 2022-01-19
99
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 transactions_on_wormhole as (select *,SUBSTRING(msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom,0,LEN(msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom)) as denominations ,
(msg_value:execute_msg:initiate_transfer:asset:amount)/pow(10,6) as amount ,
msg_value:sender as sender_address ,
msg_value:execute_msg:initiate_transfer:recipient_chain as chain_number ,
msg_value:execute_msg:initiate_transfer:asset:info:token:contract_addr as contract_address,
len(denominations) as length_
from terra.msgs
where msg_value:contract = 'terra10nmmwe8r3g99a9newtqa7a75xfgs2e8z87r2sf' and amount> '0' and tx_status = 'SUCCEEDED'),
sent_across_the_bridge as (
select
*
from terra.msgs m
where (msg_value:to_address::string = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc' or msg_value:to_address::string = 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2')
and tx_status = 'SUCCEEDED'
),
prices as (select date(block_timestamp) as day,currency, avg(price_usd) as avg_price from terra.oracle_prices
group by day, currency ),
shuttle_bridge_users as ( select block_timestamp as bt , avg_price*amt as total_amt,addr from
(select *, msg_value:from_address as addr, msg_value:amount[0]:denom as denominations, msg_value:amount[0]:amount/pow(10,6) as amt from sent_across_the_bridge
where tx_status = 'SUCCEEDED')
inner join prices on currency =denominations and date(block_timestamp) = day ),
wormhole_amt as (select block_timestamp, avg_price*amount as total_amt, sender_address
from transactions_on_wormhole
inner join prices on currency =denominations and date(block_timestamp) = day),
first_use_details_wormhole as (
select sender_address, min(block_timestamp) as first_use, count(*) as total_use, avg(total_amt) as avg_wormhole_amt from wormhole_amt
group by sender_address),
first_use_details_shuttle as (
select addr, min(bt) as first_use from shuttle_bridge_users
group by addr)
Run a query to Download Data