Query editor
WITHtime AS (WITH time_seq AS (SELECT sequence(CAST('2021-03-01 00:00' AS TIMESTAMP),date_trunc('day', CAST(now() AS TIMESTAMP)),INTERVAL '1' day) AS time)SELECT t.timeFROM time_seqCROSS JOIN UNNEST(time) AS t(time)),daily_txs AS (SELECTtime,SUM(tx_count_d) AS tx_count_dFROM (-- ethereumSELECT date_trunc('day', block_time) AS time, COUNT(hash) AS tx_count_d, 'Ethereum' AS chainFROM ethereum.transactionsWHERE ("from" = 0x942f9ce5d9a33a82f88d233aeb3292e680230348 OR "from" = 0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > CAST('2021-12-01 00:00:00' AS TIMESTAMP)AND success = trueGROUP BY 1UNION ALL-- polygonSELECT date_trunc('day', block_time), COUNT(hash), 'Polygon'FROM polygon.transactionsWHERE ("from" = 0x942f9ce5d9a33a82f88d233aeb3292e680230348 OR "from" = 0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > CAST('2021-12-01 00:00:00' AS TIMESTAMP)AND success = trueGROUP BY 1UNION ALL-- optimismSELECT date_trunc('day', block_time), COUNT(hash), 'Optimism'FROM optimism.transactionsWHERE ("from" = 0x942f9ce5d9a33a82f88d233aeb3292e680230348 OR "from" = 0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > CAST('2021-12-01 00:00:00' AS TIMESTAMP)AND success = trueGROUP BY 1UNION ALL-- arbitrumSELECT date_trunc('day', block_time), COUNT(hash), 'Arbitrum'FROM arbitrum.transactionsWHERE ("from" = 0x942f9ce5d9a33a82f88d233aeb3292e680230348 OR "from" = 0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > CAST('2021-12-01 00:00:00' AS TIMESTAMP)AND success = trueGROUP BY 1UNION ALL-- avalancheSELECT date_trunc('day', block_time), COUNT(hash), 'Avalanche'FROM avalanche_c.transactionsWHERE ("from" = 0x942f9ce5d9a33a82f88d233aeb3292e680230348 OR "from" = 0xcfafc6bdd1b92e510d5409ee460bb1a712165aa8)AND block_time > CAST('2021-12-01 00:00:00' AS TIMESTAMP)