daily_stats AS (

SELECT

block_timestamp::date AS utc_date

, (CASE WHEN utc_date >= CURRENT_DATE - interval '30 days' THEN 'Past 30 days' ELSE 'Past year' END) AS time_period

, count(*) AS transactions

, count(distinct account_keys[0]::string) AS unique_addresses

, count(*) / 24 / 60 / 60 AS tps

, avg(CASE WHEN succeeded = True THEN 100.0000 ELSE 0.0000 END) AS success_rate

FROM solana.core.fact_transactions

WHERE block_timestamp::date >= CURRENT_DATE - interval '365 days'

GROUP BY 1,2

SELECT *

FROM daily_stats

ORDER BY 1 DESC