My friend introduced me to BitClout a couple weeks ago. I’m no crypto or blockchain expert (though the Bitcoin rally over the past few years has been very kind to me), but it seems like a really powerful idea.
A Youtube video of this article is below —
From Wikipedia — “BitClout is an open source social media platform where users can buy and sell cryptocurrency “creator coins” based on people’s reputations and speculation of future value.” Basically, you can bet on the reputation of different people, and the value of their coins go up and down based on demand (via an automated market making model).
BitClout is backed by a bunch of really high profile investors, including Sequoia, a16z, and Social Capital. It has gotten a bunch of traction, including almost 25k Twitter followers as of this writing.
BitClout has detailed documentation here. It was founded by a person that goes by the name of diamondhands, and the central thesis is to build an open social network via blockchain. BitClout.com is actually just an example of what you can build on the BitClout platform, and their vision is that multiple front ends are built on the same blockchain.
The interesting thing about BitClout is because it is based on blockchain, all the data is publicly accessible by scraping this chain. However, just because it is possible does not mean it is easy. Every person wanting to analyze BitClout data or build an app on top of BitClout needs to build and maintain large scale data pipelines.
So we have grabbed the data and put it into BigQuery and made it accessible on the Fidap data platform.
Let’s dive in and start exploring the data — here’s a link to the BitClout dataset on Fidap. It’s big, but not huge, especially compared to some other datasets on Fidap. But still, at 1 billion rows and 500+ GB, it’s a non trivial amount of data.
Note that we are currently updating the data about once a week — though we do have the ability to turn on live, streaming updates.
The table names are a bit difficult to understand at first glance -
Using this data, we can write a simple query that just gets back a given user’s PKID given their Username — for example -
select * from fidap-301014.bitclout.PrefixProfileUsernameToProfilePubKey_25 where Username = ‘elonmusk’
This gives us the result -
You can run this query on platform here.
Let’s now do something a bit more complex. We’ll explore how to find users with the most “clout” on BitClout — as measured by how many likes they have.
In order to do this, we need to join four different tables together -
Here’s what the SQL query looks like -
SELECT Username, NumUsernames, SUM(Likes) AS total_likes, ROUND(SUM(Likes)*100/SUM(SUM(Likes)) OVER(), 2) AS percent_of_total
(SELECT t.Body, u.Username, u.NumUsernames, COUNT(l.PublicKey) AS Likes
FROM fidap-301014.bitclout.PrefixPostHashToPostEntry_17 AS t
LEFT JOIN fidap-301014.bitclout.PrefixLikerPubKeyToLikedPostHash_30 AS l
ON t.PostHash = l.LikedPostHash
JOIN fidap-301014.bitclout.PrefixPKIDToPublicKey_37 AS p
ON t.PosterPublicKey = p.PublicKey
JOIN (SELECT PKID, Username, NumUsernames FROM (
SELECT PKID, Username, RANK() OVER(PARTITION BY PKID ORDER BY Time DESC) Rank,
COUNT(DISTINCT Username) OVER (PARTITION BY PKID) NumUsernames
) WHERE Rank = 1 GROUP BY 1,2,3
) AS u ON p.PKID = u.PKID
GROUP BY Body, Username, NumUsernames
ORDER BY Likes DESC
) AS table1
GROUP BY Username, NumUsernames
ORDER BY total_likes DESC LIMIT 100
Our results are here -
What else should we do on BitClout data? I want to see which coins are undervalued based on followers and likes on BitClout.
Find our company news, product announcements, and in depth data analysis on our blog.