max(BLOCK_NUMBER) as LATEST_BLOCK,

count(DISTINCT tx_hash) as n_transaction,

count(distinct from_address) as active_accounts,

count(DISTINCT case when TX_SUCCEEDED = 'TRUE' then tx_hash end) as TRANSACTION_COUNT_SUCCESS_,

count(DISTINCT case when TX_SUCCEEDED = 'FALSE' then tx_hash end) as TRANSACTION_COUNT_FAILED_,

TRANSACTION_COUNT_SUCCESS_ / (TRANSACTION_COUNT_SUCCESS_ + TRANSACTION_COUNT_FAILED_) as success_rate,

sum(tx_fee) as total_fee_btc,

sum(tx_fee * b.price) as total_fee_usd,

sum(tx_fee * b.price) / count(DISTINCT tx_hash) as average_fee_usd

from mezo.testnet.fact_transactions a

left join (

select

price

from crosschain.price.ez_prices_hourly

where blockchain = 'bitcoin'

and is_native = 'TRUE'

qualify row_number() over (order by hour desc) = 1

) b ON TRUE