SELECT

date_trunc('day', block_timestamp) as day,

COUNT(distinct txn_hash) as total_Txs

FROM flipside_prod_db.mdao_near.transactions

where day >= '2022-06-01'

GROUP BY 1

), failed as (

SELECT

date_trunc('day', block_timestamp) as day,

COUNT(distinct txn_hash) as failed_Txs

FROM flipside_prod_db.mdao_near.transactions

WHERE substr(tx_receipt[0]:"outcome":"status",3,7) Like '%Fail%'

and day >= '2022-06-01'

GROUP BY 1

select t.day, total_Txs, failed_Txs, failed_Txs/total_Txs*100 as failed_percentage, avg(failed_percentage) over (order by t.day) as avg_failed_percentage

from total_txs t left join failed f on t.day=f.day

order by 1 desc