0xtableauTerra Stablecoin Fees in UST
Updated 2022-07-07
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
›
⌄
WITH ORACLE_PRICES AS (
SELECT DATE(BLOCK_TIMESTAMP) as BLOCK_DATE,
SYMBOL,
AVG(price_USD) as AVG_DAILY_PRICE
FROM terra.oracle_prices
WHERE DATEDIFF(D, BLOCK_DATE, CURRENT_DATE) <= 30
GROUP BY BLOCK_DATE, SYMBOL
),
FEES_PAID AS (
SELECT
DATE(block_timestamp) as BLOCK_DATE,
UPPER(SUBSTRING(fee[0]:denom::string, 2, 100)) as currency,
sum(fee[0]:amount)/POW(10,6) as fee
FROM terra.transactions
WHERE CURRENCY IS NOT NULL AND DATEDIFF(D, BLOCK_DATE, CURRENT_DATE) <= 30
GROUP BY BLOCK_DATE, CURRENCY
)
SELECT o.BLOCK_DATE, SYMBOL, fee, avg_daily_price,
fee * avg_daily_price AS AVG_FEE_IN_USD
FROM ORACLE_PRICES o
INNER JOIN FEES_PAID f
ON o.BLOCK_DATE = f.BLOCK_DATE AND SUBSTRING(currency, 1, 2) = SUBSTRING(symbol, 1, 2)
WHERE fee is NOT NULL
ORDER BY 1, 2;
Run a query to Download Data