Query editor
withtime AS (withtime_seq as (SELECTsequence(date_add('day', -60, current_date),date_trunc('day', cast(now() as TIMESTAMP)),interval '1' day) as time)SELECTtime.timeFROMtime_seqCROSS JOIN unnest (time) as time (time)),-- 2) union all the v1 + v2 tx-counts, grouped by day & chaindaily_txs AS (SELECTtime,chain,SUM(tx_count_d) AS tx_count_dFROM (-- Ethereum v1SELECTdate_trunc('day', block_time) AS time,COUNT(hash) AS tx_count_d,'Ethereum' AS chainFROM ethereum.transactionsWHERE"from" IN (0x942f9ce5d9a33a82f88d233aeb3292e680230348,0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > date_add('day', -60, current_date)AND success = trueGROUP BY 1UNION ALL-- Ethereum v2SELECTDATE_TRUNC('day', t.block_time) AS time,COUNT(*) AS tx_count_d,'Ethereum' AS chainFROM ethereum.logs lJOIN ethereum.transactions t ON l.tx_hash = t.hashWHEREl.topic0 = 0x49628fd1471006c1482da88028e9ce4dbb080b815c9b0344d39e5a8e6ec1419fAND l.topic3 = 0x000000000000000000000000a8b267c68715fa1dca055993149f30217b572cf0AND t.block_time > date_add('day', -60, current_date)AND t.success = trueGROUP BY 1UNION ALL-- Polygon (v1 only)SELECTdate_trunc('day', block_time) AS time,