abrahamsmithUntitled Query
    Updated 2022-11-30
    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