Query editor
withtime AS(with time_seq as (SELECTsequence(cast('2022-09-04 00:00' as TIMESTAMP), date_trunc('day', cast(now() as TIMESTAMP) + interval '1' day), interval '1' day) as time)SELECTtime.timeFROM time_seqCROSS JOIN unnest(time) as time(time)),daily_txs as (SELECTtime, sum(tx_count_d) as tx_count_dfrom (--polygonSELECTdate_trunc('day', block_time) as time, count(hash) as tx_count_d, 'Polygon' as chainfrom polygon.transactionswhere to = 0xe957a692c97566efc85f995162fa404091232b2eand block_time > cast('2022-09-04 00:00:00' as TIMESTAMP)and success = trueGROUP by 1)yGROUP by 1)SELECTtime, tx_count_d--, chain, sum(tx_count_d) over () as total_tx--, sum(tx_count_d) over (partition by chain order by time) as total_tx_chain, sum(tx_count_d) over (order by time) as cum_tx_count, avg(tx_count_d) over (order by time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as tx_count_7dma, avg(tx_count_d) over (order by time ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as tx_count_30dmafrom (SELECTt.time, COALESCE(d.tx_count_d, 0) as tx_count_dfrom time tleft join daily_txs don t.time = d.time)xwhere time < date_trunc('day', now() + interval '1' day)order by1 desc, 2 desc