Diving Deep into zkSync Data: Unveiling Transaction Insights with Chainbase API
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
- This SQL snippet extracts data from the
zksync.transactions
table, focusing on both the sender and receiver of transactions. - It truncates the transaction timestamp by date, only considering the day the transaction occurred.
- By combining both sent and received transactions, we get the total number of transactions for each address on each date.
- 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.
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.
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
The Original Link:https://chainbase.com/blog/article/diving-deep-into-zk-sync-data-unveiling-transaction-insights-with-chainbase-api