0xtableauTerra Stablecoin Fees in UST
    Updated 2022-07-07
    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