DIY Steemit Statistics with Python: Part 5 - Reputation

Our next order of business: the distribution of reputation scores among active Steemit users.

Before we start, we prepare the workspace as usual (see the previous posts in the series for additional context: 1, 2, 3, 4):

%matplotlib inline
import sqlalchemy as sa, pandas as pd, seaborn as sns, matplotlib.pyplot as plt

sns.set_style()
e = sa.create_engine('mssql+pymssql://steemit:[email protected]/DBSteem')

def sql(query, index_col=None):
    return pd.read_sql(query, e, index_col=index_col)

The reputation of each user is updated dynamically with each vote they receive. In principle, we could recover it by processing the TxVotes blockchain table and accounting for all the votes and flags one by one. This would probably be computationally quite heavy, however. Instead, we can rely on the fact that SteemSQL is helpfully tracking various current user-related metrics in a dedicated table for us already.

Indeed, let us take a look at all the tables currently available to us at SteemSQL:

sql("select * from information_schema.tables")

	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
0	DBSteem	dbo	Reblogs	VIEW
1	DBSteem	dbo	TxWithdraws	BASE TABLE
2	DBSteem	dbo	TxWithdrawVestingRoutes	BASE TABLE
3	DBSteem	dbo	TxWitnessUpdates	BASE TABLE
4	DBSteem	dbo	VOCurationRewards	BASE TABLE
5	DBSteem	dbo	Tokens	BASE TABLE
6	DBSteem	dbo	Blocks	BASE TABLE
7	DBSteem	dbo	Transactions	BASE TABLE
8	DBSteem	dbo	TxCustomsReblogs	VIEW
9	DBSteem	dbo	TxCustomsFollows	VIEW
10	DBSteem	dbo	Comments	BASE TABLE
11	DBSteem	dbo	VOInterests	BASE TABLE
12	DBSteem	dbo	TxAccountCreates	BASE TABLE
13	DBSteem	dbo	VOFillOrders	BASE TABLE
14	DBSteem	dbo	Accounts	BASE TABLE
15	DBSteem	dbo	TxAccountRecovers	BASE TABLE
16	DBSteem	dbo	TxVotes	BASE TABLE
17	DBSteem	dbo	TxAccountUpdates	BASE TABLE
18	DBSteem	dbo	TxAccountWitnessProxies	BASE TABLE
19	DBSteem	dbo	TxAccountWitnessVotes	BASE TABLE
20	DBSteem	dbo	VOFillConvertRequest	BASE TABLE
21	DBSteem	dbo	TxClaimRewardBalances	BASE TABLE
22	DBSteem	dbo	TxComments	BASE TABLE
23	DBSteem	dbo	TxCommentsOptions	BASE TABLE
24	DBSteem	dbo	TxConverts	BASE TABLE
25	DBSteem	dbo	TxCustoms	BASE TABLE
26	DBSteem	dbo	VOAuthorRewards	BASE TABLE
27	DBSteem	dbo	TxDelegateVestingShares	BASE TABLE
28	DBSteem	dbo	TxDeleteComments	BASE TABLE
29	DBSteem	dbo	TxEscrowApproves	BASE TABLE
30	DBSteem	dbo	TxEscrowDisputes	BASE TABLE
31	DBSteem	dbo	TxEscrowReleases	BASE TABLE
32	DBSteem	dbo	VOShutdownWitnesses	BASE TABLE
33	DBSteem	dbo	TxEscrowTransfers	BASE TABLE
34	DBSteem	dbo	VOFillVestingWithdraws	BASE TABLE
35	DBSteem	dbo	TxFeeds	BASE TABLE
36	DBSteem	dbo	TxLimitOrders	BASE TABLE
37	DBSteem	dbo	Followers	VIEW
38	DBSteem	dbo	TxPows	BASE TABLE
39	DBSteem	dbo	TxTransfers	BASE TABLE

Scanning through the list, we can guess that Accounts is most probably the table we are interested in:

sql("select top 3 * from Accounts")

From the results of this query (omitted) can see that there's a reputation field indeed. It is, however, given as a long, unfamiliar number - not the short value we are used to seeing in the profile page. It turns out we can convert this "raw" value to a "usual" reputation score using the following formula:

Let us test it:

sql("""
select 
    name, 
    reputation as raw_reputation,
    cast(log10(reputation)*9 - 56 as int) as reputation
from Accounts 
where name = 'konstantint'""")

	name	raw_reputation	reputation
0	konstantint	263717261138	46

This worked as expected. Now let us finally count how many accounts are there per each reputation score (dropping the scores below 26, just like @arcange does it):

reputations = sql("""
with Data as 
    (select 
       cast(log10(isnull(reputation, 0))*9 - 56 as int) as Reputation
     from Accounts
     where reputation > 0)

select 
    Reputation, count(*) as Count
from Data 
group by Reputation
having Reputation > 25
order by Reputation desc""", "Reputation")

For the sake of variety, we made use of a Common Table Expression in our query this time.

Let us conclude by plotting the results: