count(distinct tx_from) as unique_user,

count(*)/86400/count(distinct block_timestamp::date) as tx_per_sec,

count(distinct tx_id) as tx_count,

case when tx_succeeded = TRUE then 'Success'

else 'Fail' end as type,

AVG(tx_per_sec)

OVER (partition by type ORDER BY date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

AS rolling_tps

from sei.core.fact_transactions

where block_timestamp::date < CURRENT_DATE

-- and block_timestamp::date >= '2023-07-13'

group by date, type

order by date desc, type desc