So, If you are not living in a Farcaster cave, you should know that Power Badge is the obsession of the day on Warpcast. Apparently, interacting with the holders of this elusive purple badge brings you riches beyond imagination. So, naturally, you have to know which of them follows you so you can show them some love so none of them unfollows you. For that, we summon the power of SQL and API through Dune.com and extract this forbidden knowledge.
The First thing we need is the list of these exalted Powerbadge holders. The gods of Warpcast kindly provide this knowledge as an API endpoint which you can see right here.
We right this simple query to get the list of all Power badge holders according to Warpcast API:
SELECT * FROM UNNEST(CAST(JSON_EXTRACT(json_parse(http_get('https://api.warpcast.com/v2/power-badge-users')),'$.result.fids') as ARRAY(INTEGER)))So let's break this down:
http_get is a LiveFetch function, That can interact with HTTP servers directly in SQL queries. This function allows us to fetch data from external APIs.
The http_get function returns JSON which is data in a human-readable format, with json_parse we get the thing we want out of JSON.
JSON_EXTRACT again makes the FIDs into a Nice JSON and with cast and the as ARRAY(INTEGER) part, we turn that into a neat table of FIDS.
Running the query returns this list of about 4300 FIDs (farcaster IDs) as I'm writing this post. This is all of the Farcaster accounts that currently hold powerBadge on Warpcast:
So for the next part we need the list of your followers on Farcaster. The kind folks at neynar maintain a Dataset on Dune called farcaster_links. you can read about it here. This database contains the follower and following relationships on the Farcaster protocol, It has some important fields, The first one is target_fid which in this case is you. The second is FID which is your follower and the third one is deleted_at which should be NULL or empty, Otherwise, it contains the exact time when that FID has unfollowed you. So using that and my FID (291065) as an example we use this single query to return the list of FIDs of my followers on Farcaster Protocol (not warpcast):
SELECT FID FROM dune.neynar.dataset_farcaster_links WHERE
type = 'follow'
AND deleted_at IS NULL
and target_fid = 291065 This query returns the list of my Farcaster followers:
So now we have two lists containing FIDs, one is the power badge Holders and one is your followers. The last step is getting the info of Farcaster accounts that their FIDs are present in both of these lists. That's Doable with joins but I prefer subqueries:
select fid,fname,display_name,verified_addresses from dune.neynar.dataset_farcaster_profile_with_addresses
where fid in (SELECT * FROM UNNEST(CAST(JSON_EXTRACT(json_parse(http_get('https://api.warpcast.com/v2/power-badge-users')),'$.result.fids')as ARRAY(INTEGER)) ))
and fid in ( SELECT fid FROM dune.neynar.dataset_farcaster_links
WHERE type = 'follow'
AND deleted_at IS NULL
and target_fid = {{Your fid here:}})
And running this returns The list we wanted:
I have the last query here, you can play with it and enter your FID and get your list, you can even export that list to Google Sheets if you like, I wrote about that in my last post here. If you see any mistakes or have any questions about this, I'm @miladgh on farcaster.