date_trunc('{{granularity}}', BLOCK_TIMESTAMP) as date,

'Mezo' as blockchain,

count(BLOCK_NUMBER) as n_blocks,

count(DISTINCT tx_hash) as n_transaction,

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_ / (coalesce(TRANSACTION_COUNT_SUCCESS_,0) + coalesce(TRANSACTION_COUNT_FAILED_,0)) as success_rate,

count(DISTINCT from_address) as n_active_users,

sum(tx_fee) as tx_fee_btc,

avg(tx_fee) as avg_tx_fee,

avg(tx_fee * b.price) as avg_tx_fee_usd,

sum(tx_fee_btc) over (order by date_trunc('{{granularity}}', BLOCK_TIMESTAMP)) as cumulative_fee_btc,

sum(tx_fee * b.price) as tx_fee_usd,

sum(tx_fee_usd) over (order by date_trunc('{{granularity}}', BLOCK_TIMESTAMP)) as cumulative_fee_usd

from mezo.testnet.fact_transactions a

left join (

select

date_trunc('day', hour) as date,

median(price) as price

from crosschain.price.ez_prices_hourly

where blockchain = 'bitcoin'

and is_native = 'TRUE'

group by 1

) b ON date_trunc('day', block_timestamp) = b.date

group by 1 , 2

order by date desc

-- 20225-03-28