1. Introduction

ZettaBlock’s Use Cases are almost endless, ranging from API Builders, Analytics Dashboards to the possibility of building real-time reliable apps, and many more. The purpose of this article is to investigate one of the use cases, namely how ZettaBlock’s data can help us understand Top Traders found on Uniswap via self-serve data modeling.

Based on the Uniswap tokens overview, we can see the current most popular liquidity pools, as well as tokens. How does this information relate to the top traders? In order to answer this question, we first have to focus on the following aspects:

• What tokens are traded by the ‘top traders’?
• What pools are ‘top traders’ providing liquidity for the most?
• What insights can we derive from analysing the behaviour of ‘top traders’?

The answer to these three questions cannot be found on Uniswap itself, yet we are able to tackle these ideas using data modelling that ZettaBlock enabled.

Firstly, one has to define what a ‘top trader’ is, before beginning the data analysis. A ‘top trader’ can be defined in terms of the trading volume on Uniswap V3. One must also keep the goal of the analysis in mind, which can be divided into two branches:

• The business goal: To discover top traders on Uniswap.
• The tech goal: To learn about data modelling, which can be very useful when dealing with complex analytics ideas.

Now that we’re aware of what our focus is and what the goals are, we can move to getting insights from the data!

2. Research

The first step is to research how to obtain trading data from Uniswap. Going back to Uniswap’s Overview, we can clearly see how each swap is indexed, as well as the trading value in USD. By investigating the accounts that executed each trade, we can find more in-depth information, such as:

(1) Raw transaction swap event:

By using Uniswap’s definitions of swap events, we can understand exactly what the above information is telling us, i.e., who executed the trade and who is the recipient.

(2) We can also look into the Subgraph event, which tells us the same information, but more in-depth. We are able to see the token symbol that the user interacted with, the USD value of the trade or the gas amount that the user had to pay in order for the swap to be successful. Subgraph events are a great starting point for users to implement further from SQL.

3. Creating Tables / Data modelling

(1) Re-creating the Swap table and the Pool table

By looking at the Subgraph events, we can learn how Uniswap creates swap events, which in turn allows us to re-create them in SQL and use them for our further analysis.

The analysis on Uniswap’s top traders will be conducted using the Swap table, as well as the Pool table.

In practice, we can prototype a table using a temporary view, and then submit PR into the repo. Here both are already implemented using dbt.

For further instructions on how to use dbt for data modelling, please refer to dbt’s documentation.

(2) Creating Trade table

Whilst the original table is a lot more informative, we can enrich and simplify it further, for example by adding the side of the trade (buy, sell), the USD amount, the base, the quote etc. We can enrich our previously built tables and build the Ethereum Trades table

{ config(
  alias=dex_uniswap_v3_ethereum_trades
)
}

SELECT /*+ COALESCE(12) */ * FROM
(
SELECT
  act,
  s.symbol,
  token,
  amount,
  (amount * p.price) AS amount_usd,
  account,
  block_time,
  s.data_creation_date
FROM
  (
    SELECT
      'B' AS act,
      symbol_out AS symbol,
      token_out AS token,
      - amount_out AS amount,
      from_address AS ACCOUNT,
      block_time,
      data_creation_date
    FROM
      dex.ethereum_dex_swaps_v3
    WHERE data_creation_date = date('{{ var("data_creation_date") }}')
    UNION ALL
    SELECT
      'S' AS act,
      symbol_in AS symbol,
      token_in AS token,
      amount_in AS amount,
      from_address AS account,
      block_time,
      data_creation_date
    FROM
      dex.ethereum_dex_swaps_v3
    WHERE data_creation_date = date('{{ var("data_creation_date") }}')
  ) s
  INNER JOIN prices.usd p ON LOWER(token) = p.ethereum_token_address
  AND p.data_creation_date = s.data_creation_date
  AND p.minute = date_trunc('minute', block_time)
WHERE p.data_creation_date = date('{{ var("data_creation_date") }}')
)

Now that all of our tables are built in the same style as the one found on Uniswap, we can finally answer the questions asked in section 1.

4. Analyze

The analysis begins by querying the top traders by volume in the recent 3 months.

WITH t AS (
SELECT
  *
FROM
  dex.uniswap_v3_ethereum_trades
where
  symbol not IN ('USDT', 'USDC', 'DAI', 'FRAX') -- filter stable coin
),
account_stat as (
-- 3 months
SELECT
  ACCOUNT,
  token,
  SUM(amount_usd) AS amount_usd,
  SUM(amount) AS amount,
  count_if(act = 'B') AS buy_count,
  count_if(act = 'S') AS sell_count,
  MAX(symbol) AS symbol,
  '3m' AS duration
FROM
  t
WHERE
  block_time > NOW() - INTERVAL '3' MONTH
GROUP BY
  1,
  2
)
 
SELECT account,
  sum(amount_usd) AS amount_usd,
  SUM(buy_count) AS buy_count,
  SUM(sell_count) AS sell_count
FROM account_stat
GROUP BY 1
ORDER BY 2 DESC
limit 50

This query is also published here.

The output given by the query is as follows:

And here it is! We have found out who the top traders are on Uniswap based on their trading volume in the past 3 months. Note that we add LIMIT 50 in the end of the query to fetch the top 50 traders. You can remove it if you want to fetch a full list of top traders on Uniswap.

We can dig further dig into these addresses to understand their trading behaviour in more depth. One interesting address from the list is 0x489a8756c18c0b8b24ec2a2b9ff3d4d447f79bec. Investigating this user in Etherscan, we can see that it is the BNB Bridge exploiter!

We can also see that the address only has a few trades, which translate to his money laundering behaviour. Examining data as such helps us to cross-check the results of our data modelling.

Furthermore, using tools such as Nansen or Arkham, we can get an idea of what each wallet is doing quite quickly.

5. Conclusion

This article tackled how to use ZettaBlock to find out who the Top Traders are on Uniswap. We have covered

• how to navigate through Uniswap and its Subgraph to build our own tables and conduct in-depth analysis using them
• how data modelling / dbt is used to help produce the data used in the analysis.

6. Next Steps

There’re a lot of interesting ideas to explore, countless tables to build and insights to derive. As an open platform, ZettaBlock supports many popular connectors to BI and developer tools, such as

1. BI tools: Tableau, Preset, Superset, Mode, PowerBI, and Redash.
2. Developer tools: Jupyter notebook, Zeppelin notebook, Python, Typescripts, and Go SDK

A few examples of what we can do after finding out the top traders on Uniswap can be found below:

1. Iterating on top traders definition

For example:

• Customize the ranking further, for self-defined smart money.
• Customize specific tokens, in order to see recent whales who are buying / selling the token you are watching closely.

This can be done using either ZettaBlock UI or your favoriate tools like Jupyter Notebook.

2. Dashboard for internal use

If you are a team of group alpha hunter, then you can build internal dashboards to share your results using ZettaBlock dashboard or your favourite visualization tools, such as Tableau.

3. Product features

You can also build things like leaderboards for Uniswap to show the top traders. GMX has already built something similar - this could be your inspiration to build something alike for a different DApp!

4. Cross-use with other services using API

For example, you could build a tool which notifies you when a new top trader appears, or when the ranking of the current top traders changes. This is also possible, because you can create a real-time GraphQL API using the GraphQL API Builder.