Chainbase Data Platform is an all-in-one data infrastructure for Web3 that allows you to index, transform, and utilize large-scale on-chain data. It is a platform that provides a suite of tools and services to help you build, manage, and scale your Web3 applications.

By leveraging enriched on-chain data and streaming computing technologies across one data infrastructure, Chainbase Data Platform automates the indexing and querying of blockchain data, enabling developers to accomplish complex data tasks with ease. Developers can access the fresh on-chain data with low latency.

BigQuery is a fully managed, AI-ready data platform that helps you manage and analyze your data with built-in features like machine learning, search, geospatial analysis, and business intelligence. BigQuery streaming supports continuous data ingestion and analysis while BigQuery’s scalable, distributed analysis engine lets you query terabytes in seconds and petabytes in minutes.

By leveraging the streaming support of BigQuery, we can onboard data from Chainbase Data Platform to BigQuery without losing data freshness. And developers can also integrate data from Chainbase Data Platform with their workflows on BigQuery.

Next, we will introduce the integration of the Chainbase Data Platform and BigQuery through two specific scenarios:

• Retrieve the holding ratio of ERC-20 tokens.

• Analyze the top traders in Uniswap V3 and track their trading activity.

The holding ratio of ERC-20 tokens represents the distribution of a particular token across different wallet addresses on the blockchain. This metric is crucial for understanding the token’s decentralization, investor concentration, and overall market health.

The holding ratio of ERC-20 tokens provides valuable insights into the token’s ecosystem, helping stakeholders make informed decisions and maintain the integrity of the market.

To calculate the holding ratio of ERC-20 tokens, it is essential first to clean and prepare a comprehensive table of ERC-20 token balances. This data cleaning process is often challenging and complex. Chainbase Data Platform has successfully completed this data cleaning task and utilized BigQuery’s streaming synchronization capabilities to transfer the cleaned data to BigQuery in real-time. This real-time data synchronization ensures data freshness, which is crucial for accurate data analysis and informed business decision-making. By leveraging Chainbase’s data, developers on BigQuery can seamlessly integrate this up-to-date information into their existing workflows and BI systems, significantly enhancing their data analysis and business intelligence capabilities.

Using ERC-20 balances on Ethereum as an example, the schema of chainbase_erc20_balances is:

Developers can write SQL like this to get the holding ratio of ERC-20 tokens:

WITH amount_conversion AS (
    SELECT
        wallet_address,
        CAST(amount AS DECIMAL) AS amount
    FROM
        `datasets-431007.public.chainbase_erc20_balances`
    WHERE
        contract_address = '0x8802269d1283cdb2a5a329649e5cb4cdcee91ab6'
),
total_amount AS (
    SELECT
        SUM(amount) AS total_amount
    FROM
        amount_conversion
),
sorted_amounts AS (
    SELECT
        wallet_address,
        amount,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
        COUNT(*) OVER () AS total_count
    FROM
        amount_conversion
),
percentile_calculation AS (
    SELECT
        wallet_address,
        amount,
        row_num,
        total_count,
        CASE
            WHEN row_num <= total_count * 0.1 THEN 'Top 10%'
            WHEN row_num <= total_count * 0.3 THEN 'Top 30%'
            WHEN row_num <= total_count * 0.5 THEN 'Top 50%'
            ELSE 'Other'
        END AS percentile_group
    FROM
        sorted_amounts
),
percentile_sums AS (
    SELECT
        percentile_group,
        SUM(amount) AS group_amount
    FROM
        percentile_calculation
    WHERE
        percentile_group IN ('Top 10%', 'Top 30%', 'Top 50%')
    GROUP BY
        percentile_group
)
SELECT
    percentile_group,
    group_amount,
    (group_amount / total_amount.total_amount) * 100 AS percentage_of_total
FROM
    percentile_sums, total_amount
order by percentile_group
;

The results of the SQL show that the top 10% of wallets holding the token FIGHT (0x8802269d1283cdb2a5a329649e5cb4cdcee91ab6) hold the vast majority(approximately 95.31%) of the tokens.

Tracking the top trader on Uniswap V3 and analyzing their trading activity is an invaluable tool for investors and analysts looking to follow “smart money”. “Smart money” refers to capital that is controlled by seasoned investors, knowledgeable traders, and experts who have a deep understanding of the market. By monitoring the trading patterns and strategies of these top traders offers a unique opportunity to leverage the insights and strategies of the most successful market participants. This can lead to more strategic decision-making, better risk management, and ultimately, improved investment outcomes.

Also, using FIGHT (0x8802269d1283cdb2a5a329649e5cb4cdcee91ab6) on Ethereum as an example, you can analyze the top trader from table chainbase_ethereum_uniswap_v3_trader .

The SQL to get the top trader is:

select
    *
from
    `datasets-431007.public.chainbase_ethereum_uniswap_v3_trader`
where
    contract_address = '0x8802269d1283cdb2a5a329649e5cb4cdcee91ab6'
order by
    total_amount_usd desc
limit
    10;

The results below show the top 10 traders of the token FIGHT:

After identifying the top traders, developers can get their trading activities from the table chainbase_ethereum_uniswap_v3. 

Using trader 0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad as an example:

SELECT
    transaction,
    timestamp,
    pool,
    token_0_symbol,
    token_1_symbol,
    sender,
    origin
FROM `datasets-431007.public.chainbase_ethereum_uniswap_v3` 
WHERE sender='0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad' 
AND token_1_symbol = 'FIGHT'
ORDER BY timestamp DESC
LIMIT 100
;

Get the following results:

Apart from analyzing the top trader’s transactions through BigQuery, developers can also use the Pub/Sub to track the top trader’s transactions in real time.

The Pub/Sub topic of Uniswap V3 trading activities is chainbase_ethereum_uniswap_v3_activities. Developers can create a subscription on this topic.

Then, developers can get the real-time activities from the subscription and also filter for specific traders.

The schema of the message is the same as table chainbase_ethereum_uniswap_v3. And message is like this:

{
    "token_0_decimals": 18,
    "sqrt_price_x96": "8005339006224852690586023520712",
    "origin": "0xdd9c018cdd41919a429886ce90ddf4f32f8269db",
    "token_1_name": null,
    "amount_usd": "5583.586366589651025751983060374842",
    "token_1_symbol": null,
    "token_0_total_supply": 2753097348751067167321776,
    "vid": 59838882,
    "token_1_total_supply": null,
    "block$": 20473730,
    "id": "0x05f5f3280bcc600c0474fb7dbbf83d400ab9095c4c65030dc71324474271762e#47756",
    "timestamp": [
        2024,
        8,
        7,
        2,
        32,
        35
    ],
    "token_0_name": "Wrapped Ether",
    "pool": "0xbe24f9952bf2a15bc4d2661151049d9588d6c0cb",
    "token_1_decimals": null,
    "tick": "92315",
    "amount_1": "-22457.544567184000305417",
    "log_index": "137",
    "token_0_total_value_locked": 3623,
    "amount_0": "2.215792400570866358",
    "token_1_total_value_locked": null,
    "token_0_symbol": "WETH",
    "sender": "0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad",
    "recipient": "0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad",
    "token_0": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
    "token_1": "0xf94e7d0710709388bce3161c32b4eea56d3f91cc",
    "pk": 59,
    "transaction": "0x05f5f3280bcc600c0474fb7dbbf83d400ab9095c4c65030dc71324474271762e"
}