QuickNode: API Guide

Important links: https://flipsidecrypto.xyz/chispas/quick-node-api-guide-quicknode-api-guide-GrPzL_

Hi there! Welcome to the QuickNode API guide for data dashboard analysis using LiveQuery at Flipside. In this guide, we'll explore how to leverage QuickNode APIs and nodes to analyze token balances and transactions for a16z, a prominent TradFi and Crypto fund.

We'll be utilizing the following QuickNode Node & APIs:

quicknode_ethereum_tokens.get_wallet_token_balance
ethereum_mainnet.udf_get_token_balance
quicknode_ethereum_tokens.get_transactions_by_address

Our goal is to analyze a16z's token balances to gain insights into their portfolio, as well as to retrieve the latest transactions, including the addresses that have transferred to the wallet and the transactions made by the a16z wallet, ordered by date, and other factors.

It's important to note that this dashboard/guide focuses on a single wallet owned by a16z, specifically the wallet with the address 0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E, which has been labeled as a16z by Arkham Intel.

To get started, the first step is to sign up on Flipside. Once your account is created, navigate to the 'Products' tab and select 'LiveQuery'. Click on 'QuickNode' and follow the integration steps outlined here to seamlessly integrate your Flipside Account with QuickNode.

After integration, return to the 'Products' tab and select 'Studio'. Click the '+' icon at the top left and choose 'Query' to create a query.

Let's start with an example using quicknode_ethereum_tokens.get_wallet_token_balance to fetch the native ETH token balance of a wallet.

To access quicknode_ethereum_tokens.get_wallet_token_balance, navigate to the Studio menu and click the small orange lightning icon at the bottom. You'll find the function listed, resembling the image on the right.

You can access the token balance code by clicking on the chart titled a16z example token balance that initially runs the code with the address. After running the code (you can change the ETH address if needed), you'll receive a JSON response containing the nativeBalance, which represents the current ETH amount held in the wallet. You can refer to the second picture on the right for visual guidance.

SELECT
quicknode_ethereum_tokens.get_wallet_token_balance(
{ 'wallet': '0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E' } --a16z
) as response

First part of the JSON response:

