select block_timestamp,

tx_hash,

status,

label_type,

project_name

from optimism.core.fact_transactions a

join optimism.core.dim_labels

on to_address = address

where block_timestamp::date >= '2022-07-01' and block_timestamp::date <= '2022-07-31'

),

success_txs as ( -- success

select

date(block_timestamp) as date

, label_type

, count(*) as tx_count

from all_txs

where status = 'SUCCESS'

group by date, label_type),

failed_txs as ( -- failure

select

date(block_timestamp) as date

, label_type

, count(*) as tx_count

from all_txs

where status = 'FAIL'

group by date, label_type)

select

s.date

, s.label_type

, s.tx_count as success_count

, f.tx_count as failed_count

, success_count+failed_count as tx_count