with daily as (
select
date_trunc('day', block_timestamp) as day,
count(tx_id) as transactions,
count(iff(TX_SUCCEEDED = 'TRUE', 1, NULL)) as succeeded_transactions,
transactions /(3600 * 24) as tps_with_fails,
succeeded_transactions /(3600 * 24) as tps,
succeeded_transactions / transactions as success_rate
from
terra.core.fact_transactions
group by day
order by day DESC
)
SELECT date_trunc('week', day) as date, avg(tps) as "Weekly TPS" from daily
GROUP by date