zakkisyedUntitled Query
Updated 2022-03-30
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
›
⌄
with rawfees as (
SELECT
block_timestamp::date as metric_date,
case when chain_id = 'columbus-5' then UPPER(SUBSTRING(fee[0]:amount[0]:denom::string, 2, 2))
else UPPER(SUBSTRING(fee[0]:denom::string, 2, 2)) end as fee_denom,
sum(case when chain_id = 'columbus-5' then fee[0]:amount[0]:amount
else fee[0]:amount end)/POW(10,6) as amount,
avg(case when chain_id = 'columbus-5' then fee[0]:amount[0]:amount
else fee[0]:amount end)/POW(10,6) as avg_amount
FROM terra.transactions
WHERE fee_denom IS NOT NULL
GROUP BY metric_date, fee_denom
ORDER BY 1,2
),
prices as (
select
block_timestamp::date as metric_date,
symbol,
SUBSTRING(symbol,1,2) as fee_denom,
avg(price_usd) as price
from terra.oracle_prices
where symbol in ('UST', 'SDT', 'AUT', 'CAT', 'EUT', 'JPT', 'KRT', 'LUNA', 'MNT')
group by 1, 2, 3
)
select
r.metric_date as BLOCK_DATE,
sum(amount * price) as amount,
sum(avg_amount * price) as avg_amount
from rawfees r join prices p
on r.metric_date = p.metric_date
and r.fee_denom = p.fee_denom
group by 1
order by 1 desc, 2
Run a query to Download Data