How to Learn SQL and Create a Dune Dashboard in 3 Hours
From "I know nothing" to "wow I made a dashboard" in no time flat!
** I wrote the following as a sort of “proof of work” validation to land a contract job at Dune. It resulted in a 5-month gig with them that was awesome, but even if I hadn’t gotten the contract doing this was worth the challenge and skills-building.
I hope this provides an example of a path to learning a new skill fast and some inspiration to tackle a challenge you’ve been hesitant to engage! **
I know nothing about SQL.
I created a Dune account a year ago and after making one attempt to create a dashboard moved on - back then I was more interested in being a degen than a blockchain analyst.
Today, I want a job at Dune and I've got through to the last round with the founder.
Things are going well, but I have a suspicion there’s concern about my lack of experience with SQL or blockchain analysis.
How do I respond?
Challenge accepted
As someone who:
has an IT administrator father
took 3 classes on programming in high school
learned a bit of Ruby after school when I still thought I wanted to be Mark Zuckerberg
has talked to a lot of developers at networking events
and whipped up a few Google Sheets QUERY calls when I wanted to do more than a mere VLOOKUP would allow
… I felt confident I was up to the challenge.
I respect deep knowledge, there are things I’ll know about SQL after years of using it that I won’t be able to learn in a weekend (or two or three), and there’s value to that when it comes to creating and planning technical content.
But as a marketing consultant who’s worked with a handful of sub-contractors for years, I’ve had a lot of practice getting up to speed on new topics fast and creating systems/processes to figure out what content needs to do, for whom, and how to deliver the right knowledge to the right people at the right time.
Also, I got my start in marketing (after not going to college and working in restaurants) by finding potential projects, creating samples for them, then sending those custom-made samples in my proposals to win work - or at the very least build my portfolio.
So I’m always up for a little “show don’t tell” action. B)
Show don’t tell
Rather than simply sweet-talk my way through the interview (though I’d do that too), I figured I’d spend the weekend building a Dune Dashboard and writing about the process.
Worst case, it wasn’t enough to win me the job, yet I’d still:
End up with a Web 3 portfolio piece (this wasn’t the first time I’d been asked for one).
Make headway into learning how to use Dune, which could help me further my Web 3 work in a variety of ways.
Best case I’d gain both those benefits and a job; all I had to lose was…
Well, nothing really because I’d still go on my Friday night date (learning and content creation both need rest :) and the friends I was supposed to camp with Saturday canceled anyway.
And so our story begins…
If someone told you to “learn about x” outside of school where would you start?
I think most of us would Google “what is x”, find a few blog posts and videos, and start consuming.
This is a mistake.
Yes, general background knowledge is helpful, and when I move from planning to working I’ll start there as well.
But without an aim, without a clear “here’s what complete looks like,” we tend to let “learning” become “reading and watching” until our interest trails off over the course of hours, days, and weeks.
That’s great if you’re looking for a new hobby.
That’s not great if you want to pass a final job interview.
So instead, as Stephen covey taught me years ago, I’ll begin with the end in mind, as discovered by answering these questions:
What can I create that would be valuable?
What can I create that’s simple enough to figure out in a weekend?
(TL;DR here’s the Dashboard I created, read on to hear more about it)
What can I create that would be valuable?
In the case of analytics like Dune, the primary forms of value I see are first, actionability and second, interest.
Actionability
Some dashboards, like @hagaetc’s DEX metrics, have actionable data.
I have a friend who did well on an investment in Sushi Swap because he watched this dashboard over the course of weeks and saw the DEX first appear, then slowly rise on the volume chart.
If I remember correctly I think he bought $SUSHI for about $.30 and sold it for around $3.
Every degen loves a good 10x.
Interest
Some things are just for fun, like working in crypto.
Well, working in crypto is also work. And people do it to get paid.
But Goblintown is just for fun.
I remember hearing about that NFT collection launching on crypto Twitter. A couple of days later was the first Twitter Spaces filled with plenty of moaning and groaning mixed with sporadic “so what’s the roadmap for this project” type questions.
Bless their hearts.
For those brave souls looking to take Goblintown seriously (the floor price did moon a week or so after launch), good luck.
For the rest of us, @tonadotirunt’s Goblin Town Dashboard is just interesting to look at once or twice.
So what do I create that’s actionable/interesting?
The biggest Crypto news the week I’m writing this is the US Treasury Department blacklisting TornadoCash’s smart contracts.
Lots of implications, lots of data relevant to a lot of people there.
A day or two after, someone (or multiple people) started dusting doxxed celebrity wallets with ETH directly from Tornado Cash’s contracts.
This seems interesting to me and is perhaps actionable information.
Can I create a Dune Dashboard related to this?
Let’s look first to see if Tornado Cash data is accessible on Dune.
A quick search for “Tornado Cash” shows a bunch of dashboards with data, so that’s a yes.
Jumping over to the Queries tab I see @abe588 created a Query for “Top 10 ETH Tornado Cash Withdrawers Since OFAC Sanctions (by total ETH).”
What can I create that’s simple enough to figure out in a weekend?
My initial idea for my “I’m worthing you hiring me” Dune project is to generate a list of addresses that have received funds from Tornado Cash.
@abe588’s query tells me I can probably do that.
From there, depending on how much more time I have, I can do a couple of further steps:
Display in a dashboard the number of wallets that have received withdrawals from Tornado Cash
Make a list of celebrity wallets that have received funds from Tornado Cash withdrawals
Another quick search on Dune for “celebrity wallets” returned one result, an empty dashboard.
Alright, what about Google?
I found a post by @zombie.wtf with a small list of celebrity wallets.
If I worked on this longer, I could follow his process and dig deeper on my own to find more addresses to add to that list.
For now, this is enough info for me to create a “number of Celebrities who have received funds from Tornado cash” count.
Depending on my time, I can either expand my celebrities list and/or show the amounts they’ve received or display some other metric.
For those of you familiar with the project management trilemma, you’ll see how I’m accounting for scope and time by picking one “need to have” goal and layering on a few “nice to haves” if it turns out I get the “need to have” done quickly enough within my weekend sprint (miracle of miracles).
For those of you not familiar with the project management trilemma, check out that Wikipedia link :)
Now we can dive into “learning”
With a basic endpoint scoped out and lightly validated, now I can turn to reading docs/watching videos to get a sense of what the critical path of learning and action is to complete my “proof of talent” project.
The first question I have is “what do I need to do to create and display a number like “# of wallets that have received withdrawals from Tornado Cash” (henceforth known as “# of recs” because I don’t want to keep typing all that out lol).
Let's take another look at @hagaetc’s DEX Metrix:
I see plenty of numbers displayed here like 7-Day Dex Volume.
Click that text (which is a link) and:
I see a few things:
SELECT, FROM, WHERE, AND statements - which I recognize from my Google Sheets QUERY calls as SQL functions.
Tabs for “Counter” and “Table”; one of the counters is broken (a test I guess), one displays the same number I saw on the dashboard.
The “Table” has this data labeled and in simple text format
From this, I figure:
Dashboards are made of separate queries, and once I create a query for the number I want in just a couple of steps I can display it on a Dashboard. I might not even need to get a list of unique addresses first for this.
I might need to first create a table; not sure if Counters (the visual display) are reliant on first outputting the data from the query into a table, but knowing what I know about development I wouldn’t be surprised.
Emboldened by the simplicity of generating this number (I know what “AND” means!), I decide to go straight for the throat - maybe I can get my “need to have” item done in just a couple of hours.
First I click that “New Query” button in the top right corner to find:
On the left it looks like we have:
A dropdown that says “Ethereum” (number 1. of course) lets me choose other networks; Tornado is on Ethereum so I’m set here
A promo for Dune Engine V2; not sure I need that so I’m not going to try it out though I’m sure they worked hard to make V2 great :)
A “search dataset” field with a list of what looks like different protocols; sure enough type “tornado” and there’s a bunch of tornado_cash items.
On the right, it looks like there’s a space to write my SQL query, and a run button to test it/view the output - pretty straightforward.
But what are these various “items?”
The search bar said “search dataset” so I’m assuming these are datasets.
I see one labeled “tornado_cash erc20_call_withdraw” but I assume that means USDC/other ERC-20 tokens; our dusting vigilante is using ETH from what I hear so I want ETH withdrawals.
Add that to my search and find “tornado_cash eth_call_withdraw"
Click the little “>>” arrows next to it and it gets added to my Query text editor.
I also see in the dropdown under “eth_call_withdraw” “call_trace_address (_int4)”
I’m guessing this will return the address that the withdrawal was sent to, in “int4” format.
Clicked to add that to my Query SQL and clicked Run just for fun
Syntax error, didn’t expect it to work but if it did there’s maybe hours of my time saved. :)
To both feel like I accomplished something (and test to see if Dune shows the entirety of the code for Queries) I copy/paste @hagaetc’s 7-day dex volume and get satisfaction.
From this I learn a few things:
Dune shows the complete query code, so I can reference working code on their site to figure out how Dune/SQL work without having to wonder whether something is happening that I can’t see.
I might not need to make a separate table
The calls to smart contracts (like tornado_cash."eth_call_withdraw") are wrapped inside SQL functions
SQL seems to close its statements with “;”, maybe they need to be on their own line (or maybe this is just done by @hagaetc and others for clarity)
And have a few questions:
Are usd_amount and billion_volume subsets of dex.”trades”?
What does “t” at the end of that FROM statement mean?
Where does the “DEX” in category = DEX come from?
Digging further, let’s see if we can find “dex.”trades”
Sure can, and there’s “usd_amount” and “block_time”
Looks like “billion_volume” is a label we’re adding to that data and “DEX” as a category might be the same (at least it’s not a piece of data I’m seeing under “dex.trades”).
If I really wanted to know my stuff, I’d also want to learn more about what to call “dex.trades” and the list items underneath it like “usd_amount,” as well as where this data comes from (could I add this data to Dune if I wanted or would I need their team to add it for me?).
But we’re learning to accomplish something simple first, if I can get a Query that works without knowing those things no need to spend time on them for now.
Just to see if it’s really that easy, I click “New Visualization” and select “Counter.”
“12 Counter” isn’t very helpful, but it looks like we can modify the labels below… and sure enough that’s exactly what those fields are for:
I see the “Add to dashboard” button and mentally commend Dune’s designers for not wasting time capitalizing the “D” in “dashboard.”
I also consider clicking it to get a better picture of the full workflow for creating a dashboard but I think that’ll come easily enough when I have my Tornado Cash data, and I don’t want to have to do any re-work in case creating a dashboard and adding this query to it means I have to create a new dashboard/query with what I really want later.
My_Dashboard v1
Back to Tornado Cash and my new query, let’s try plugging and playing some things to see if I get a valid result.
Alright, looks like “call_trace_address” isn’t what I want, not sure what these values mean but there are 142,000+ of them so I think we’re a couple of steps away from having the stat I want.
I see a “_recipient” item…
I’m starting to think eth_call_withdraw is a smart contract name and items like “_recipient” are different functions or data fields inputted when running the contract.
I see Ethereum addresses! :star_eyes:
Now can I just make a “counter” visualization?
Nope, that just outputs a single address.
I bet there’s a SQL function that lets me count the number of rows in a data set, Google time!
“SQL count rows in dataset,” the first result - there’s a COUNT function.
We can input “*” which is usually a wildcard (at least it is in REGEX which I’ve also learned through my Google Sheets ninja work).
So COUNT(*), as the article suggests, seems to just count every row.
We could put different inputs into the () and select only rows with certain data.
Let’s slap this into our SELECT function call.
Ahh, but I don’t want to count all the rows in “eth_call_withdraw” just “_recipients” so input that, and voila:
Let’s clean it up a bit, following @hagaetc’s lead we’ll label it and display it by thousands.
Alright, now I want to play with Dashboards - saving this query and adding it to a new dashboard:
I see room for some re-labeling and quickly playing with the editor I see I can add text-only widgets and there’s a simple drag-and-drop interface.
And at this point, my “need to do” is done in just 2.5 hours (a good bit of which was typing and screenshotting).
Just keep building
Two more ideas I had overnight (hello Saturday morning!):
It could be interesting (and maybe actionable) to import the list of OFAC-sanctioned addresses; maybe just a count of them, and if I can, maybe also find the overlap between sanctioned addresses and Tornado Cash recipients.
Let’s count the number of recipients since the OFAC sanction.
Number two seems easy enough, really just an extra layer to my base count and I already saw in my earlier research someone had a count “since OFAC.”
So there’s probably a way to incorporate DATE data into SQL queries.
Also, maybe I just import that existing query into my dashboard to give that Dune Wizard some props.
Looks like it was @coldfire that made a pretty thorough post-sanction Tornado Cash Dashboard.
Scrolling through it I see a few things:
Separate Data for various ERC-20 tokens, not just ETH
He adds the list of addresses to his dashboard, which comes by just printing the Query results without making a visualization
@coldfire’s Queries have way more lines than mine and the @hagaetc example I’ve been referencing :)
I can reference ENS data, there’s at least one .eth address (davidchappelle.eth) that could be a celebrity though not all celebrity wallets will have .eth domains
There are “related dashboards” at the bottom, looks like it’s based on tags used to label the dashboard - if I want to maximize the possibility of others finding my dashboard, I should not only name it something relevant (“tornado cash,” “OFAC,”), but also add tags.
Alright, so clicking into the “Unique Recipients of ETH since Sanctions” query, I could just add this stat to my dashboard but I want to modify and simplify it a bit.
I don’t think I need to filter for ETH specifically; my endpoint would be to see how many celebrities have received withdrawals from Tornado Cash since the sanctions. From what I read the dusting vigilantes have been using ETH but maybe there are ERC-20s involved too.
I do want to see what @coldfire did to filter for transactions after the sanctions.
… Lot’s to unpack here.
First, I see “date” is a modifier in SQL, and that we can use the “call_block_time” data from “eth_call_withdraw” to know when the contract was run and sent ETH to “_recipient.”
Looks like I can just use the date (@coldfire used Monday) in YYYY-MM-DD format.
I also see that @coldfire picked specific contract_addresses, and only for “call_success = true.”
The latter is pretty obvious, only including recipients that actually received something; in my case, I don’t think I’ll include this as I’ll assume OFAC might want to come after you simply if someone attempts to make you a recipient, even if it doesn’t actually go through.
These addresses though, let me cross reference this list of OFAC “Specially Designated Nationals” to see if I can find these addresses. I’m guessing these are specific Tornado Cash contract addresses, maybe they’re just for ETH withdrawals.
Looks like all 4 of those are on the SDN list. Let’s check Etherscan to see if I can figure out if I’m right about them being the ETH Tornado Cash addresses.
Hmm, so it looks like
These are the Tornado Cash ETH contracts
There are a few amounts users can deposit/withdraw (.1, 1, 10, 100)
Tornado Cash is apparently on multiple chains so @coldfire’s dashboard looking at BNB and Matic might not be ERC-20 withdrawals but T-Cash data on other chains.
I’m not going to Tornado.cash to confirm some things because I don’t want to get in trouble with the feds, so I’m fine with leaving #3 as a guess here. :)
And since this is just a weekend project, for now I’ll skip looking for Tornado Cash data on non-Ethereum chains but add it to my nice-to-have list.
There’s a lot more I can find if I really read and think through @coldfire’s Query but I’m feeling antsy about making something else so one more bit of research for now then it’s back to making myself another Query:
At the bottom of the Query there’s a SELECT statement, from which I learn:
SELECT statements go at the bottom of Queries (probably), the lines above were all prep work to aggregate/transform data and wrap them into easy-to-use labels for the SELECT statement that ultimately generates the Query’s output.
“DISTINCT” seems to be similar to Google Sheets’ UNIQUE() function - it only selects unique data points.
AS seems to associate data with an output label.
I’m dumb, this is one of many SELECT statements in this Query which I realize when I see the “FROM eth_price_vol_cleaned” (which was a statement where @coldfire seems to have modified data to generate his “total_usd_withdrawn” data).
Alright, let’s Fork my Tornado Cash Total Recipients Query (not sure if I should have done that vs. just copy/pasting the code but I know developers Fork code so this makes me feel more legitimate).
It looks like my new date qualifier goes after the FROM call, so maybe SQL is like Spanish where “adjectives” go after “nouns;” in this case, modifiers come after base functions.
By the power of open source, I’ve copy-pasted one line and now have my second data point done.
Added that to my dashboard along with @coldfire’s tags and it seems I was right about the “Related dashboards” widget as there’s his right below mine.
Let’s get the Celebrities involved
From @coldfire’s work, I know I can add ENS data to my queries, and I can potentially use that to identify celebrities.
But I also want to see if I can find a list of celebrities already inside of Dune somehow. Maybe Dune only contains smart contract data, maybe there are other data sources too let's find out!
Turning to the old search bar, I don’t get any results for “Celebrities” but I do for “Mark Cuban.”
@jeremyarbid’s Monitoring Mark Cuban's wallet dashboard should give me an idea of how I can get celebrity wallet data into my Queries.
Yes, digging in:
There are {{}} elements, which seem to be a way to insert variables.
In this case, those variables are “parameters” and it looks like @jeremyarbid manually entered an address that he believes is Mark Cuban’s.
Forking my Total Transactions query again (let's do all celebrity recipients as well as post-OFAC recipients), I click “add parameter” and see I can add a list heck yeah!
Let’s also copy in this Mark Cuban wallet:
Good to know I was right, the modal tells me to “wrap my parameter in single quotes to use it as a value.”
It also has a default selection, which suggests I may have to work some magic to have a query pull data for every item in my list, not just one.
So let’s go back to @zombie.wtf’s list and add those celebrity wallets as well so I can test this latest assumption.
For reference, here’s my starter list, I’m just going to assume these are right:
Gary Vee’s NFT: 0xd6a984153aCB6c9E2d788f08C2465a1358BB89A7
Gary Vee’s ETH: 0x263A1C688C4426Eede9B57E741fFb2B9CAdcCA94
Mark Cuban: 0x95abda53bc5e9fbbdce34603614018d32ced219e
Mark Cuban #2: 0x293Ed38530005620e4B28600f196a97E1125dAAc
Mark Cuban (@jeremyarbid’s 1): 0xa679c6154b8d4619Af9F83f0bF9a13A680e01eCf
Mark Cuban (@jeremyarbid’s 2): 0xb9f6C43FaAe4B3bC0e00C4918C17B86Fc9f84C66
Logan Paul’s NFT: 0xff0bd4aa3496739d5667adc10e2b843dfab5712b
Jay Z: 0x3b417faee9d2ff636701100891dc2755b5321cc3
Steve Aoki: 0x6d315ba45f1a1112808853ea15aa9bddf3d5b8b4
3LAU: 0xc24f574d6853f6f6a31c19d468a8c1b3f31c0e54
Steph Curry: 0x3becf83939f34311b6bee143197872d877501b11
Faze Banks: 0x7d4823262Bd2c6e4fa78872f2587DDA2A65828Ed
Here’s what I get on attempt 1:
A big fat zero, what fun!
Removed the “COUNT” call for now just to limit my variables when testing.
Not sure if I messed up that WHERE statement or if the parameter doesn’t function the way I want it to.
In the process of testing this it also looks like Dune will delete my parameters if I remove them from my Query, but @jeremyarbid shows me “--” is how I can make comments in SQL so I’ll comment out my parameter, which seems to preserve the data, and try adding just a single celebrity address manually to see what happens.
Still nothing, and I realize I’m not getting an error… which means there could be no results because the address isn’t in the “_recipient” list.
So let’s find a celebrity address that I know has received deposits from Tornado, back to Google…
According to CoinDesk, I should have just gone to Twitter because basically all they did was embed @joseph.eth’s tweet and riff on it.
But @joseph.eth got me the Etherscan page I need.
… But there are hundreds of pages and Etherscan doesn’t let me export them to CSV to quickly find celebrity names.
Also, I would just be finding ENS names that match celebrities. I remember from @coldfire’s report seeing davechappelle.eth in the recipients' list.
Time for some investigative journalism, is davechappelle.eth actually Dave Chappelle?
Looks like that’s a no, at least according to OpenSea that ENS is for sale by bochan228.
If all those CoinDesk writers did was see that ENS was on Etherscan and called it a day, big fail.
They didn’t publish receipts (aka Ethereum addresses) so I don’t know if that’s what they relied on and I’m also not able to copy that address into my Query.
The article also mentioned Beeple, I bet I can find his address.
Nice, Etherscan says this is it, let’s search for a Tornado related transaction.
Success!
At least if we trust this is actually Beeple’s address which for the sake of this test project we will (my journalistic standards go no further atm).
Let’s plug that address into my Query:
Yeah baby, there’s a result!
Now to find one more celebrity address that’s been dusted so I can confirm if my “{{celebrity_wallets}}” list parameter will return every result or just one.
The screenshot in @joseph.eth’s tweet also showed this Etherscan wallet labeled Jimmy Fallon; again for this project, I’m just going with it - if you’re with the Treasury Department I’ll hope you do more research than this before going in for the arrest.
After checking that Jimmy Fallon's wallet showed up when I hard coded it, I added that and Beeple to my list parameter and try again:
Nothing. Going to have to get a bit trickier, how can I find out how to run through the entire list, not just one item?
First, let’s finally look at Dune’s docs :)
There is, however, a list of example uses of parameters, let's see if one of those uses a list (and pulls multiple data points from it).
…
After a few minutes of looking, I see a few ways parameters are used but nothing that seems to show me a solution to my problem.
Let’s turn to Google and search “SQL parameters.”
…
Found something about “Stored Procedures” which seems slightly related but that’s not obvious.
This is a good example of where background knowledge of SQL and Dune would help me; but I can spend time learning that if I get the job!
So for now, let’s figure out how to hack this project by just manually adding these celebrity addresses to my Query and checking each of them.
From one statement in @coldfire’s Query:
“AND contract_address in ('\xa160cdab225685da1d56aa342ad8841c3b53f291',
'\x12d66f87a04a9e220743712ce6d9bb1b5616b8fc',
'\x47ce0c6ed5b0ce3d3a51fdb1c52dc66a7c3c2936',
'\x910cbd523d972eb0a6f4cae4618ad62622b39dbf')”
I’m guessing I can use () to load a comma separated list of multiple values into SQL statements, let’s try it with Jimmy and Beeple.
Hmm, so I notice @coldfire used “in” before his () list, that doesn’t really apply here. I know from the 3 programming classes I took in high school, “type casts” mean I might need to specify the type of data I’m sending the function, or that I’m sending the wrong type of data (it doesn’t accept lists).
A Google search shows me there’s an “IS” function in SQL so let’s try that instead of “=”
Still no good, a quick double check on Web3Schools shows me “IN” is actually what I’m looking for as “The IN operator allows you to specify multiple values in a WHERE clause.”
Ahh, sweet sweet progress!
Adding one of the addresses that hasn’t been dusted just to confirm this isn’t somehow just spitting out any address I put in ().
And let’s give each address its own line as that seems to be the convention, plus a comment on whose address it is for good documentary measure.
A bit cleaner and it looks like we’re only getting results returned when the address has been dusted.
Add some of our count code back and…
Doubling Down
Now to add to our list of Celebrity Wallets, I found two sites with lists I could fairly easily scrape: DappRadar and Famous Wallets.
Now, if I knew more about programming in general, I could write a script to scrape the data from these lists BUT instead I will rely on my old friend Google Sheets.
First, we inspect the DappRadar page to find the HTML we can use to just copy the source code that includes celebrity wallets:
Looks like “<div class="wp-block-columns wrapped-flex-columns">” should do the trick.
Copy that source code section into a spreadsheet then dust off the old REGEXTRACT to pull all the links to get something like this:
Basically, all the URLs included the celebrity’s ETH address (thanks DappRadar!) and I pulled all of those plus the link anchor text, which is the celebrities name.
After a bit of cleanup:
A nice list of celebrity wallet addresses. B)
Ran a similar process for the Famous Wallet data, then added back some formatting so I could easily copy/paste this back into my Dune Query and now we have a nice list of 100 celebrity wallets, 18 of whom may now be international financial criminals.
Let’s quickly fork our “post-OFAC” query and now our dashboard looks like:
Reading between the lines, looks like 5 of the celebrities on our list received deposits *before* the Sanctions.
Interesting…
Who are they?
Well naturally it was Vitalik, but 5 times.
Which now highlights that my numbers are total transactions not unique wallets.
And I think @coldfire was onto something with that idea so let’s modify our Queries so we’re getting the number of unique wallets not counting total transactions with that DISTINCT call we saw him use.
Hmm, that didn’t work but @coldfire also had these two lines:
“SELECT
_recipient as receive, “
I forget the technical programming term but he’s basically creating a label and saying instead of typing out “_recipient” to reference that data I’m just going to use “receive”
I also see above:
“WITH
outputs AS (
“
Which might be relevant, so I’ll try just that “as” statement but I know I might need to read up on what “outputs AS” and “WITH” do.
Well, it would have been nice if it was that simple, but it’s research time.
…
Turns out I just shouldn’t have used the () nice B)
Fixed all the old Queries and now our counts:
Are a good bit lower and are more correct!
Further Refinements
Two more ideas I have for now:
What other SDN addresses have received $$ from Tornado
Can I label the celebrity and SDN addresses in the Dashboard (I have the celebs labeled in the doc comments but is there a way I can output that data in my query so I can have a table with the Address and my custom labels; I think that “output” piece I just saw in @coldfire’s Query might mean it is possible).
Going to do number 1 first because I basically already have the code for this, just need to scrape the list of SDN addresses from the Treasury Department’s txt file.
To start, it’s basically the same process of copy/pasting into Google sheets then using REGEXTRACT to pull ETH addresses and names, then clean up the data.
After pushing the limits of web-browser based spreadsheet software (the SDN list ended up with like 240k cells of data I had to trim down), I was able to find 31 unique ETH addresses on the SDN list (not counting Tornado Cash’s Addresses) and their associated entities.
So let’s plug those addresses into our counter and:
Count 0?
Wait what?
I must have missed something
The US Treasury specifically cited the Lazarus Group and claimed they laundered at least $455 million through Tornado Cash, let’s double check their SDN list.
…
I have all the Unique Lazarus Group addresses in my Query.
Let’s remove the count in case something weird is going on there:
Still nothing…
Have I just caught the Office of Foreign Assets Control lying?
Probably not, for at least 3 reasons:
I manually scraped this data and even with double checking missed an address.
I missed something else in how I made this Query.
The addresses in question were on another chain or for USDC (my queries are only for ETH).
OFAC didn’t add the relevant Lazarus Group Addresses to their SDN list.
Where to next?
I had a friend from my hometown come through and since I made so much progress above I decided to prioritize spending time with him Sunday.
I think if I made more time I think I could have figured out how to create a WHILE loop or something to match the names I have for wallets to their Ethereum addresses.
If the data was in an SQL database I could definitely do it, and since my lists are relatively small even though it’s a bit brute force I think I could do it just inside of queries.
But I don’t know exactly what Dune can accept as outputs which leads us to…
How much more do I have to learn?
A lot. A whole lot.
When I originally got into marketing work, with no college degree and no work history outside of working in restaurants, I started by leveraging my basic writing skills combined with a bit of research into “how to write a blog post” to start winning freelance jobs.
From there, I found ways to turn blog contracts into writing other types of content and copy.
Then that turned into analytics work, PPC work, at this point I’ve basically seen or touched just about every facet of digital marketing work.
And it was almost all entirely learned “Just in Time” eg I would agree to do work I was 83.6% sure I could complete, then I’d learn what I needed to by filling in the gaps.
This was one of the many reasons why I liked working at project rates instead of by the hour (I can justify ending up at a lower hourly rate than I want to be at because I spent time learning).
Just-in-time learning is great because it’s lean, because it sticks more, and because you learn what really matters.
When you get stuck on a problem and then find a way to break through it, the information that unlocked that problem for you is much more memorable than learning a bunch of facts and figures that aren’t so connected to meaning.
And you don’t spend a lot of your attention trying to remember things that ultimately don’t prove that useful in getting work done.
But having reinvented the wheel a lot by learning bits and pieces here and there, I do appreciate the value of “Just in Case” learning as well.
At this point, I’m pretty sure I can’t load my own databases in Dune to associate data like wallet names with on-chain data. But I would know for a fact that I can or can’t do that if I’d gone more thoroughly through Dunes docs than I have so far.
Likewise, if I knew SQL from even a 101 level already, I would have almost certainly been able to accomplish everything I did above in a couple of hours.
So where do I go from here?
Assuming I either 1. get the job or 2. have another reason to keep learning and building on Dune, I’d do the following:
Spend some time skimming and scanning the Dune Docs and the linked resources to get a general sense of the vocabulary and pieces and parts
I’d pick whichever SQL 101 resource felt the most actionable, ideally one that has a way to interact with a practice dataset, and learn SQL’s basic syntax and functionality.
I’d do the same for Dune, pick one of their community “getting started” guides, one that I can follow step by step to build the same dashboard they do in their post to get a solid ground-level understanding of vocabulary and functionality in Dune specifically.
Build relationships with Dune Wizards so I have people I can ask questions to clear blockers fast. I remember really clearly one time I spent 10 hours trying to figure out how to migrate a website that ultimately took 20 minutes and 4 clicks using a plugin I found; it’s nice to have people who’ve already been there to tell you how to do something fast. The key thing is to make sure you understand the why behind their solution so you can solve your own problems next time.
Start building some light processes to capture ideas for and examples of Dune queries, both ones that I have and ones that others ask about. The ideas list is to have more practice fodder, the latter is to have more reference points to see if I can solve my own problems by referencing others’ work.
And with this friends, I’m calling it a weekend well spent.