CryptoLionterra fees and actions
    Updated 2021-11-10
    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