So If you have read my last paragraph post, you can find the verified ETH addresses of people on Farcaster with a simple SQL query on Dune website. in this post, we are going to have a look at some other datasets currently available on dune.com and see what we can get out of them. first of all, I have to say that I'm considered a noob in SQL, and any recommendation about the queries I right in this post is appreciated.
So as a sample, we are going to take a look at a cast by one and only Jon (@commstark) on Farcaster, this dude appreciates a lot and hosts the great appreciation channel on Farcaster. the cast you want to take a look at should be included in a dataset on Dune called dune.neynar.dataset_farcaster_casts and this takes time, so the cast should be at least a few hours old at the time of writing this post. I selected this cast from Jon, casted 3 days ago, you can see that Warpcast shows 53 replies and 250 likes for this cast.
Lets see if we can find it in the dataset. let's take a look at the link to this cast:
https://warpcast.com/commstark/0x3b512952
You may notice that the last part of the cast looks like part of a transaction hash. if you look at the cast dataset you can see a column called hash, so I guessed that the hash part in the URL corresponds to that.
let's try this with a simple query, the hash in the dataset is varbinary, so we convert it to varchar to compare it to the part hash in the URL and see if we get a hit. This is the query:
select * from dune.neynar.dataset_farcaster_casts where substring(cast(hash as varchar), 1, 10) = '0x3b512952'and we get a hit, so the cast is indeed in the dataset.
Now if you take another look at the dataset, you can see that each cast has a field called parent_hash, and for this cast, you can see that It's empty. So what do you think would happen if we write a query for casts that have the same parent_hash as this one? Let's try this, we can simply replace hash with parent_hash
select * from dune.neynar.dataset_farcaster_casts where substring(cast( parent_hash as varchar), 1, 10) = '0x3b512952' we get 54 rows in the result set which is close to the 53 that Warpcast was showing us:
There is another field called root_parent_hash, let's see what that one does:
select * from dune.neynar.dataset_farcaster_casts where substring(cast( root_parent_hash as varchar), 1, 10) = '0x3b512952' This time we got 87 results, so maybe we are getting something more here, let's see what's in there:
The difference between these two result sets is that in the first one, all of the 54 results have the same parent_hash and root_parent_hash, but in the second dataset, we have casts that have different parent_hash and root_parent_hash, those are (if I'm not mistaken), sub replies or replies to the replies, we don't need them.
So the next step we can do is check out who is commenting on the cast, and get their verified ETH addresses If we want to plan an airdrop. This time we use another neynar dataset called dune.neynar.dataset_farcaster_profile_with_addresses and we can join that with our result set or use a simple nested select:
select fname , display_name , fid , verified_addresses from dune.neynar.dataset_farcaster_profile_with_addresses where fid in
(select fid from dune.neynar.dataset_farcaster_casts where substring(cast( parent_hash as varchar), 1, 10) = '0x3b512952' )which returns these results:
Some folks have multiple verified addresses, so to get rid of the ones we don't need, we just get the first one:
select fname , display_name , fid , SUBSTRING(verified_addresses, 3, 42) as verified_addresses from dune.neynar.dataset_farcaster_profile_with_addresses where fid in
(select fid from dune.neynar.dataset_farcaster_casts where substring(cast( parent_hash as varchar), 1, 10) = '0x3b512952' )
and we have what we wanted:
You can find the query here and try it on other casts.