blockchain ,

avg(success_rate) as avg_success

from (

select *

from (

select

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

'Klaytn[kaia]' 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,

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

from kaia.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 = 'klaytn'

and is_native = 'TRUE'

and symbol = 'KLAY'

group by 1

order by date desc

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

group by 1 , 2

union all

select

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

blockchain,

sum(block_count) as n_blocks,

sum(transaction_count) as n_transaction,