tx_id,

block_timestamp,

fee,

b.value as fee_parsed,

b.value:amount[0] as parsed,

parsed:denom as fee_denom,

parsed:amount/1e6 as fee_amount

FROM terra.transactions, TABLE(FLATTEN(terra.transactions.fee)) b

where tx_id not in (select s.tx_id from terra.swaps s )

and fee_amount>0

and block_timestamp >= '2021-09-01')

,

Fee_USD as

( SELECT f.block_timestamp, f.fee_denom,

case when f.fee_denom='uusd' then f.fee_amount else f.fee_amount*(select

avg(price_usd)

from terra.oracle_prices o,flattenTable f

where o.CURRENCY = f.fee_denom and o.block_timestamp=CURRENT_DATE) end as Fee_USD

from flattentable f)

select

--date_trunc('year',block_timestamp) as block_year,

date_trunc('day',block_timestamp) as block_day,

count(fee_usd) volume,

sum(round(fee_usd,0)) as fee_usd

from fee_usd group by --block_year,

block_day order by --block_year,

block_day