Original title: "Analyzing Data In Web3 with SQL"
Original author: GOKE ADEKUNLE
Original compilation: old yuppie
This article comes from WeChat public account: Old Yuppie
Do you want to know how to be an efficient data analyst in Web3? Or, do you just want to build a Web3 analyzer to analyze the Web3 data sense of interest? Anyway, welcome to this article.
As a data guy, the most challenging part of Web3 data is understanding the data itself. From my experience, one thing that I find easy is to think of everyone as a giant data warehouse and the protocol on the Ethereum blockchain, as various tables and views in the warehouse. model. PS: This also applies to other blockchains.
For this post, I will try to keep things concise and highlight them using these themes:
· Web3 data flow and landscape. Learn about Web3, decentralized data storage.
Tools for decoding transactional data: an overview of tools that work with the Web3 data stack, and how to leverage them
Let's get started!
The whole point of Web3 is to become more independent and autonomous. This is achieved through blockchains and distributed peer-to-peer networks rather than server-client relationships. This makes the data flow and format different from what we are used to in Web2. Through my research, I found data mappers like Dune Analytics and Flipside Crypto with decoded blockchain data and built-in visualization tools for data scientists interested in Web3 to use and build upon.

Image source
p>
The picture above shows different ways of extracting data for analysis from different blockchain models, taking Ethereum as an example.
If you've ever transacted on Ethereum (or any smart contract-enabled blockchain), you've probably been on Block Explorer Looked it up and saw this message:

A transaction example on the Ethereum blockchain seen through the Etherscan block explorer
Learning to read transaction details will be the basis of all Ethereum data analysis and knowledge, but it is also important to understand the flow of Token and transaction data.
I may be biased towards using the Ethereum blockchain to explain decrypting blockchain data, but as a rule of thumb, when you understand how data is stored on smart contracts and Ethereum is easier to understand when it comes to governance. That being the case, it's important to note that many of the concepts I'll be talking about will apply broadly to all EVM-compatible chains and smart contracts, such as Polygon, Fantom Opera, BSC, Arbitrum One, etc.
A smart contract transaction is equivalent to a background API call in a smart contract driven web3 application. Details of activities on smart contracts and the resulting application state changes are recorded in data elements called transactions, calls, and logs. Transaction data elements represent function calls initiated by users (or more precisely EOAs), call data elements represent additional function calls initiated by smart contracts within a transaction, and log data elements represent events that occurred during transaction execution.
In order to understand the transaction data on the Ethereum blockchain, we can try to use Etherscan (as the basis of the transaction example, I used a Mirror Crowdfund contract). It’s like checking your car’s dashboard for pointers to fix a problem with your car, but as a data scientist, we have to pop the hood of the car and understand what’s going on behind the scenes. Typically, there are 3 different types of transactions:
·Transfer from one user (EOA) to another in the form of base currency, for example, Zhang San sent 3 ETH to Li Si on the Ethereum network
·A smart contract is created by an EOA. For example, Zhang San submits the code to an address on the blockchain to create a smart contract. Once a certain amount of resources are transferred to the seller's account (or wallet), the smart The contract will transfer the ownership of the NFT.
· EOA calls smart contracts, for example, Zhang San calls Li Si's smart contract, and exchanges NFT with 999ETH.
When a user makes a request on the blockchain, what happens under the hood is:
·The EOA associated with the user initiates a transaction, specifying the target smart contract address, the target function, the parameters of the function, the transaction payment (if any), and its The willingness to pay the gas fee, if any.
·The transaction is broadcast to the network and picked up by miners willing to perform the functions specified in the target smart contract.
· If the execution is successful, the smart contract emits events, marking the completion of certain milestones. The resulting event data structure is called a "log".
· The target smart contract can initiate internal transactions (extra calls) to other smart contracts. The data structures created by these internal transactions are called "traces" and may also emit additional log events during the course of their respective executions.

