In a previous article, "Crafting Visualizations with LiveQuery and Chainbase API," we took readers on a journey through the multifaceted world of data visualization using some APIs of Chainbase available.

If you missed it, catch up here: Crafting Visualizations with LiveQuery and Chainbase API

Today, we're exploring the function deeper, seeking insights from the vast ocean of zkSync public chain data. We'll spotlight key transactions and address details, enhancing our discoveries with livequery.

Introduction to zkSync Era

zkSync Era, based on ZK rollup technology, represents a second-layer scaling solution that brings scalable and cost-effective transactions to Ethereum. ZK rollups utilize cryptographic validity proofs to ensure all transactions on zkSync are valid, with the majority of computation and data storage happening off-chain, enhancing efficiency and speed. Despite this off-chain approach, all transactions are validated on the Ethereum mainchain, ensuring a security level equivalent to Ethereum. One of the distinguishing features of zkSync compared to Ethereum is its method of address derivation and memory management. Additionally, zkSync offers fully Ethereum-compatible smart contract capabilities, allowing developers an easy migration between the two platforms. At the same time, zkSync introduces novel features, such as native account abstraction, offering added convenience for both users and developers.

Visualize Data Via Chainbase Data-cloud API

At the top left, you can switch to zkSync database tables for a deeper dive. For this piece, our primary data source is the 'transactions' table, and we'll approach the analysis from two angles,

address activity analysis and gas analysis.

If it piques your interest, give it a shot!

WITH address as(
    SELECT
        from_address AS exchange_address,
        date_trunc("day", block_timestamp) as date,
        COUNT(*) AS fa
    FROM
        zksync.transactions
    GROUP BY 
        date,exchange_address
    UNION ALL
    SELECT
        to_address AS exchange_address,
        date_trunc("day", block_timestamp) as date,
        COUNT(*) AS fa
    FROM
        zksync.transactions
    GROUP BY 
        date,exchange_address)
SELECT
    distinct exchange_address,
    SUM(fa) AS total_transactions,
    date 
FROM
    address
GROUP BY
    date, exchange_address
ORDER BY
    total_transactions desc

1. This SQL snippet extracts data from the zksync.transactions table, focusing on both the sender and receiver of transactions.
2. It truncates the transaction timestamp by date, only considering the day the transaction occurred.
3. By combining both sent and received transactions, we get the total number of transactions for each address on each date.
4. Finally, the results are sorted in descending order by total transactions, showcasing the most active addresses.

Data Analysis with Livequery

Next, I will conduct a more in-depth analysis of zkSync data from two different perspectives with livequery. I welcome all of you to join me in this endeavor!

Address Activity Analysis

• Examine the number of transfers made by addresses to check how many transactions it has had over a certain period. Addresses that transact frequently are typically considered more active.
• Look at the change in the number of transfers for a specific address over the past week.

Note: Adjusting the date and address can help you achieve your desired outcome.

Example SQL:

with data as ( 
 SELECT
    value:exchange_address as exchange_address,
    value:total_transactions :: int as total_transactions,
    value:date as date
  FROM
    (
        SELECT
        chainbase_utils.post(
          '/v1/dw/query',
          { 'query': '
    WITH address as(
    SELECT
        from_address AS exchange_address,
        date_trunc("day", block_timestamp) as date,
        COUNT(*) AS fa
    FROM
        zksync.transactions
    GROUP BY 
        date,exchange_address
    UNION ALL
    SELECT
        to_address AS exchange_address,
        date_trunc("day", block_timestamp) as date,
        COUNT(*) AS fa
    FROM
        zksync.transactions
    GROUP BY 
        date,exchange_address)
SELECT
    distinct exchange_address,
    SUM(fa) AS total_transactions,
    date 
FROM
    address
GROUP BY
    date, exchange_address
ORDER BY
    total_transactions desc   
' }
        ) :: variant AS resp
    ) AS subquery,
    LATERAL FLATTEN(input => resp:data:data:result)
)

SELECT
    exchange_address,
    total_transactions,
    left(date,10) as date -- or DATE(date) or CAST(date AS DATE)
FROM
    data
WHERE left(date,10) = '{{YYYY-MM-DD}}' -- filter date

Gas Fee Analysis

• Ranking of addresses by cumulative gas usage. This enables us to ascertain which addresses exhibit greater activity within the network or are responsible for a more substantial consumption of resources.
• Transaction fee ranking. In this way, we can determine which transactions require higher fees due to their complexity or urgency.

Example SQL:

with data as ( 
 SELECT
    value:from_address as from_address,
    value:gas_used :: int as gas_used,
    value:gas_price :: int as gas_price,
    value:date as date
  FROM
    (
        SELECT
        chainbase_utils.post(
          '/v1/dw/query',
          { 'query': '
    SELECT
        from_address,
        date_trunc("day", block_timestamp) as date,
        gas_used,
        gas_price
    FROM
        zksync.transactions
' }
        ) :: variant AS resp
    ) AS subquery,
    LATERAL FLATTEN(input => resp:data:data:result)
)
SELECT
from_address,
sum(gas_used*gas_price/10e7) as total_gas_fee
FROM
    data
GROUP BY from_address
ORDER BY total_gas_fee desc
limit 10

Would you like to try it firsthand? Curious minds can delve deeper into the code behind the visuals right here!!

Conclusion

Over the past period, our community has consistently stayed at the forefront of technology, dedicating its endeavors to the efficient integration and presentation of on-chain data. Through in-depth research and continuous practice, we've discovered that the combination of LiveQuery and Chainbase API offers us unprecedented possibilities. They not only provide an efficient mechanism for data extraction but also present users with a clear and intuitive data visualization. Such innovations ensure that even individuals devoid of profound technical expertise can readily comprehend on-chain data.

We warmly welcome new partners to join us. Whether you are a seasoned technical researcher or a beginner who is curious about on-chain data, we have prepared a wealth of resources and a friendly community environment for you. Together, let's embark on this journey of exploration into data, technology, and the future!

About Chainbase

Chainbase is an all-in-one data infrastructure for Web3 that allows you to index, transform, and use on-chain data at scale. By leveraging enriched on-chain data and streaming computing technologies across one data infrastructure, Chainbase automates the indexing and querying of blockchain data, enabling developers to accomplish more with less effort.

Want to learn more about Chainbase?

Visit our website chainbase.com Sign up for a free account, and Check out our documentation.

Website｜Blog｜Twitter｜Discord｜Link3