For a while Arbitrum only had USDC.e, the bridged asset. Recently, native USDC arrived on Arbitrum. But many Dune tables still associate the symbol 'USDC' with USDC.e’s address and have the actual USDC’s symbol missing. Take the uniswap.trades
table for an example. From Arbiscan, we can easily find the token addresses for USDC.e and USDC:
USDC.e: 0xff970a61a04b1ca14834a43f5de4533ebddb5cc8
USDC : 0xaf88d065e77c8cC2239327C5EDb3A432268e5831
If we filter USDC.e’s address on uniswap.trades
, we get records with “USDC” as token symbol. And we’d want to change them to “USDC.e.”
On the other hand, if we filter USDC’s address, we get records with blank token symbols. And we’d want to fill the blanks with “USDC.”
So how do we fix this issue? The answer is case when
, which is SQL’s implementation of “if else.” Here’s the syntax:
case
when condition_01 then item_01
when condition_02 then item_02
…
else item_n
end
The key to remember is that SQL evaluates each when
condition one by one, in the order they appear. Also remember that item_01, item_02, …, item_n
don’t have to be scalars. They can be and often are columns from the same table. In our case, the following code fixes ‘USDC’ and ‘USDC.e’ in token symbols:
Here’re some sample records showing what fixed data look like:
I put all SQL code for this tutorial in this query, fork it, star it, and run it. This issue came up while I was building the Uniswap pool screeners (curated and full). Make sure you check them out too and give a star if you enjoy using them.
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: I put a lot of thoughts and time into this article, so if you got something out of it, can I ask you to buy me a coffee to help me stay awake?