Token transmission data lifecycle< br>
Now, you understand what is going on behind the scenes and the corresponding data elements. Now is the time to itemize the various elements that make up the web3 landscape. Transaction and tracking data structures contain the details of smart contract function calls, in this order:
· On the blockchain, each transaction has a unique hash value `transaction hash value`, which has various transaction details. For example, we can query the transaction hash value here 0xbe3b109b857e8897cbe3c2a261d8072546ae693662422a1b93fe1a3a990dfded
`From` is the sender of the transaction, `To` is the receiver of the transaction, It can also be a contract address. In this example, it was transferred from `0x83ebd57bf4f22cba9c4a624a69c910d7c8619ee2` to the CROWDFUND_EDITIONS token.
·`Value` is the value of ETH being transferred. Sometimes the ETH value can be 0, in this case it is.
· `Transaction fee = gas used in the transaction * gas price`, you can find more details related to Ethereum gas here. (https://ethereum.org/en/developers/docs/gas/#post-london)
Input data contains additional information about the transaction . In this case, it's a transfer function, with details including the address and value of the transaction.
· The data itself is bytecode, which has data type `bytea` on Dune.
· The first 8 characters `0xa22cb465` are the `MethodId` of the function signature, which is not always unique (but should be).
·Every subsequent 64 characters is a different input variable. Here we have two, corresponding to `to address` and `value`.
Based on the above information, we can use the following query to pull data on Dune (https://dune.xyz/queries/601249)——
p>

Now we have a good understanding of the elements of Web3 data, what it looks like, and have queried our first Trading data, you may wonder how to quickly familiarize yourself with a protocol and its data. There is a good set of tools that can help us do this.
As mentioned earlier, using Block Explorers is a great way to gain insight into transactions on the blockchain. Block Explorers are online browsers that display details of all transactions that have ever occurred on a blockchain network. Using the above screenshot as an example, it is important to know that all major blockchains have explorers - prominent examples include Etherscan, Polygonscan, BSCScan, Solana beach, etc.
While blockchain explorers are helpful for interrogating individual records within a blockchain ledger, they are not useful for answering questions that require aggregating or transforming data good at. For example, if you want to know how many NFTs have been sold through the Opensea exchange in the past 3 months, or if someone wants to know how often transactions flow from "Coinbase" to "Axie Infinity", it is difficult to answer with only block explorers. To do this, people will need direct access to data. This is in my Previous article It has been introduced in the article.
Dune analytics is a great resource for accessing and analyzing blockchain data. As of this writing, it has raw and decoded data for Ethereum, Solana, Xdai, Polygon, Optimism, and BSC. It provides a PostgreSQL interface to query datasets, and a simple point-and-click interface to create simple dashboards based on query results. The user community on Dune is also quite active and has produced an extensive library of examples of queries and dashboards for people to learn from. Below are a few examples of analyzes created on Dune
·Solana NFT Transaction Overview (https://dune.xyz/sealaunch/Solana-Transactions)
< /p>
·OpenSea Market Analysis (https://dune.xyz/hildobby/OpenSea-Market-Analytics)
·Eth2 Flow Liquid Staking (https://dune.xyz/eliasimos/Eth2-Liquid-Staking)
If you are completely new to SQL, I recommend these helpful hints and guides for Dune analysis:
Inline Ethereum addresses. When working in Dune, Ethereum addresses are stored as PostgresSQL byte counts, and when you query them on Etherscan, they are encoded with an `x` prefix instead of the usual `0x`.
Identifies the contract address in the transaction. When trying to distinguish contract addresses from normal addresses, you can do this by checking whether the `CREATE` opcode is called in `traces`, e.g.
3. Make the most of the tags on Dune. A tag is a piece of metadata about an address, a label or metadata, if you will. It comes in the form of key-value pairs. Keys are tag types, and values are tag names. Essentially, you can use labels to look up addresses to see what they are (see label table below). You can get more information here More insight on what tags are and how to use them
< /p>
If you want to get the label of the address you are looking at, please use For this UDF, you can do;
4. Quotes around table and column names. PostgresSQL does not recognize camel column and table names without quotes. So, in Postgres, double quotes are reserved for tables and columns, and single quotes are reserved for values, so Dune will throw an error if you query for capital letters in table names.
5. Remove decimals. processing Token Remember to check for decimals in `erc20. "tokens"` when adding amounts.
6. Generate time series. When dealing with a dataset with a discontinuous time series, use
Hope this was a useful discussion post and you already know enough to do basic Web3 data exploration and analysis, using SQL on Dune, what it looks like, and how to work with it. When analyzing economic activity and user behavior in web3, it is important to develop an interest in understanding how a particular smart contract works, which will go on to help you identify the key functions and events involved in the metrics of interest.
Web3 is experiencing rapid development and high variability, with new ideas, products, communities and experiments emerging every day. It currently touches every aspect of life, including but not limited to payments, finance, art, music, gaming, community, governance, and identity, making it exciting for anyone to participate.
So please understand that combining actual use of Web3 products, checking data exhaustion on block explorers like Etherscan, and reading smart contract source code are key imperatives for developing a correct data analysis strategy.
Original link
Welcome to join the official BlockBeats community:
Telegram Subscription Group: https://t.me/theblockbeats
Telegram Discussion Group: https://t.me/BlockBeats_App
Official Twitter Account: https://twitter.com/BlockBeatsAsia