select date_trunc('day', block_timestamp) as date, count(distinct(tx_hash)) as success_tx_num from avalanche.core.fact_transactions

where status = 'SUCCESS'

group by date

),

list2 as (

select date_trunc('day', block_timestamp) as date, count(distinct(tx_hash)) as failed_tx_num from avalanche.core.fact_transactions

where status = 'FAIL'

group by date

)

select list1.date, success_tx_num, failed_tx_num from list1 join list2 on list1.date = list2.date

group by list1.date

order by list1.date