Repository
https://github.com/steemit/steem
This is a follow-up analysis which is an attempt to determine if inactive accounts have any impact on the witness rankings.
The initial contribution is here: @abh12345/do-inactive-account-votes-for-witnesses-affect-the-witness-ranks
In the previous contribution, 180 days was used as the cut-off point for an 'inactive' account. This time, multiple cut-off dates are analysed against various activity dates.
Adapted from source
Contents
- Background
- Assumptions / Criteria used
- Results
- Overview and analysis of results
a) Last bandwidth update
b) Last vote or comment
c) Last witness vote change - Summary
- Scripts used in the production of this report
Background
There have been a number of discussions recently regarding inactive accounts that have votes cast for witnesses.
The basic premise is that in order to vote for say an election, or a change in government, the voter must:
- be alive
- show proof of identification
If the above is not true, then a when election time comes, a new vote cannot be cast.
Within the Steem blockchain code, obviously there is currently no mechanism to check the user of an account is deceased or has lost their keys (identification). And so as it stands, the witness votes cast prior to one or both or these events occurring will remain in place forever.
From: @ats-witness/steem-witnesses-vote-number-and-decay
Our witnesses ought to be representative of the active invested voters.
... to help ensure that votes are being cast by active users and to help ensure that witnesses are being routinely scrutinized/vetted and voted on accordingly, requiring “stale” votes to be recast periodically could be a beneficial protocol for the Steem blockchain.
... requiring a periodic vote will mitigate against truly inactive and/or uninterested voters. It can also help mitigate against entrenched witnesses that may have veered from their witness responsibilities and their stated intentions/goals.
In the post linked above, the following is put forward as a possible date to commence 'vote decay' on a witness vote that has been cast by an 'inactive' account:
- A vote will begin to decay 52 weeks after being cast.
- Decay will occur over a 13-week period (the same weekly influence reduction and time-frame as a full SP power-down cycle).
This analysis includes work to assess if these dates, or a shorter timespan would have any effect on the witness ranks.
Assumptions / Criteria used
For the purpose of this analysis, 60, 90, 180, 365, and 465 days have been chosen as the number days to assess inactivity.
These dates have been used against the following criteria:
- Last bandwidth update
- Last witness vote change
- Last vote and/or last comment (including post)
All votes are accounted for, including proxy -> proxy -> proxy -> proxy -> proxy -> witness
For this report, only the active witnesses are accounted for, and this list was taken from https://steemian.info/witnesses on the 29th October 2018 at 2:30 pm UTC.
Results
To collate the data, every accounts VESTS voting for each witness, via proxy or directly, have been totaled depending on the criteria stated in the previous section.
- 5 dates: 60 days, 90 days, 180 days, 365 days, and 465 days
- Inactivity criteria: Last bandwidth update (any activity), Last witness vote, and/or last vote or comment/post
Data was collected from SteemSQL and stored locally on the 29th October 2018 at 2:30am UTC.
The raw data, which can be copied into excel directly, for the sections below can be found here:
Bandwidth inactivity data: https://hackmd.io/s/BkaYBir37
Last witness vote inactivity data : https://hackmd.io/s/r1CvLsr3m
Last vote and/or comment inactivity data: https://hackmd.io/s/H1F9LjS2m
Overview of results
Last bandwidth update
This field holds a date in which any activity on the Steem blockchain last took place. This should include transactions such as the time of last vote, or the last wallet transfer, or also the last game/cards reveal on Steemmonsters.com
The next table displays the various percentages of total voting MVESTS when using the 5 inactivity dates stated above.
From this table we can make the following observations:
The vast majority of MVESTS (held by the accounts) voting for any of the top 100 witnesses have performed some activity in each of the inactivity dates analysed
The positions of the top 20 witness would remain unchanged if witness votes 'dropped' on any of the dates analysed
Despite being perhaps the fairest date to use to decide if an account is inactive of not, using the last bandwidth update date would have next to no effect on witness standings throughout all active witness placings
In this chart which uses the last bandwidth update data, the witnesses have been grouped (in original MVEST order), 20 at a time, and the percentages are averaged within these groups.
This again shows that the 'movement' between the rankings is negligible, and the only noticeable figure is the activity in the past 60 days of the accounts voting for the top 20 witnesses. However, this percentage is less than 1% lower than each of the other witness rank groupings.
When you spend a lot of time gathering data for an analysis, and the results are like this, it's a little disheartening to say the least!
Last vote or comment (on content)
For this table, a combination of the last vote and last comment dates were used. If either of these dates fell within the ranges specified, the MVESTS were included in the calculations. For the column headings, 'VC' stands for 'Vote or Comment', and the number, e.g. '60', is the maximum date for an accounts MVEST to be counted.
Example: 69.633% of the total MVESTS voting for 'timcliff' have voted or made a comment(post) in the last 90 days
From this table we can make the following observations:
- Many percentages are much lower than 100%, for example:
- For the accounts voting for 'aggroed', just over 2/3rds have posted or voted on content in the past 60 days, and less than 75% have posted or voted on content in the last 450 days.
Again, by grouping the witness ranks together we can see from the following table and chart that voting/posting inactivity percentages are generally lower (more activity) the further down the the witness ranking we go.
If 'last vote/post' was to be used as the metric to classify inactivity, the witness rankings would look like this should 365 days be used as the cut-off date for activity.
- All of the top 20 witness rankings change
- 5 witnesses move into the top 20
- 'rival' would lose the most positions (39), whilst 'pfunk' would gain the most positions (13) and move into the top 10
Although this metric is unlikely to be used as criteria to assess witness voter inactivity, there is at least some movement in the rankings for each of the inactivity dates assessed.
Last witness vote change
The final metric assessed is the last time each account voting for a particular witness, made a change to their witness votes. If the account proxies another account to cast their votes, the date of their proxy (or their proxies proxy, etc) is used.
The table above has again been summarized into another table and chart below.
Interestingly, the most static range is the accounts voting for witnesses ranked 80-100. This is followed by the 0-20 range, which could have been expected to be the most static range by some.
If 'last witness vote change' was to be used as the metric to classify inactivity, the witness rankings would look like this should 365 days be used as the cut-off date for activity.
- Much of the rankings remain unchanged or adjust by 1/2 places
- 'blocktrades' regains a top 10 position, while 'smooth' drops outside the top ten
- 'charlieshrem' would fall the most places (23) with this criteria applied
As with 'last vote or comment' I cannot see this criteria being used to gauge inactivity of an account voting for a witness. Although I feel it would make a little more sense to do so. Further to this, the witness rankings (based on 365 days inactivity) show little change.
Summary
For the most part, the accounts holding the MVESTS voting for witnesses are active in some way.
The most 'sway' is within the 60-90 day inactivity ranges, but this may be deemed too short a time period to assess the inactivity of an account.
Using 'last bandwidth update' as criteria to assess inactivity of the accounts voting for witnesses does not affect the witness standings to any meaningful extent using any of the inactivity dates assessed.
Posting/Voting activity is associated less with the higher ranks than the lower, but this is unlikely to be used as criteria to assess for the inactivity of accounts voting.
At present, with the Steem blockchain's life at around 2.5 years, I think it is too early to consider the removal of witness votes using the inactivity criteria in this analysis. Applying vote degradation, as well as being potentially complex, would by inference, also have little effect on the witness standings using the criteria above.
Thanks
Asher
Scripts used in the production of this report
-- Raw data collection for local insert
--tbl_witness
select name, vesting_shares, last_post, last_vote_time, last_bandwidth_update, proxy, witness_votes from accounts with (nolock)
order by name desc
-- tbl_witnessvotes
select account,witness, timestamp from TxAccountWitnessVotes with (nolock)
-- tbl_witnessproxy
select account,proxy,timestamp from TxAccountWitnessProxies
-------------------------
-- BANDWIDTH, 365 days
SET NOCOUNT ON
Declare @witness Varchar(2000)
Declare @total_SP float
Declare @mvests float
DECLARE WIT_CURSOR CURSOR FOR
select [wit] FROM [SteemSQL].[dbo].[witnesses]
OPEN WIT_CURSOR
FETCH NEXT FROM WIT_CURSOR
INTO @witness
WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert into tbl_witness_60
SELECT @witness, sum(cast(left(vesting_shares, len(vesting_shares)-6) as float)*495.644/1000000)
,sum(cast(left(vesting_shares,charindex(' ', vesting_shares)-1) as float))/1000000
from [dbo].[tbl_witness]
where name in (
select a1.name from [dbo].[tbl_witness] a1
where witness_votes like '%'+@witness+'%'
and (
a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a1.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
where a2.witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a2.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
or
a2.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-60)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
where a3.witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a3.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
or
a3.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-60)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
where a4.witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a4.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
or
a4.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-60)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
where a5.witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a5.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
or
a5.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-60)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
inner join [dbo].[tbl_witness] a6 on a5.proxy = a6.name
where a6.witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-60 or a1.last_vote_time > getdate()-60 or a1.last_bandwidth_update > getdate()-60
or
a6.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-60)
or
a6.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-60)
)
)
FETCH NEXT FROM WIT_CURSOR
INTO @witness
END
CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR
SET NOCOUNT OFF
-------------------------
-- VOTE OR COMMENT, 465 days
SET NOCOUNT ON
Declare @witness Varchar(2000)
Declare @total_SP float
Declare @mvests float
DECLARE WIT_CURSOR CURSOR FOR
select [wit] FROM [SteemSQL].[dbo].[witnesses]
OPEN WIT_CURSOR
FETCH NEXT FROM WIT_CURSOR
INTO @witness
WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert into tbl_witness_votes_465
SELECT @witness, 0
,sum(cast(left(vesting_shares,charindex(' ', vesting_shares)-1) as float))/1000000
from [dbo].[tbl_witness]
where name in (
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
where a2.witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
union
select a1.name from [dbo].[tbl_witness] a1
where witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
where a3.witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
where a4.witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
where a5.witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
inner join [dbo].[tbl_witness] a6 on a5.proxy = a6.name
where a6.witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-465 or a1.last_post > getdate()-465 )
)
FETCH NEXT FROM WIT_CURSOR
INTO @witness
END
CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR
SET NOCOUNT OFF
--------------------------
-- WITNESS VOTE CHANGES, 365 days
SET NOCOUNT ON
Declare @witness Varchar(2000)
Declare @total_SP float
Declare @mvests float
DECLARE WIT_CURSOR CURSOR FOR
select [wit] FROM [SteemSQL].[dbo].[witnesses]
OPEN WIT_CURSOR
FETCH NEXT FROM WIT_CURSOR
INTO @witness
WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert into tbl_witness_witness_votes_365
SELECT @witness, 0
,sum(cast(left(vesting_shares,charindex(' ', vesting_shares)-1) as float))/1000000
from [dbo].[tbl_witness]
where name in (
select a1.name from [dbo].[tbl_witness] a1
where witness_votes like '%'+@witness+'%'
and
(
a1.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
where a2.witness_votes like '%'+@witness+'%'
and
(
a2.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
or
a2.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-365)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
where a3.witness_votes like '%'+@witness+'%'
and
(
a3.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
or
a3.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-365)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
where a4.witness_votes like '%'+@witness+'%'
and
(
a4.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
or
a4.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-365)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
where a5.witness_votes like '%'+@witness+'%'
and
(
a5.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
or
a5.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-365)
)
union
select a1.name from [dbo].[tbl_witness] a1 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
inner join [dbo].[tbl_witness] a3 on a2.proxy = a3.name
inner join [dbo].[tbl_witness] a4 on a3.proxy = a4.name
inner join [dbo].[tbl_witness] a5 on a4.proxy = a5.name
inner join [dbo].[tbl_witness] a6 on a5.proxy = a6.name
where a6.witness_votes like '%'+@witness+'%'
and
(
a6.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-365)
or
a6.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-365)
)
)
FETCH NEXT FROM WIT_CURSOR
INTO @witness
END
CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR
SET NOCOUNT OFF
--------------------------
-- FOR DISPLAY
-- Bandwidth
select a.witness, a.vests as MV, b.vests as MV_60, c.vests as MV_90, d.vests as MV_180, e.vests as MV_365, f.vests as MV_465
FROM [tbl_witness_all] a
inner join [tbl_witness_60] b on a.witness = b.witness
inner join [tbl_witness_90] c on a.witness = c.witness
inner join [tbl_witness_180] d on a.witness = d.witness
inner join [tbl_witness_365] e on a.witness = e.witness
inner join [tbl_witness_465] f on a.witness = f.witness
-- Witness Vote
select a.witness, a.vests as MV, b.vests as WIT_60, c.vests as WIT_90, d.vests as WIT_180, e.vests as WIT_365, f.vests as WIT_465
FROM [tbl_witness_all] a
inner join [tbl_witness_witness_votes_60] b on a.witness = b.witness
inner join [tbl_witness_witness_votes_90] c on a.witness = c.witness
inner join [tbl_witness_witness_votes_180] d on a.witness = d.witness
inner join [tbl_witness_witness_votes_365] e on a.witness = e.witness
inner join [tbl_witness_witness_votes_465] f on a.witness = f.witness
-- Vote or Post
select a.witness, a.vests as MV, b.vests as VC_60, c.vests as VC_90, d.vests as VC_180, e.vests as VC_365, f.vests as VC_465
FROM [tbl_witness_all] a
inner join [tbl_witness_votes_60] b on a.witness = b.witness
inner join [tbl_witness_votes_90] c on a.witness = c.witness
inner join [tbl_witness_votes_180] d on a.witness = d.witness
inner join [tbl_witness_votes_365] e on a.witness = e.witness
inner join [tbl_witness_votes_465] f on a.witness = f.witness