Working with Farcaster data again, this time, reactions and quotes

So in the two previous posts, I wrote about working with Farcaster data and extracting Farcaster verified addresses from Farcaster handles or from the commenters (or replyoors) of a cast. Some folks on Farcaster thanked me so I guess these posts are helpful for some people even though these are pretty basic SQL queries.

In this post, I'm going to tinker with The Farcaster reaction dataset and see If we can extract stuff from it. As a sample, I chose this cast from the one and only @karbonbased aka the bossman, Host of the paidgroup channel. this is the cast:

as I'm writing this post, It has 147 recasts, 10 quotes, and 427 likes. you have to keep in mind that Warpcast stats may not be accurate because Warpcast sometimes nerfs some accounts for spamming and other reasons and these accounts are still able to send reactions from other Farcaster clients.

So for the first step let's find this cast in the dune.neynar.dataset_farcaster_casts Dataset. Just like the last posts we do this with a simple query and search for the last part of the Cast URL which is a truncated hash:

select * from  dune.neynar.dataset_farcaster_casts where hash is like '0x9ce916e9%''

The last time I used substring to truncate all the hash column and search for the last part of the URL, This time I used % wildcard which is a really better way to do that. The query returns one result which is the cast we were looking for:

There is another Dataset on Dune called dune.neynar.dataset_farcaster_reactions and as you can see in the picture below It has a few interesting columns called reaction_type, fid, target_hash, target_fid, and target_url.

Fortunately, Neynar has provided the schema for this database here😀:

And from this doc, we can see that reaction_type 1 is “like” and 2 is “recast”. so we use the query above to get the full hash of the cast and query for all reactions in the reactions Dataset that have the same target_hash:

select * from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%')

We got 653 results back which is more than what Warpcast was showing:

To see how many likes ( 1 in reaction_type) and recasts (2 in reaction_type) we have here, we slightly change this query:

select reaction_type , count(reaction_type) as "count" from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%') group by reaction_type

and we get this table which shows 488 likes and 165 recasts (at the time of writing, This numbers went up as I was writing this)

So now we want to get the verified address of these people and that's in the dune.neynar.dataset_farcaster_profile_with_addresses dataset. in the reactions dataset, we have the fid of the people who did the reaction. So we simply join these two datasets (or tables in SQL terms). first, we get the Fids. This query returns the Fids:

select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%')

And then we use that to get the addresses (also we shorten the address column because it may include a Solana address and we don't like Solana on this blog):

select fname, fid , SUBSTRING(verified_addresses, 3, 42) as address from dune.neynar.dataset_farcaster_profile_with_addresses
where fid in (select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%')) order by fid

which returns this table:

This result needs a little more work because this is the list of folks who liked or recasted the cast. I'm a bit lazy and these queries can be written way better but for likes only:

select fname, fid , SUBSTRING(verified_addresses, 3, 42) as address from dune.neynar.dataset_farcaster_profile_with_addresses
where fid in (select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%') and reaction_type = 1 )

and recasts only:

select fname, fid , SUBSTRING(verified_addresses, 3, 42) as address from dune.neynar.dataset_farcaster_profile_with_addresses
where fid in (select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%') and reaction_type = 2 )

and folks who liked and recasted: (I know this query is really bad but it works)

select fname, fid , SUBSTRING(verified_addresses, 3, 42) as address from dune.neynar.dataset_farcaster_profile_with_addresses
where fid in (select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%') and reaction_type = 1 ) and fid in (select  fid  from dune.neynar.dataset_farcaster_reactions 
where cast(target_hash as varchar) in (SELECT  CAST(hash AS VARCHAR) FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x9ce916e9%') and reaction_type = 2 ) order by fid

here is the link to this query if you want to play with it. also, bear in mind that these Datasets are not complete and If you plan to do an airdrop, you may omit some people unintentionally.

Now It's time to get the quotes. let's take a look at this cast and two of Its quotes, we can do this with this query:

 SELECT * FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x511396b3%' or cast(hash AS VARCHAR) like '0x9ce916e9%' or cast(hash AS VARCHAR) like '0x5bd2d6da%'

Looking at the results, The only thing I can find in the quote cast data that points to the original cast is in the embeds column, So I think if you want to extract the wallet addresses of folks who quoted a cast, you have to start with one of the quotes.

So we start by writing a query to select the embed column from one of the quotes and continue from there. Also, note that I selected the first part because if the quote includes a picture or a URL (like a Zora mint or a frame) That will also be in the embeds column and we don't need that:

SELECT split_part(embeds ,'"type": "Buffer"}}}', 1 ) as embeds  FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x5bd2d6da%'

which gives us the part we need:

using that, we can query all the quotes we want:

Select * from dune.neynar.dataset_farcaster_casts where split_part(embeds ,'"type": "Buffer"}}}', 1 ) =  (SELECT split_part(embeds ,'"type": "Buffer"}}}', 1 ) as embeds  FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x5bd2d6da%')

and it is done, we have all the quote casts for the original cast:

we take the Fids from this and use them to query wallets and anything we need from the farcaster_profile_with_addresses dataset:

select fid, fname,  SUBSTRING(verified_addresses, 3, 42) as address from  dune.neynar.dataset_farcaster_profile_with_addresses where fid in
(Select fid from dune.neynar.dataset_farcaster_casts where split_part(embeds ,'"type": "Buffer"}}}', 1 ) =  (SELECT split_part(embeds ,'"type": "Buffer"}}}', 1 ) as embeds  FROM dune.neynar.dataset_farcaster_casts 
where cast(hash AS VARCHAR) like '0x5bd2d6da%'))

and we have what we wanted:

You can find this query here and if you have any questions or comments about this you can find me as @miladgh on Farcaster.

Loading...
highlight
Collect this post to permanently own it.
Subscribe to Tinkering Onchain and never miss a post.