select

date_trunc(week,BLOCK_TIMESTAMP) as date ,

iff(substr(tx_receipt[0]:"outcome":"status",3,7) ilike '%Success%',true,false) as status ,

(count(1)/ (7*24*60)) as tpm

from flipside_prod_db.mdao_near.transactions

where BLOCK_TIMESTAMP::date >= '2022-01-01'

-- where try_BASE64_DECODE_string(TX_RECEIPT[0]:outcome:status:SuccessValue) in (true,false)

group by 1,2 having status = true

),

ETH as (

select date_trunc(week,BLOCK_TIMESTAMP) as date ,

(count ( tx_hash)/(7*24*60) ) as tpm

from ethereum.core.fact_transactions

where BLOCK_TIMESTAMP::date >= '2022-01-01'

and STATUS = 'SUCCESS'

group by 1

),

sol as (

select date_trunc(week,BLOCK_TIMESTAMP) as date ,

(count (tx_id)/(7*24*60)) as tpm

from solana.core.fact_transactions

where BLOCK_TIMESTAMP::date >= '2022-01-01'

and SUCCEEDED = true

group by 1

),

all_ as ( select 'NEAR' as block_chain , date , tpm from all_transactions_near

UNION

select 'Ethereum' as block_chain , date ,tpm from ETH

UNION

select 'SOLANA' as block_chain , date ,tpm from sol

)

select block_chain ,

avg(tpm) as avg_tpm

from all_

group by 1