CryptoLionterra fees and actions
Updated 2021-11-10
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
›
⌄
WITH t0 as (
SELECT
date_trunc('hour',block_timestamp) as hour,
currency,
symbol,
avg(price_usd) as oracle_price
FROM terra.oracle_prices
WHERE block_timestamp >= getdate() - interval '60 days'
GROUP BY 1,2,3
ORDER BY 1
),
t1 as (
SELECT DISTINCT
date_trunc('hour',tr.block_timestamp) as hour,
sum(tf.event_amount_usd) as transfer_volume,
sum(offer_amount_usd) as swap_volume,
sum(st.event_amount_usd) as staking_volume,
sum(gas_used) as gas_used,
sum(gas_wanted) as gas_wanted,
SUM(CASE
WHEN fee[0]:denom::string = 'usdr' THEN fee[0]:amount::float/POW(10,6) * oracle_price --sdt
WHEN fee[0]:denom::string = 'uusd' THEN fee[0]:amount::float/POW(10,6) * oracle_price --ust
WHEN fee[0]:denom::string = 'uluna' THEN fee[0]:amount::float/POW(10,6) * oracle_price --luna
WHEN fee[0]:denom::string = 'umnt' THEN fee[0]:amount::float/POW(10,6) * oracle_price --mnt
WHEN fee[0]:denom::string = 'ukrw' THEN fee[0]:amount::float/POW(10,6) * oracle_price --krw
WHEN fee[0]:denom::string = 'uaud' THEN fee[0]:amount::float/POW(10,6) * oracle_price --aut
WHEN fee[0]:denom::string = 'ucad' THEN fee[0]:amount::float/POW(10,6) * oracle_price --cat
WHEN fee[0]:denom::string = 'uchf' THEN fee[0]:amount::float/POW(10,6) * oracle_price --cht
WHEN fee[0]:denom::string = 'ucny' THEN fee[0]:amount::float/POW(10,6) * oracle_price --cnt
WHEN fee[0]:denom::string = 'ueur' THEN fee[0]:amount::float/POW(10,6) * oracle_price --eut
WHEN fee[0]:denom::string = 'ugbp' THEN fee[0]:amount::float/POW(10,6) * oracle_price --gbt
WHEN fee[0]:denom::string = 'uinr' THEN fee[0]:amount::float/POW(10,6) * oracle_price --int
WHEN fee[0]:denom::string = 'ujpy' THEN fee[0]:amount::float/POW(10,6) * oracle_price --jpt
WHEN fee[0]:denom::string = 'usek' THEN fee[0]:amount::float/POW(10,6) * oracle_price --set
WHEN fee[0]:denom::string = 'uthb' THEN fee[0]:amount::float/POW(10,6) * oracle_price --tht
ELSE 0
Run a query to Download Data