{
  "bytes": 4382,
  "data": {
    "id": 1,
    "jsonrpc": "2.0",
    "result": {
      "nativeTokenBalance": "0.364080526023002282",
      "pageNumber": 1,
      "result": [
        {

If you're interested in parsing the JSON to extract specific information in just a few lines of code, simply click on the chart title labeled a16z ETH Native Balance Explained with Comments on the Flipside dashboard. There, you'll find a detailed, step-by-step explanation within the code to guide you through the parsing process. If not, find it below:

-- Define a Common Table Expression (CTE) to get the token balance from the API
WITH token_balance AS (
  SELECT
    -- Call the get_wallet_token_balance function from the quicknode_ethereum_tokens API
    -- The function takes a JSON object as input, which contains the wallet address
    quicknode_ethereum_tokens.get_wallet_token_balance(
      { 'wallet': '0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E' } -- This is the a16z wallet address
    ) as response
)

-- Select the native token balance from the response
SELECT
  -- The nativeTokenBalance is nested within the response JSON object
  -- Use the ::string operator to cast it to a string
  response:data:result:nativeTokenBalance::string as nativeTokenBalance
FROM
  -- Finally, select from the CTE that we defined earlier
  token_balance

Final code:

WITH token_balance AS (
  SELECT
    quicknode_ethereum_tokens.get_wallet_token_balance(
      { 'wallet': '0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E' } --a16z
    ) as response
)
SELECT
  response:data:result:nativeTokenBalance::string as nativeTokenBalance
FROM
  token_balance

Done! You successfully have the native ETH balance of an ETH address using QuickNode and LiveQuery. Congrats!

Now, since you already have your Flipside account integrated with QuickNode, please go to the Tab in the dashboard called UDF Token Balance to see the steps for the other feature. Do that as well with the other Tab that says Wallet Transactions.

If not, simply stay here since we’ll go over them in this guide next.

Our objective is to retrieve the balance of a specific token, COMP (Compound), held by a16z. To accomplish this, we will utilize the ethereum_mainnet.udf_get_token_balance function. You can easily locate this function by clicking on the orange lightning icon in your Studio menu and scrolling until you find udf_get_token_balance (refer to the image below for visual guidance).

To gain a comprehensive understanding of how to use this function, simply click on the title of the charts found in the Flipside dashboard under the UDF Token Balance tab. You can choose to view the chart with step-by-step comments that explain the process, or directly access the chart containing the code. Both resources are available to assist you in effectively utilizing the ethereum_mainnet.udf_get_token_balance function.

If not, find them below:

Code explained with comments:

SELECT
  -- Cast the result of the function to DECIMAL
  CAST(
    -- Call the udf_get_token_balance function from the ethereum_mainnet API
    -- The function takes two arguments: the wallet address and the token address
    ethereum_mainnet.udf_get_token_balance(
      '0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E',
      -- This is the a16z wallet address
      '0xc00e94cb662c3520282e6f5717214004a7f26888' -- This is the COMP token address (input the token address you want to look for in their portfolio)
    ) AS DECIMAL
  ) / 1.0E18 AS comp_holdings;
-- Convert the balance from Wei to Ether (1 Ether = 10^18 Wei)

Code:

SELECT
  CAST(
    ethereum_mainnet.udf_get_token_balance(
      '0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E', --a16z
      '0xc00e94cb662c3520282e6f5717214004a7f26888' --COMP token address
    ) AS DECIMAL
  ) / 1.0E18 AS comp_holdings;

Done!

Now, our objective here is to retrieve the latest transactions containing the addresses that have transferred to a16z and the transactions that a16z has initiated to other addresses, ordered by date in descending order (from recent to oldest). To accomplish this, we will utilize the quicknode_ethereum_tokens.get_transactions_by_address function. You can easily locate this function by clicking on the orange lightning icon in your Studio menu, then clicking on "API" and scrolling until you find get_transactions_by_address.

To gain a comprehensive understanding of how to use this function, simply click on the title of the charts found on the Flipside dashboard under the “Wallet Transactions” tab. You can choose to view the query with step-by-step comments that explain the process, or directly access the chart containing the code. Both resources are available to assist you in effectively utilizing the quicknode_ethereum_tokens.get_transactions_by_address function.

If not, simply find them below:

Code explained with comments:

-- Get the response from the API and parse it as JSON
WITH api_response AS (
  SELECT 
    parse_json(
      quicknode_ethereum_tokens.get_transactions_by_address(
        parse_json('{ "address": "0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E" }') -- this is the a16z address
      )
    ) as response
)

-- Extract the relevant transaction data from the response
SELECT
  -- Convert the block number to an integer
  transaction.value:blockNumber::INTEGER AS block_number,

  -- Convert the block timestamp to a timestamp
  to_timestamp_ntz(transaction.value:blockTimestamp) AS block_timestamp,

  -- Get the from address as a string
  transaction.value:fromAddress::STRING AS from_address,

  -- Get the to address as a string
  transaction.value:toAddress::STRING AS to_address,

  -- Get the transaction value as a float and convert it from Wei to Ether (1 Ether = 10^18 Wei)
  transaction.value:value::FLOAT / 1.0E18 AS transaction_value

-- Flatten the transaction items in the response
FROM api_response,
     lateral flatten(input => (response:data:result:paginatedItems)) transaction

-- Order the results by the block timestamp in descending order
ORDER BY block_timestamp DESC;

Code:

WITH api_response AS (
  SELECT
    parse_json(
      quicknode_ethereum_tokens.get_transactions_by_address(
        parse_json(
          '{ "address": "0x0f50D31B3eaefd65236dd3736B863CfFa4c63C4E" }'
        ) --a16z
      )
    ) as response
)
SELECT
  transaction.value:blockNumber::INTEGER AS block_number,
  to_timestamp_ntz(transaction.value:blockTimestamp) AS block_timestamp,
  transaction.value:fromAddress::STRING AS from_address,
  transaction.value:toAddress::STRING AS to_address,
  transaction.value:value::FLOAT / 1.0E18 AS transaction_value
FROM
  api_response,
  lateral flatten(input => (response:data:result:paginatedItems)) transaction
ORDER BY
  block_timestamp DESC;

valens codes

valens codes

QuickNode: API Guide