SocioCryptotransfers - last N days cross chain
Updated 2022-12-15
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
›
⌄
SELECT CASE WHEN SUBSTR(to_char(sender), 0, 4) = 'osmo' then 'osmo'
when SUBSTR(to_char(sender), 0, 4) = 'axel' then 'axelar'
when SUBSTR(to_char(sender), 0, 4) = 'grav' then 'GRAV'
when SUBSTR(to_char(sender), 0, 4) = 'secr' then 'secret'
when SUBSTR(to_char(sender), 0, 4) = 'terr' then 'terra'
when SUBSTR(to_char(sender), 0, 3) = 'cre' then 'CRE'
when SUBSTR(to_char(sender), 0, 3) = 'sif' then 'SIF'
when SUBSTR(to_char(sender), 0, 4) = 'kuji' then 'kujira'
when SUBSTR(to_char(sender), 0, 4) = 'cosm' then 'cosmos'
when SUBSTR(to_char(sender), 0, 4) = 'evmo' then 'evmos'
when SUBSTR(to_char(sender), 0, 4) = 'stri' then 'STRI'
when SUBSTR(to_char(sender), 0, 4) = 'juno' then 'juno'
else SUBSTR(to_char(sender), 0, 4) end as sender_category,
CASE WHEN SUBSTR(to_char(receiver), 0, 4) = 'osmo' then 'osmo'
when SUBSTR(to_char(receiver), 0, 4) = 'axel' then 'axelar'
when SUBSTR(to_char(receiver), 0, 4) = 'grav' then 'GRAV'
when SUBSTR(to_char(receiver), 0, 4) = 'secr' then 'secret'
when SUBSTR(to_char(receiver), 0, 4) = 'terr' then 'terra'
when SUBSTR(to_char(receiver), 0, 3) = 'cre' then 'CRE'
when SUBSTR(to_char(receiver), 0, 3) = 'sif' then 'SIF'
when SUBSTR(to_char(receiver), 0, 4) = 'kuji' then 'kujira'
when SUBSTR(to_char(receiver), 0, 4) = 'cosm' then 'cosmos'
when SUBSTR(to_char(receiver), 0, 4) = 'evmo' then 'evmos'
when SUBSTR(to_char(receiver), 0, 4) = 'stri' then 'STRI'
when SUBSTR(to_char(receiver), 0, 4) = 'juno' then 'juno'
else SUBSTR(receiver, 0, 4) end as receiver_category,
count(DISTINCT tx_id) as n_transfers,
sum(TO_NUMBER (amount))/pow(10,6) as amnt,
median(TO_NUMBER (amount))/pow(10,6) as avg_amnt
FROM terra.core.ez_transfers
WHERE currency = 'uluna' AND date_trunc('day',block_timestamp) >= current_date-{{Last_N_days}} AND transfer_type != 'IBC_Transfer_Off'
GROUP by sender_category, receiver_category
Run a query to Download Data