flipside

    daily 2 tx
    yasmindaily 2 tx
    Updated 2 days 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: 1 day ago
    BLOCKCHAIN
    DATE
    TRANSACTIONS
    1
    		B32025-03-23 00:00:00.000571774
    2
    		Gunz2025-03-23 00:00:00.0001888224
    3
    		Ronin2025-03-23 00:00:00.000468579
    4
    		arbitrum2025-03-23 00:00:00.000771056
    5
    		base2025-03-23 00:00:00.0003358390
    6
    		ethereum2025-03-23 00:00:00.000435165
    7
    		polygon2025-03-23 00:00:00.0001359935
    8
    		solana2025-03-23 00:00:00.00020272356
    9
    		B32025-03-22 00:00:00.000962756
    10
    		Gunz2025-03-22 00:00:00.0002776332
    11
    		Ronin2025-03-22 00:00:00.000614122
    12
    		TON2025-03-22 00:00:00.0001867305
    13
    		arbitrum2025-03-22 00:00:00.0001641678
    14
    		base2025-03-22 00:00:00.0007383674
    15
    		ethereum2025-03-22 00:00:00.0001035004
    16
    		polygon2025-03-22 00:00:00.0002824014
    17
    		solana2025-03-22 00:00:00.00047028327
    18
    		B32025-03-21 00:00:00.0001039671
    19
    		Gunz2025-03-21 00:00:00.0002602038
    20
    		Ronin2025-03-21 00:00:00.000668259
    ...
    278
    12KB
    4s