select

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

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

count(DISTINCT from_address) as n_active_users,

sum(tx_fee * b.price) as total_fee_usd,

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

from swell.core.fact_transactions a

left join (

select

date_trunc('day', hour) as date,

median(price) as price

from crosschain.price.ez_prices_hourly

where blockchain = 'ethereum'

and is_native = 'TRUE'

group by 1

order by date desc

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

group by 1 , 2

order by date desc