In order to become a Dune wizard, you need to master two skills: DuneSQL and domain knowledge. My goal here is to help you master DuneSQL with minimal effort so that you can spend more time acquiring the domain knowledge, which includes blockchain knowledge, how to read smart contracts, how does the DeFi protocol of your interest work (be prepared to use the protocol, read its docs and smart contracts, and talk to the team), from which Dune tables can you find the data you want and cross check accuracy, and etc. Ok, so how to master DuneSQL efficiently? By memorizing the query execution order and sticking to a consistent coding style.
Query Execution Order
The select
statement is the most commonly used SQL statement.
If you don’t know, SQL has many dialects. For example, the famous ones are SQLite, MySQL, PostgreSQL, and of course DuneSQL. Although there are exceptions, these dialects, by and large, execute the select
statement in this order:
from [table] join [another table] on
where
group by
having
select
distinct
order by
limit / offset
Let me describe this execution order in English :
(join tables and) tell the database you’ll perform operations on the (joined) table.
filter data.
group records by one or more categorical attributes.
filter grouped data.
select attributes, which can be the original columns, derived columns, window functions, and/or aggregation functions.
remove duplicated records in attributes that come after
distinct
.order records by one or more attributes in increasing or decreasing order.
return a limited number of records and/or skip this many records.
Notice select
is executed at step 5, although we write it at the start of the statement!
If a line of code hasn’t been executed, we cannot access its output.
This means that within the select
clause, we can derive new columns based on existing columns of the table in the from
clause (because from
gets executed before select)
, but we can’t use the new columns we want to derive to create other new columns. Let’s see an example. In the following screenshot, the code throws an error because btceth
doesn’t exist in uniswap.trades
and is only created after the execution of the select
clause. The database engine doesn’t execute the comma-separated expressions sequentially! It executes the entire select
clause as a whole.
But the following code works. We create btc_sell_zone
in the select
clause not using btceth
but using token_bought_amount
and token_sold_amount
, two columns from the uniswap.trades
table. We then can use btc_sell_zone
in the order by
clause because order by
is executed after select
.
Summary
Memorizing the query execution order is easily the least-effort-and-most-effective thing you can do in your SQL learning journey. So recite this mantra when you meditate:
from (join on) where
? group by
? having
? select
?
I added question marks to help you ponder about them. If you become enlightened, make sure you let me know and buy me a coffee.
Consistent Coding Style
Having a clear and consistent coding style makes your code easier to read and reduces mental load during code writing sessions. I borrowed and experimented with different ways of formatting my DuneSQL code and finally settled with the following:
I follow the guidelines shown in the above table almost religiously. I’m much more flexible and spontaneous when it comes to alignment within clauses, alignment within FROM
clause, and parentheses across multiple lines. In general, I don’t like lengthy code, so I tend to minimize the number of lines I write as long as it’s not too difficult to read. Hope this gives you a good starting point to think about how to best format your SQL code.
Good Read
The above sections borrow from this Database SQL Primer. Give it a read. It’s worth more than reading a couple of SQL books.
Tutorials
Now you know the execution order of the select
statement/query and some good guidelines for formatting your SQL code. Let me present you with a collection of tutorials that will help you master DuneSQL. This collection will be continuously updated. Every time I publish a new tutorial, I’ll link it here. So bookmark this article and come back often.
How to use
case when
in theselect
clause to create new columns.
Referrals
This section contains affiliate links to crypto products. If you click on the link and use the product, I may receive a small commission at no cost to you. I only link to products I use myself.
Digital Ocean is a cloud computing platform where you can rent remote servers for cheap. I have my remote data science server there. You can do the same and get $200 credit.
Mint.fun is a NFT aggregator/minting platform where you get points for minting and qualify for potential airdrop. Your mints will also get you Zora points for some NFTs.
Debank will likely do an airdrop in the near future. Better to start using it now.
Join the waitlist for Synthetix's newest frontend, Infinex. Team is considering a token and people on the waitlist may be rewarded.
People are speculating Reveel will airdrop like Arkham did because they are doing early user sign-up and waitlist just like Arkham did in its early days. Binance Labs invested in it and I heard they raised (or are in the process of raising) $2m. You can now use my referral link to sign up and hopefully be pleasantly surprised later. There’s nothing to lose.
Qualify for potential airdrop when trading on Hyperliquid.
Get 5% discount when trading on GMX.
PS: My last month’s invoice from OpenAI’s was ~$9. If you find my tutorials helpful, can I ask you to help me with the cost? You can donate here. Really, even $2 is helpful. I want to dive deeper into LLA and share with you the code I will be writing. Thank you. 🙏