boomer77Terra-BSC Top Assets Bridged
Updated 2021-12-03
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 raw as (select date_trunc('week', block_timestamp) as dt, msg_value:contract::string as asset, sum(msg_value:execute_msg:transfer:amount/1e6) as amount, count(distinct tx_id) as tx_count
from terra.msgs
where msg_value:execute_msg:transfer:recipient::string = 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2' and tx_status = 'SUCCEEDED'
group by 1,2),
raw2 as (select date_trunc('week', block_timestamp) as dt, msg_value:amount[0]:denom::string as asset, sum(msg_value:amount[0]:amount/1e6) as amount, count(distinct tx_id) as tx_count
from terra.msgs
where msg_value:to_address::string = 'terra1g6llg3zed35nd3mh9zx6n64tfw3z67w2c48tn2' and tx_status = 'SUCCEEDED'
group by 1,2),
label as (select address, address_name
from terra.labels),
final as (select dt, asset, amount, tx_count from raw
union
select dt, asset, amount, tx_count from raw2),
price as (select date_trunc('week', block_timestamp) as dt, currency, avg(price_usd) as price
from terra.oracle_prices
where currency in (select asset from final)
group by 1,2),
lol as (select a.dt, a.asset, a.amount, a.tx_count, b.address_name, c.price, case
when a.asset = 'uusd' then 'UST'
when a.asset = 'uluna' then 'LUNA'
when a.asset = 'ukrw' then 'KRT'
when a.asset = 'umnt' then 'MNT'
when a.asset = 'usdr' then 'SDT'
when a.asset = 'ueur' then 'EUT'
when a.asset = 'terra1mddcdx0ujx89f38gu7zspk2r2ffdl5enyz2u03' then 'ORION'
else b.address_name end as Assets
from final a
left join label b on a.asset = b.address
left join price c on a.asset = c.currency and a.dt = c.dt),
xx as (select dt, assets, amount, price, tx_count, case