Implementation

Data is stored within the MySQL database. The appropriate dialect must be used when building SQL queries.

Example

Let's build the same chart that was given as an example in , but with SQL now.

1. Click on Create and select SQL query

2. Enter the following SQL query

SELECT
    `ethereum_transactions`.`block_date` AS `block_date`,
    count(*) AS `count`
FROM
    `ethereum_transactions`
GROUP BY
    `ethereum_transactions`.`block_date`
ORDER BY
    `ethereum_transactions`.`block_date` ASC

3. Choose a Line chart as a visualisation option

Best practices

Good sql habits can improve the efficiency of running SQL, hope these cases can help you optimise your sql code and find the data you want faster.

WHERE clause

🔴 Not recommended

date(block_timestamp) > '2022-03-01'

🟢 Recommended

block_timestamp > CAST ('2022-03-01' AS datetime)

Number of results

Try to LIMIT the time and order of magnitude of large tables at the beginning, filter only the required fields, and try to avoid full table scans.

🔴 Not recommended

SELECT *
FROM TABLE

🟢 Recommended

SELECT name,
       on_date
FROM TABLE
LIMIT 10

JOIN clause

When join, put the small table on the left side, and each time you JOIN or SELECT, think about whether you can significantly reduce the amount of data.

IN and NOT IN clauses

IN and NOT IN should also be used with caution, otherwise it will lead to a full table scan, for continuous values, BETWEEN can be used, do not use IN.

Apply for filters

Field filters

1. First, type {{variable_name}} in the SQL statement:

SELECT * from `defi_protocol_daily_stats` [[where {{chain}}]]".

2. Set the parameters in the right sidebar.
3. Select the variable type as field filter and select the associated field.

4. Finally, you can associate the filter field of the chart in the dashboard.

Text filters

1. First, type {{variable_name}} in the SQL statement:

select * from defi_protocol_daily_stats [[where chain={{chain}}]]".

2. Second, set the parameters in the right sidebar.
3. Third, you must select the variable type as Text .
4. Finally, you can type Ethereum in the filter input box and run it, and you can see that the result set has been filtered with Ethereum.

Tutorial: Parsing Ronin Chain Logs Using SQL

In this tutorial, we will walk through how to parse records from the ethereum_logs table to analyze transactions related to the Ronin chain. This example demonstrates how to extract specific data from log entries using SQL.

Prerequisites

• Basic understanding of SQL.
• Familiarity with blockchain concepts, especially Ethereum logs.
• Access to a database containing the ethereum_logs table.

Step-by-Step Guide

1. Extract Date

We start by extracting the date from the block_timestamp column. The date_trunc function is used to truncate the timestamp to the day level.

select date_trunc('day', block_timestamp) as block_date, -- intercept date

2. Extract User Address

Next, we extract the user address from the log data. The address is located in the fourth part of the data field. Each part is 64 characters long, and we start extracting from the third character.

substring(data, 3 + 64 * 3, 64) as hex_address, -- extract the 4th part of data into a user address, starting with the 3rd character, every 64 bits as a group

To format the address properly, we concatenate '0x' with the last 40 characters of the extracted data.

concat('0x', substring(substring(data, 3 + 64 * 3, 64), -40)) as address, -- extract the 4th part of the data into a user address, starting from the 3rd character, every 64 bits as a group, intercept 40 bits from the right, use '0x' to concatenate the intercepted data

3. Extract Token Address

Similarly, we extract the token address from the fifth part of the data field.

concat('0x', substring(substring(data, 3 + 64 * 4, 64), -40)) as token, -- extract the 5th part of data into user address

4. Extract Transaction Amount

The amount is located in the twelfth part of the data field. We extract it as a hexadecimal string first:

substring(data, 3 + 64 * 11, 64) as hex_amount, -- extracts the 12th part of the data

Then, we convert this hexadecimal string to a decimal value:

from_base(substring(data, 3 + 64 * 11, 64), 16) as amount, -- extracts the 12th part of the data and converts it to a decimal value

5. Include Transaction Hash

We also include the transaction_hash in our results for reference:

transaction_hash

6. Filtering the Logs

To filter the logs, we use the following conditions:

• The address matches the Ronin Bridge V2 contract address.
• The first topic matches the Send event signature.
• The fourth part of the data field matches the WETH token address.
• The block_timestamp is within the last 7 days.

from ethereum_logs
where address = '0x64192819ac13ef72bf6b5ae239ac672b43a9af08' -- Axie Infinity: Ronin Bridge V2 
    and element_at(topics, 1) = lower('0xd7b25068d9dc8d00765254cfb7f5070f98d263c8d68931d937c7362fa738048b') -- Send
    and substring(data, 3 + 64 * 4, 64) = '00000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH, direct determination of hexadecimal value
    and block_timestamp >= now() - interval '7' day

Full SQL Query

Here is the complete SQL query combining all the steps above:

select date_trunc('day', block_timestamp) as block_date, -- intercept date
    substring(data, 3 + 64 * 3, 64) as hex_address, -- extract the 4th part of data into a user address, starting with the 3rd character, every 64 bits as a group
    concat('0x', substring(substring(data, 3 + 64 * 3, 64), -40)) as address, -- extract the 4th part of the data into a user address, starting from the 3rd character, every 64 bits as a group, intercept 40 bits from the right, use '0x' to concatenate the intercepted data
    concat('0x', substring(substring(data, 3 + 64 * 4, 64), -40)) as token, -- extract the 5th part of data into user address
    substring(data, 3 + 64 * 11, 64) as hex_amount, -- extracts the 12th part of the data
    from_base(substring(data, 3 + 64 * 11, 64), 16) as amount, -- extracts the 12th part of the data and converts it to a decimal value
    transaction_hash
from ethereum_logs
where address = '0x64192819ac13ef72bf6b5ae239ac672b43a9af08' -- Axie Infinity: Ronin Bridge V2 
    and element_at(topics, 1) = lower('0xd7b25068d9dc8d00765254cfb7f5070f98d263c8d68931d937c7362fa738048b') -- Send
    and substring(data, 3 + 64 * 4, 64) = '00000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH, direct determination of hexadecimal value
    and block_timestamp >= now() - interval '7' day

Conclusion

This tutorial demonstrated how to parse Ethereum log data to extract specific information related to the Ronin chain. By following these steps, you can analyze transactions and gain insights into blockchain activities.