As a Developer Relations at Chainbase, I often find myself at the intersection of technology and community. One of the most common requests we receive from our community is the ability to merge Dex transaction data and price data together, similar to what other companies offer. Today, I'm excited to share how you can craft visualizations using LiveQuery and Chainbase API to meet this need.

Prerequisites

To get started, you'll need to create a free account and generate an API key. This key will be used to authenticate your requests to the Chainbase API.

Go to the LiveQuery website and add your api-key to the add on.

Crafting Visualizations with LiveQuery

Whether you're tracking token transfers, monitoring top token holders, or analyzing token price history, LiveQuery can help you bring your data to life. For instance, you can use LiveQuery to combine data from the Chainbase Token API to create a comprehensive visualization of token activity.

Visualize Data via Web3 API

First, let's look at how you can use LiveQuery with the Chainbase Token API. Here's a simple example:

SELECT 
  value:amount::float AS amount,
  value:original_amount::string AS original_amount,
  value:usd_value::float AS usd_value,
  value:wallet_address::string AS wallet_address
FROM 
  (
    SELECT 
      chainbase_utils.get('/v1/token/top-holders', 
        PARSE_JSON('{"chain_id": 1, "contract_address": "0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0"}')
      )::variant AS resp
  ) AS subquery,
  LATERAL FLATTEN(input => resp:data:data)

This SQL statement is making a request to the /v1/token/top-holders endpoint, retrieving the JSON response, flattening the nested JSON array, and finally extracting the desired fields from each item in the array. The information it's extracting relates to the top holders of a particular token, specified by the contract_address in the Ethereum blockchain (as indicated by the chain_id of 1). You can see the result below.

Visualize Data Via Chainbase Dataset API

You can also obtain much more specific data from our dataset to visualize ETH UniswapV3 pool data. Here's a simple example:

SELECT 
  value:name::string AS name,
  value:symbol::string AS symbol,
  value:totalValueLockedUSD::int AS total_Value_Locked_USD,
  value:createdBlockNumber::int AS createdBlockNumber,
  value:id::string AS contract_address
FROM 
  (
    SELECT 
      chainbase_utils.post(
        '/v1/subgraphs/ethereum_uniswap_v3/1.0.0', 
        {
          'operationName':'MyQuery',
          'query':'
            query MyQuery {
              liquidityPools(first: 10, orderBy: totalValueLockedUSD, orderDirection: desc) {
                id
                createdBlockNumber
                symbol
                totalValueLockedUSD
                name
              }
            }'
        }
      ) :: variant AS resp
  ) AS subquery,
LATERAL FLATTEN(input => resp:data:data:liquidityPools)

This SQL query uses the chainbase_utils.post function to send a request to the 'ethereum_uniswap_v3' subgraph, asking for the top 10 liquidity pools based on their total locked value in USD. The data returned from the subgraph is in the JSON format, which is then flattened to extract the properties of the pools. These properties - name, symbol, total locked value in USD, the block number at the time the pool was created, and contract address - are then selected from the response and used to form the output table.

Visualize Data Via Chainbase Data-cloud Api

Although Flipside does not have on-chain data about SUI, it is available in Chainbase's Datacloud. Through the method below, we can visualize the on-chain data of SUI:

First, go to the Chainbase Datacloud and select the data you want to show in the live query. In this example, I use the sui.validators table to see the rank and SUI balance in their SUI staking pool.

WITH latest AS (
        SELECT max(epoch) AS latest 
        FROM sui.validators
          )
        SELECT 
          name,
          staking_pool_sui_balance / 1000000000 AS stake_balance,
          image_url 
        FROM 
          sui.validators,
          latest 
        WHERE 
          sui.validators.epoch = latest.latest 
        ORDER BY 
          stake_balance DESC

Copy your SQL inside the live query's chainbase_utils.post('/v1/dw/query'), so you can fetch the data in the console.

SELECT 
  value:name::string AS name,
  value:stake_balance::int AS stake_balance
FROM 
(
  SELECT 
    chainbase_utils.post(
      '/v1/dw/query', 
      {
        'query': 
        'WITH latest AS (
        SELECT max(epoch) AS latest 
        FROM sui.validators
          )
        SELECT 
          name,
          staking_pool_sui_balance / 1000000000 AS stake_balance,
          image_url 
        FROM 
          sui.validators,
          latest 
        WHERE 
          sui.validators.epoch = latest.latest 
        ORDER BY 
          stake_balance DESC'
      }
    ) :: variant AS resp
) AS subquery,
LATERAL FLATTEN(input => resp:data:data:result)

If you want to put the entire query on one line, here is the sample code:

SELECT
value:name::string AS name,
value:stake_balance::int AS stake_balance
FROM
(
SELECT
chainbase_utils.post(
'/v1/dw/query',
{
'query': 'WITH latest AS (SELECT max(epoch) AS latest FROM sui.validators) SELECT name, staking_pool_sui_balance / 1000000000 as stake_balance, image_url FROM sui.validators, latest WHERE sui.validators.epoch = latest.latest ORDER BY stake_balance DESC'
}
) :: variant AS resp
) AS subquery,
LATERAL FLATTEN(input => resp:data:data:result)

Please note that putting the entire query on one line may reduce readability, especially for more complex queries. In this particular case, since the query is relatively simple, merging it into one line shouldn't be a big issue.

This SQL command extracts and visualizes on-chain data related to SUI validators, focusing on their names and stake balances. First, it retrieves the most recent epoch from the sui.validators data. Then, it collects the names and staking balances (adjusted to be in correct units) of validators whose records correspond to this latest epoch. The results are ordered by stake balance in descending order. This operation utilizes the 'chainbase_utils.post' function to fetch the data from a specific web API endpoint. Finally, the resulting data structure is flattened for easier processing and analysis.

Note: For developers who have worked with flipside studio, you might be accustomed to using single quotes to denote strings. However, in Chainbase, we use double quotes for the same purpose.

I've created numerous templates and visualizations using the Chainbase APIs and LiveQuery. These resources are designed to help you get started and inspire your own data analysis projects.

Conclusion

I'm thrilled to see the innovative ways our community is using LiveQuery and Chainbase APIs to visualize and analyze on-chain data. These tools not only simplify the process but also open up new possibilities for understanding and interpreting blockchain data. We're excited to see what you'll create next and are always here to support your journey in the blockchain space.

Happy data crafting!

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