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.