Create L0 exclusive data dashboard using Dune

Introduction#

Recently, various websites have emerged that offer airdrops and wallet quality, such as 10kDrop. Among them, it is obvious that the data of L0 comes from this table in Dune, which was created by @superamscom. Thanks to @superamscom for their hard work. Let's show some respect!

For free users of 10kDrop, they can query one by one, which is not very convenient for users with multiple accounts. Since @superamscom has generously opened this table, let's create our own data dashboard based on the Dune table.

For users with multiple accounts, they may be concerned about exposing their addresses. There is no need to worry about this, as free users of Dune have a quota for creating private queries, so privacy exposure is not an issue. The data synchronization of 10kDrop is once every 24 hours, while using Dune is faster. Let's get started.

Creating a Dune Account#

Open Dune and click on "Sign up" at the top, as shown in the following image. Register your account using your email address, and I won't go into further details.

Writing Query Statements#

After registering, log in to Dune with your account. You can directly open the dashboard here in your browser, or find the corresponding dashboard LayerZero Users Ranking For Potential Airdrops? (If using an Arbitrum-like algorithm) below by name.

Finding the Source Table#

After entering the dashboard, click on the location indicated by the red box in the image below to view the specific query implementation.

Then, copy the part in the red box in the address bar above, which is query/2464151. This is the name of the source data table we will use.

Next, click on "Fork" in the upper right corner, as shown in the following image:

This will take you to a new query that we created ourselves. Now, delete all the code in it.

Query Statement#

After deleting the code, enter the following SQL statement in the code input box:

select
  *
from
  query_2464151
order by rs desc, amt desc, tc desc

Click on "Run" and wait for a while. We will get a table with all the data. Then, filter out the addresses we don't want to see and keep our own addresses.

Address Conversion#

First, we need to convert the addresses in the query to a different data format. The specific query statement is shown in the code below. The addresses are extracted from the source data.

select
  *
from
  query_2464151
where
  ua IN (
    CAST(
      0xa6e96dbb6b7d2c12062e70d3dec87c4fe23f961f as varchar
    ),
    CAST(
      0x8a4a50b13fd2cb36feb96c408cb98b4c9f2b8f25 as varchar
    ),
    CAST(
      0x1e081471ebc8174b5e8ba2d985777c4d9d193ceb as varchar
    )
  )
order by rs desc, amt desc, tc desc

If you have too many addresses and find it difficult to convert them, you can use a tool I created to quickly output the corresponding code. Click here to open the tool.

Then, click on "Run" to see if we get the desired results.

If everything is fine, click on "Save" and give this query a name. The most important thing is to check the "Make private" option.

This way, we have created our own dashboard without revealing our addresses.

Conclusion#

Dune is very powerful. My SQL skills are very limited, and I can only write some simple queries. Without the selfless contribution of @superamscom, we would not be able to display our account data so conveniently. Once again, thank you!