SELECT

block_id,

block_timestamp,

tx_id,

tx_from,

SUBSTR(fee, LENGTH(REGEXP_SUBSTR(fee, '\\d+')) + 1) AS asset,

REGEXP_SUBSTR(fee, '\\d+')::numeric AS fee_amount

FROM osmosis.core.fact_transactions

WHERE tx_succeeded

AND block_timestamp > current_date - 30

AND fee_amount > 0

SELECT

case when block_id < 8732500 then 'Before Update'

else 'After Update' end as state,

case when asset = 'uosmo' then 'uosmo Fee'

else 'Other asset Fee' end as type,

count(DISTINCT tx_id) AS total_tx,

count(DISTINCT tx_from) as total_users,

sum(price * fee_amount / POW(10, decimal)) AS total_fee_usd,

(total_tx/ total_users) AS avg_txs_per_user

FROM tbl

LEFT JOIN osmosis.core.dim_tokens ON address = asset

JOIN osmosis.core.ez_prices ON trunc(block_timestamp,'hour') = recorded_hour

AND asset = currency

GROUP BY 1,2