date_trunc(day, block_timestamp) as "Date"

, count(tx_id) as "Transactions"

, count_if(succeeded) as "Successful Transactions"

, "Transactions" - "Successful Transactions" as "Failed Transactions"

, avg("Transactions") over (order by "Date" rows between 7 preceding and current row) as "7 Day Avg. Tx"

from eclipse.core.fact_transactions

where block_timestamp < current_date

group by 1

order by "Date"