flipside

    daily 2 tx
    yasmindaily 2 tx
    Updated 3 hours ago
    WITH
    standard_chains AS (
    SELECT
    BLOCKCHAIN as blockchain,
    BLOCK_TIMESTAMP_HOUR::date AS date,
    SUM(TRANSACTION_COUNT_SUCCESS) AS transactions
    FROM
    crosschain.stats.ez_core_metrics_hourly
    WHERE
    BLOCK_TIMESTAMP_HOUR::date >= DATEADD(DAY, -{{period}}, CURRENT_DATE )
    AND BLOCKCHAIN IN ('arbitrum', 'base', 'polygon', 'solana','ethereum')
    GROUP BY blockchain, date
    ),
    ronin_data AS (
    SELECT
    'Ronin' AS blockchain,
    block_timestamp::date AS date,
    COUNT(DISTINCT TX_HASH) AS transactions,
    COUNT(DISTINCT FROM_ADDRESS) AS users
    FROM ronin.core.fact_transactions
    WHERE block_timestamp::date >= DATEADD(DAY, -{{period}}, CURRENT_DATE )
    AND TX_SUCCEEDED = true
    GROUP BY blockchain, date
    ),
    -------------------- GUN blockchain data (from Avalanche API) with multiple API calls -------------------------
    gun_transactions AS (
    WITH base_data AS (
    SELECT
    live.udf_api('https://metrics.avax.network/v2/chains/49321/metrics/txCount?startTimestamp=1726272000&timeInterval=day&pageSize=2160') AS response
    ),
    flattened_data AS (
    SELECT
    to_timestamp(tx.value:timestamp)::date AS date,
    tx.value:value::integer AS daily_transactions
    FROM base_data,
    LATERAL FLATTEN(input => response:data:results) AS tx)
    Last run: about 3 hours ago
    BLOCKCHAIN
    DATE
    TRANSACTIONS
    1
    		B32025-03-21 00:00:00.000644386
    2
    		Gunz2025-03-21 00:00:00.0001759234
    3
    		Ronin2025-03-21 00:00:00.000519892
    4
    		arbitrum2025-03-21 00:00:00.000819258
    5
    		base2025-03-21 00:00:00.0003717413
    6
    		ethereum2025-03-21 00:00:00.000494917
    7
    		polygon2025-03-21 00:00:00.0001346739
    8
    		solana2025-03-21 00:00:00.00020394609
    9
    		B32025-03-20 00:00:00.0001062652
    10
    		Gunz2025-03-20 00:00:00.0002336771
    11
    		Ronin2025-03-20 00:00:00.000689071
    12
    		arbitrum2025-03-20 00:00:00.0001852112
    13
    		base2025-03-20 00:00:00.0007770728
    14
    		ethereum2025-03-20 00:00:00.0001195211
    15
    		polygon2025-03-20 00:00:00.0002837074
    16
    		solana2025-03-20 00:00:00.00047522813
    17
    		B32025-03-19 00:00:00.0001175298
    18
    		Gunz2025-03-19 00:00:00.0002330160
    19
    		Ronin2025-03-19 00:00:00.000740701
    20
    		arbitrum2025-03-19 00:00:00.0001968426
    ...
    274
    12KB
    8s