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 evident that the data of L0 is derived 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 particularly 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. No need to worry, free users of Dune have a quota for creating private queries, so privacy exposure is not a concern. The data synchronization of 10kDrop is once every 24 hours, while using Dune is faster. Let's get started.

Create a Dune account#

Open Dune and click on "Sign up" at the top, as shown in the following image. Use your email to register an account as usual, and I won't go into details.

Write the query statement#

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

Find the corresponding 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, find the red box in the address bar above, and make a note of "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 us to a new query that we created ourselves, and we can 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 "Run" and wait for a while. We will get a table with all the data. Then, we can filter out the addresses we don't want to see and keep our own addresses.

Address conversion#

First, we need to convert the queried addresses into 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 "Run" to see if we get the desired results.

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

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

Conclusion#

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