select block_timestamp,

tx_hash,

status,

label_type,

project_name

from avalanche.core.fact_transactions a

join avalanche.core.dim_labels

on to_address = address

where block_timestamp::date >= current_date - 90 and block_timestamp::date <= current_date-1

),

success_txs as ( -- success

select

date_trunc('day', block_timestamp) as dt

, label_type

, count(*) as tx_count

from all_txs

where status = 'SUCCESS'

group by dt, label_type),

failed_txs as ( -- failure

select

date_trunc('day', block_timestamp) as dt

, label_type

, count(*) as tx_count

from all_txs

where status = 'FAIL'

group by dt, label_type)

select

s.dt

, s.label_type

, s.tx_count as success_count

, f.tx_count as failed_count

, success_count+failed_count as tx_count