messariUST transaction count by protocol type (to Q1 2021)
Updated 2022-04-29
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
›
⌄
-- Tarik - revised from query by sriram https://app.flipsidecrypto.com/dashboard/how-is-ust-being-used-ZSAAdk
with terraswap as (select * from terra.labels
where label in ('terraswap','Terraswap') and LABEL_TYPE='dex' and address_name like '%UST Pair'),
A as (select date_trunc('day',block_timestamp) as date,count(0) as tx_count_a
from terra.msgs
where (CHAIN_ID='columbus-4' or CHAIN_ID='columbus-5') and TX_STATUS='SUCCEEDED' and msg_value:contract IN(select address from terraswap)
and msg_value:execute_msg:provide_liquidity IS NOT NULL and date >= CAST('2021-01-01' as Date)
and coalesce(msg_value:execute_msg:provide_liquidity:assets[0]:info:token:contract_addr::string,
msg_value:execute_msg:provide_liquidity:assets[0]:info:native_token:denom::string)='uusd'
group by 1
order by date asc
),
B as (select date_trunc('day',block_timestamp) as date,count(0) as tx_count_b
from terra.msgs
where (CHAIN_ID='columbus-4' or CHAIN_ID='columbus-5') and TX_STATUS='SUCCEEDED' and msg_value:contract IN(select address from terraswap)
and msg_value:execute_msg:provide_liquidity IS NOT NULL and date >= CAST('2021-01-01' as Date)
and coalesce(msg_value:execute_msg:provide_liquidity:assets[1]:info:token:contract_addr::string,
msg_value:execute_msg:provide_liquidity:assets[1]:info:native_token:denom::string)='uusd'
group by 1
order by date asc)
(select COALESCE(A.date,B.date) as date,COALESCE(tx_count_a,0)+COALESCE(tx_count_b,0) as tx_count,'Swaps' as action
from A full outer join B
on A.date=B.date
order by date asc)
UNION ALL
(select date_trunc('day',block_timestamp) as date,count(0) as tx_count,'Bridging' as action from terra.transfers
where event_currency='UST' and (CHAIN_ID='columbus-4' or CHAIN_ID='columbus-5') and date >= CAST('2021-01-01' as Date) and TX_STATUS='SUCCEEDED' and
((EVENT_FROM_LABEL_TYPE='chadmin' and EVENT_FROM_ADDRESS_LABEL='terra') or
(EVENT_TO_LABEL_TYPE='chadmin' and EVENT_TO_ADDRESS_LABEL='terra'))
group by 1
order by date asc)
UNION ALL
(select date_trunc('day',block_timestamp) as date,count(0) as tx_count,'Exchange interactions' as action from terra.transfers
where event_currency='UST' and (CHAIN_ID='columbus-4' or CHAIN_ID='columbus-5') and date >= CAST('2021-01-01' as Date) and TX_STATUS='SUCCEEDED'
and (EVENT_TO_LABEL_TYPE='cex' or EVENT_FROM_LABEL_TYPE='cex')
Run a query to Download Data