abrahamsmithUntitled Query
Updated 2022-11-30
99
1
2
3
4
5
6
7
8
9
10
›
⌄
WITH
avalanche as (SELECT BLOCK_TIMESTAMP, BLOCK_NUMBER, COUNT (DISTINCT TX_HASH) as TXN from avalanche.core.fact_transactions WHERE BLOCK_TIMESTAMP >= CURRENT_DATE -180 GROUP BY 1,2 ),
arbitrum as (SELECT BLOCK_TIMESTAMP, BLOCK_NUMBER, COUNT (DISTINCT TX_HASH) as TXN from arbitrum.core.fact_transactions WHERE BLOCK_TIMESTAMP >= CURRENT_DATE -180 GROUP BY 1,2 ),
optimism as (SELECT BLOCK_TIMESTAMP, BLOCK_NUMBER, COUNT (DISTINCT TX_HASH) as TXN from optimism.core.fact_transactions WHERE BLOCK_TIMESTAMP >= CURRENT_DATE -180 GROUP BY 1,2 )
SELECT 'Avalanche' as chain, date_trunc ('week',BLOCK_TIMESTAMP) as DATE,avg (TXN) as New_User from avalanche GROUP by 2 UNION
SELECT 'Arbitrum' as chain, date_trunc ('week',BLOCK_TIMESTAMP) as DATE,avg (TXN) as New_User from arbitrum GROUP by 2 UNION
SELECT 'Optimism' as chain, date_trunc ('week',BLOCK_TIMESTAMP) as DATE,avg (TXN) as New_User from optimism GROUP by 2