WITH tps_data as (

SELECT

date(block_timestamp) as date,

count(tx_id)/86400 as tps,

count(tx_id) as total_txns

FROM solana.core.fact_transactions

WHERE

date >= dateadd(day, -30, getdate())

GROUP BY date

),

success_txns as (

SELECT

date(block_timestamp) as date,

count(tx_id) as success_txns

FROM solana.core.fact_transactions

WHERE

date >= dateadd(day, -30, getdate())

and succeeded = true

GROUP BY date

select t.date as day, tps, success_txns, total_txns, round((success_txns * 100)/total_txns,2) as success_rate

from tps_data t join success_txns s on t.date = s.date order by t.date desc