New Stats Table on Flipside!

    Introduction
    Code Comparison

    My Query Before

    Comparisons between chains consistently yield valuable insights. This is because members of each chain are interested in assessing the performance of their preferred chain against other popular ones. Consider a scenario where I aim to analyze transactions across multiple chains. It's worth noting that the combined monthly transactions of some well-known chains exceed 100 million. However, there are two significant challenges. Firstly, extracting the transaction count for each blockchain individually is a cumbersome task. Secondly, processing such a vast amount of transactions takes considerable time to produce results.

    Moreover, making changes to any parameter necessitates revisiting and editing queries in the codes of all chains, which can be quite labor-intensive.

    Ultimately, you must possess SQL knowledge to understand how to effectively join these tables.

    select trunc(block_timestamp,'day') as day,  
    'Aptos' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from aptos.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION ALL
    select trunc(block_timestamp,'day') as day, 
    'Polygon' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from polygon.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION ALL
    select trunc(block_timestamp,'day') as day, 
    'Ethereum' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from ethereum.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION ALL
    select trunc(block_timestamp,'day') as day, 
    'Avalanche' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from avalanche.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION ALL
    select trunc(block_timestamp,'day') as day, 
    'Optimism' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from optimism.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION ALL
    select trunc(block_timestamp,'day') as day, 
    'Arbitrum' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from arbitrum.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'BSC' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from bsc.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Gnosis' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from gnosis.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Flow' as chain,
    count(distinct tx_id) as total_tx,
    total_tx/86400 as tps
    from flow.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Near' as chain,
    count(DISTINCT tx_hash) as total_tx,
    total_tx/86400 as tps
    from near.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Sei' as chain,
    count(distinct tx_id) as total_tx,
    total_tx/86400 as tps
    from sei.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Cosmos' as chain,
    count(distinct tx_id) as total_tx,
    total_tx/86400 as tps
    from cosmos.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Osmosis' as chain,
    count(distinct tx_id) as total_tx,
    total_tx/86400 as tps
    from osmosis.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Axelar' as chain,
    count(distinct tx_id) as total_tx,
    total_tx/86400 as tps
    from axelar.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Aurora' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from aurora.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Bitcoin' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from bitcoin.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    UNION
    select trunc(block_timestamp,'day') as day, 
    'Base' as chain,
    count(distinct tx_hash) as total_tx,
    total_tx/86400 as tps
    from base.core.fact_transactions
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    group by 1,2
    

    My Query with the new Stats Table

    With Flipside's new "Stats" table, you don't have to deal with processing over 100 million transactions yourself. They've already done the work and neatly organized everything. Now, you can get results for more than 17 blockchains in just a few seconds. No need to be an SQL expert – it's as simple as using less than five lines of code.

    select trunc(block_timestamp_hour,'day') as day,
    blockchain,
    sum(TRANSACTION_COUNT) as total_tx,
    total_tx/86400 as tps
    from crosschain.stats.ez_core_metrics_hourly 
    where day BETWEEN DATEADD('DAY', -30, CURRENT_DATE()) AND CURRENT_DATE() - 1
    and blockchain != 'solana'
    group by 1,2
    
    Loading...
    Loading...

    Flipside Crypto recently added new tables for all available chains on Flipside, including one called Stats in the Crosschain section. The Ez_Core_metrics_hourly table makes it much easier to compare more than 17 blockchains together than before.

    Before, if you wanted to compare transactions on these chains, you had to write a query for each chain separately using chains tables. With join or union, you connect each blockchain, and it could take more than 100 lines of code. But with the new table, all you need is less than 5 lines. If you don't have prior experience with SQL, this table allows you to easily extract data like transactions, blocks, users, fees, and more.

    Below, I've created sample charts to illustrate the difference between using chains tables and the Stats Table.

    db_img
    db_img