Repository
<p dir="auto"><span><a href="https://github.com/steemit/steem" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://github.com/steemit/steem
<p dir="auto">This is a follow-up analysis which is an attempt to determine if inactive accounts have any impact on the witness rankings.
<p dir="auto"><span>The initial contribution is here: <a href="https://steemit.com/utopian-io/@abh12345/do-inactive-account-votes-for-witnesses-affect-the-witness-ranks" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://steemit.com/utopian-io/@abh12345/do-inactive-account-votes-for-witnesses-affect-the-witness-ranks
<p dir="auto">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.
<p dir="auto"><center><br />
<img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmVtdJ33TPy3zi3TUAtVqx62zpe42h1z4rJKHpRwgT3tnR/stats.jpg" alt="stats.jpg" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmVtdJ33TPy3zi3TUAtVqx62zpe42h1z4rJKHpRwgT3tnR/stats.jpg 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmVtdJ33TPy3zi3TUAtVqx62zpe42h1z4rJKHpRwgT3tnR/stats.jpg 2x" /><br />
<sub><b>Adapted from <a href="https://pixabay.com/en/statistics-graph-chart-data-3411473/" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">source<br />
<hr />
<h3>Contents
<h3>
<ul>
<li>Background
<li>Assumptions / Criteria used
<li>Results
<li>Overview and analysis of results<br />
a) Last bandwidth update<br />
b) Last vote or comment<br />
c) Last witness vote change
<li>Summary
<li>Scripts used in the production of this report
<hr />
<h3>Background
<h3>
<p dir="auto">There have been a number of discussions recently regarding inactive accounts that have votes cast for witnesses.
<p dir="auto">The basic premise is that in order to vote for say an election, or a change in government, the voter must:
<ul>
<li>be alive
<li>show proof of identification
<h4>
<p dir="auto">If the above is not true, then a when election time comes, a new vote cannot be cast.
<p dir="auto">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.
<p dir="auto"><span>From: <a href="https://steemit.com/steem/@ats-witness/steem-witnesses-vote-number-and-decay" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://steemit.com/steem/@ats-witness/steem-witnesses-vote-number-and-decay
<blockquote>
<p dir="auto">Our witnesses ought to be representative of the active invested voters.
<h4>
<blockquote>
<p dir="auto">... 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.
<h4>
<blockquote>
<p dir="auto">... 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.
<p dir="auto">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:
<ol>
<li>A vote will begin to decay 52 weeks after being cast.
<li>Decay will occur over a 13-week period (the same weekly influence reduction and time-frame as a full SP power-down cycle).
<p dir="auto">This analysis includes work to assess if these dates, or a shorter timespan would have any effect on the witness ranks.
<hr />
<h3>Assumptions / Criteria used
<h3>
<p dir="auto">For the purpose of this analysis, 60, 90, 180, 365, and 465 days have been chosen as the number days to assess inactivity.
<p dir="auto">These dates have been used against the following criteria:
<ul>
<li>Last bandwidth update
<li>Last witness vote change
<li>Last vote and/or last comment (including post)
<p dir="auto">All votes are accounted for, including proxy -> proxy -> proxy -> proxy -> proxy -> witness
<p dir="auto"><span>For this report, only the active witnesses are accounted for, and this list was taken from <a href="https://steemian.info/witnesses" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://steemian.info/witnesses on the 29th October 2018 at 2:30 pm UTC.
<hr />
<h3>Results
<h3>
<p dir="auto">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.
<ul>
<li>5 dates: 60 days, 90 days, 180 days, 365 days, and 465 days
<li>Inactivity criteria: Last bandwidth update (any activity), Last witness vote, and/or last vote or comment/post
<p dir="auto"><sub><b>Data was collected from SteemSQL and stored locally on the 29th October 2018 at 2:30am UTC.
<p dir="auto">The raw data, which can be copied into excel directly, for the sections below can be found here:
<p dir="auto"><br /><br /><span>Bandwidth inactivity data: <a href="https://hackmd.io/s/BkaYBir37" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://hackmd.io/s/BkaYBir37<span>
Last witness vote inactivity data : <a href="https://hackmd.io/s/r1CvLsr3m" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://hackmd.io/s/r1CvLsr3m<span>
Last vote and/or comment inactivity data: <a href="https://hackmd.io/s/H1F9LjS2m" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">https://hackmd.io/s/H1F9LjS2m
<hr />
<h3>Overview of results
<h3>
<h4>Last bandwidth update
<h4>
<p dir="auto">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
<p dir="auto">The next table displays the various percentages of total voting MVESTS when using the 5 inactivity dates stated above.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmYZmDr17kHBRhDPwdFPrebtx95td3dbzYZMy6n3jc2mkz/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmYZmDr17kHBRhDPwdFPrebtx95td3dbzYZMy6n3jc2mkz/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmYZmDr17kHBRhDPwdFPrebtx95td3dbzYZMy6n3jc2mkz/image.png 2x" />
<p dir="auto">From this table we can make the following observations:
<ul>
<li><p dir="auto">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
<li><p dir="auto">The positions of the top 20 witness would remain unchanged if witness votes 'dropped' on any of the dates analysed
<li><p dir="auto">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
<p dir="auto">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.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRdcpKQLmqNBfnJ41JSut6BLMNaHAas47qk7AnFNJKFWe/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRdcpKQLmqNBfnJ41JSut6BLMNaHAas47qk7AnFNJKFWe/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmRdcpKQLmqNBfnJ41JSut6BLMNaHAas47qk7AnFNJKFWe/image.png 2x" />
<p dir="auto">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.
<p dir="auto">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!
<hr />
<h4>Last vote or comment (on content)
<h4>
<p dir="auto">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.
<p dir="auto">Example: 69.633% of the total MVESTS voting for 'timcliff' have voted or made a comment(post) in the last 90 days
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmYsdSDT6CmZX5E2tGungiBVCPbvoFVGBqXuHJXuLCva92/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmYsdSDT6CmZX5E2tGungiBVCPbvoFVGBqXuHJXuLCva92/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmYsdSDT6CmZX5E2tGungiBVCPbvoFVGBqXuHJXuLCva92/image.png 2x" />
<p dir="auto">From this table we can make the following observations:
<ul>
<li>Many percentages are much lower than 100%, for example:
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmV4raHL4jfrVuEAHHp4W9Rhv3Hndb6168PrutfEM9teSV/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmV4raHL4jfrVuEAHHp4W9Rhv3Hndb6168PrutfEM9teSV/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmV4raHL4jfrVuEAHHp4W9Rhv3Hndb6168PrutfEM9teSV/image.png 2x" />
<ul>
<li>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.
<p dir="auto">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.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRfadB3jpL47h7cERyXjrNHAz1wMFho7r3nZCDFBp3smj/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRfadB3jpL47h7cERyXjrNHAz1wMFho7r3nZCDFBp3smj/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmRfadB3jpL47h7cERyXjrNHAz1wMFho7r3nZCDFBp3smj/image.png 2x" />
<h1>
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmV8hENviGcvWeDQaWQr9hDekZjNWCU8CN2vgrL3MgjSEK/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmV8hENviGcvWeDQaWQr9hDekZjNWCU8CN2vgrL3MgjSEK/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmV8hENviGcvWeDQaWQr9hDekZjNWCU8CN2vgrL3MgjSEK/image.png 2x" />
<h1>
<p dir="auto">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.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmcMnHLtH85byGHbZtRoN9P2JjvmVwXxDjhekTibc4MeYF/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmcMnHLtH85byGHbZtRoN9P2JjvmVwXxDjhekTibc4MeYF/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmcMnHLtH85byGHbZtRoN9P2JjvmVwXxDjhekTibc4MeYF/image.png 2x" />
<ul>
<li>All of the top 20 witness rankings change
<li>5 witnesses move into the top 20
<li>'rival' would lose the most positions (39), whilst 'pfunk' would gain the most positions (13) and move into the top 10
<p dir="auto">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.
<h4>Last witness vote change
<h4>
<p dir="auto">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.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmPUpxdWWXgvf4TrJFsKV4iLTtqNHqdmpXNmmAcJfFb67T/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmPUpxdWWXgvf4TrJFsKV4iLTtqNHqdmpXNmmAcJfFb67T/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmPUpxdWWXgvf4TrJFsKV4iLTtqNHqdmpXNmmAcJfFb67T/image.png 2x" />
<p dir="auto">The table above has again been summarized into another table and chart below.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRza17VqK6BDjWZY4H6Jy4vrw7xbQ8uBC46SMYMx8u25r/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmRza17VqK6BDjWZY4H6Jy4vrw7xbQ8uBC46SMYMx8u25r/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmRza17VqK6BDjWZY4H6Jy4vrw7xbQ8uBC46SMYMx8u25r/image.png 2x" />
<h1>
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmNbu3V3mdyVFUenLxLWdgNCdm2yJ3xjhZi9wPLFjnDLRu/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQmNbu3V3mdyVFUenLxLWdgNCdm2yJ3xjhZi9wPLFjnDLRu/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQmNbu3V3mdyVFUenLxLWdgNCdm2yJ3xjhZi9wPLFjnDLRu/image.png 2x" />
<h1>
<p dir="auto">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.
<p dir="auto">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.
<p dir="auto"><img src="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQme7PnPDjdTp6X1vgL7CXAs2AMtyRkLXVQq2YVCGsgRPqH/image.png" srcset="https://images.hive.blog/768x0/https://cdn.steemitimages.com/DQme7PnPDjdTp6X1vgL7CXAs2AMtyRkLXVQq2YVCGsgRPqH/image.png 1x, https://images.hive.blog/1536x0/https://cdn.steemitimages.com/DQme7PnPDjdTp6X1vgL7CXAs2AMtyRkLXVQq2YVCGsgRPqH/image.png 2x" />
<ul>
<li>Much of the rankings remain unchanged or adjust by 1/2 places
<li>'blocktrades' regains a top 10 position, while 'smooth' drops outside the top ten
<li>'charlieshrem' would fall the most places (23) with this criteria applied
<p dir="auto">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.
<hr />
<h3>Summary
<h3>
<p dir="auto">For the most part, the accounts holding the MVESTS voting for witnesses are active in some way.
<p dir="auto">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.
<p dir="auto">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.
<p dir="auto">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.
<p dir="auto">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.
<p dir="auto">Thanks
<p dir="auto">Asher
<hr />
<h3>Scripts used in the production of this report
<h3>
<pre><code>
-- 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
.
Thanks @crokkon
Miniatures comments really conclude the analysis well, he picks out a couple of great points relating to security of the network, and with regards to the movement of the 3 witnesses who don't have the 'inactive' freedom vote.
It did take some time, but as long as subjects are under discussion on the platform, it's always worth a dig to try and support thinking.
Thanks for the review!
Thank you for your review, @crokkon! Keep up the good work!
I see what you are doing but wouldn't the account still show active if they sold their vote to one of the bots and just walked aay from steemit for the rest of their lives or sold their vote and died in a horrific car accident or even sold their vote and got in good with Elon Musk and ended up on Mars where the Wifi is really shitty? You see where I am going with this by now I am sure. Just because the account votes on something does not make it not dead. At that point it is basically a Democrate voter in Chicago............ a dead person voting.
Yes, it would.
It's tough isn't it. For it to work fully, we need death certificates - unlikely!
Or.......................... We could let me decide who could vote and I could be the supreme overlord of steemit. I like that better.
:)
Let's get this campaign off and running!
I like it. Too bad I am judging comedy open mic this week or I could make up a nice joke post about it. Maybe I will anyway. lol
Well the joke comment received support so maybe there is decent scope for a COM post :)
Hot damn. made a couple bucks today. Nice. I will try and figure something out. maybe a walking dead themed witness vote post since Halloween is coming up.
Nice work Asher!
Always frustrating this. I've consigned a few analyses to the trash when the results turn out to be less-than-spectacular. Fortunately a lack of movement in witness placings on the first analysis is just as interesting as big movements would be. It shows (I think) that the current witness-voting process is probably fairly robust in response to vote-decay propositions.
I'm guessing that the big changes in the second analysis "last vote or comment" are from freedom not being a voter/commenter? The three guys that jump up the rankings are the top three witnesses without the freedom vote.
Great points!
Yes it does, I should probably state that in the post now you've pointed it out :)
Very likely that is the reason.
Thanks for the additional analysis on the results, appreciated :D
Hey @miniature-tiger Here's a tip for your valuable feedback! @Utopian-io loves and incentivises informative comments.
Contributing on Utopian
Learn how to contribute on our website.
Want to chat? Join us on Discord https://discord.gg/h52nFrV.
Vote for Utopian Witness!
The word "decaying" is again a word being taken from EOS, while we should look at our own code and adapt to it and implemement what has been learned from real life.
i.e. When we vote, we vote for a "term in office", simple as that. (lets say 365 days)
Only diff. here being that anyone can change their vote at any time.
= "0"
and vote for someone else = "1" which would again have a max term on that vote of 365 days
ALL in line with our current code.
Hi Jack
Well as you can see from the above, a decaying vote (losing its MVEST total week by week) would have little effect on the rankings. Going from 365 - 465 without the decay doesn't change things much, and so for me, decaying by a % each week is not worth looking into at present.
Cheers!
I dare say that a "decaying vote" is not only a pain in the rear in comparison to a "fixed term vote, from date of voting" as far as coding goes, but also a blunder of an idea which definitely is NOT looking after the investors best interests in any way.
Due to the possible complexity of coding, lack of real change to the rankings, and the idea that a vote is a vote until it is not a vote, I would side with you on this :)
Interesting outcome. Incidentally the relative weight of any inactive user decreases over time anyway, as the total mvests steadily increase but their own proportion does not.
Posted using Partiko Android
Yes that is a good point, is it something like 8% a year?
Cheers Joseph :)
I don't know the exact rate because it decreases gradually over time. I think it's a little less than that right now.
Posted using Partiko Android
Ok thanks. Something else to think about at least :)
well it would be less than the total inflationary rate because a portion of the inflation actually goes to holders of SP anyway. So... 15% less than whatever the current inflation rate, right?
Nice analysis, the results did not pan out the way I expected. I guess the majority of accounts that have voted for witnesses are active in some way on the blockchain. I suppose it could be that the inactive accounts never bothered to vote for witnesses in the first place?
Based on this research I can't see any reason to make this system more complicate than it already is, based on some perceived injustice.
I would guess so, either through lack of knowledge or for a reason.
I too think it's a little early to look into removing 'inactive' account witness votes, perhaps in a few years time...
This post was shared in the Curation Collective Discord community for curators, and upvoted and resteemed by the @c-squared community account after manual review.
Thank you!
No wonder I haven’t seen you on @steemmonsters lately! Too busy crunching numbers! Great analysis and I wonder if this was already expected as the focus realy hasn’t been to foster much retention nor bringing inactive accounts back... It is tough to cater to a wide population given that communities can become segmented. This is why I look forward to Hivemind as it would help engage communities closer together to foster retention.
I hope the same for Hivemind and expect it to help a little.
I'm on the Monsters right now, almost at level 4000 :D
great post Asher, love the innerjoin code you are mastering. Will be sending you some questions next time I have a query I wanna run.
Im rather surprised that the vote decaying doesn't change much. Very interesting analysis
Thanks Paula :D
Yeah not much action over these time frames - maybe the network isn't old enough yet to start to think about vote expiry?
More joins than a carpenters workshop.
:D
Brute force approach as brains to do it a better way are not available.
most delayed comment ever.
The reply wasn't to me, takes longer to find em 😛
Good Point, but I think indeed the hardest part is drawing the line of where you consider a Steemian inactive enough to return his/her witness votes...
On the other hand, everything that can help new witness (like this would) gets my votes! I started as a witness today! And proud of it! (I know, little child, new toy, no need to say, my wife already does that ;-) )
The initial discussions were that this (among other things like listing only the top 50) unfair to newer and lower-ranked witnesses.
From the numbers, vote cancellation isn't going to help much - yet.
Congrats on becoming a witness :D
Thanks ;-)
Don't know if it would help or not. I was reading another post earlier that claimed it would definitely make a difference, especially to new witnesses...
There really is no way to determine who is honestly active on steemit like @doomsdaychassis pointed out. If votes for witnesses were for only a set amount of time and required a person to make one comment a year for their witness vote to continue to be active would not really work either. Automation can make comments.
Now as we know the Dev-team is good enough to put an annoying little red banner thing on all pages of steemit - Perhaps a yearly banner - vote here to continue your witness selections or change them. Problem is do all the front ends have the ability to put a vote here banner up? Would that even be fair to an investor that voted for who they want and just want o sit back and be passive 100%?
It is not like steem is capable of sending out massive e-mails/notifications to all its share holders that it is time to vote for the board of directors.
People campaign for and against all kinds of things on steemit, so if someone is unhappy with a witness action there are ways and means to get the order changed, (they just do not work in reality - after all Jerry banfield is still a witness and he quit steemit).
It is the top 21 witnesses that sort of have the most say, limit the number of witness votes to 7 picks for each person, and there would be less chance of 3 accounts being able to control who the top witnesses are. (and yes I know it will be pointed out that those that want to control would just make more accounts to control the list).
Witness vote - One of those complicated issues -
Apologies for missing this one.
heh, nope. But they could stick the 'red bar' at the top of the page like they did when the fork happened.
Someone else is running Jerry's witness, you would have thought they'd want to distance themselves from the name, but his account has plenty of 'sticky' support....
People get busy, I did not know that someone "bought" his account and name. In time it will all blow over, but people will still bring all the past back up, and the new owner may have a hard go of it.
This post is very lengthy to read which looks interesting post always to read the content. I cant see my name in it, but i can see one of my friend name mentioned in top. Good information learnt from this post. Nice one!
I voted your content because you are on my whitelist.
Join our army of good authors, delegate to me to apply: 2000 SP JEDI | 1500 SP **SITH Lord | 1000 SP Skywalker | Less Stormtrooper
Witness: @castellano
Use SteemConnect to delegate safely: 50 SP | 100 SP | 200 SP | 500 SP
JEDY 2000 SP | SITH lord 1500 SP | SkyWalker 1000 SP |
Congratulations @abh12345! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :
<table><tr><td><span><img src="https://images.hive.blog/768x0/https://steemitimages.com/60x70/http://steemitboard.com/@abh12345/voted.png?201810301858" srcset="https://images.hive.blog/768x0/https://steemitimages.com/60x70/http://steemitboard.com/@abh12345/voted.png?201810301858 1x, https://images.hive.blog/1536x0/https://steemitimages.com/60x70/http://steemitboard.com/@abh12345/voted.png?201810301858 2x" /><td>You received more than 80000 upvotes. Your next target is to reach 85000 upvotes. <p dir="auto"><sub><em><a href="https://steemitboard.com/@abh12345" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Click here to view your Board of Honor<br /> <sub><em>If you no longer want to receive notifications, reply to this comment with the word <code>STOP <p dir="auto"><strong><span>Do not miss the last post from <a href="/@steemitboard">@steemitboard: <table><tr><td><a href="https://steemit.com/steemfest/@steemitboard/i06trehc" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link"><img src="https://images.hive.blog/768x0/https://steemitimages.com/64x128/https://ipfs.io/ipfs/QmU34ZrY632FFKQ1vbrkSM27VcnsjQdtXPynfMrpxDFJcF" srcset="https://images.hive.blog/768x0/https://steemitimages.com/64x128/https://ipfs.io/ipfs/QmU34ZrY632FFKQ1vbrkSM27VcnsjQdtXPynfMrpxDFJcF 1x, https://images.hive.blog/1536x0/https://steemitimages.com/64x128/https://ipfs.io/ipfs/QmU34ZrY632FFKQ1vbrkSM27VcnsjQdtXPynfMrpxDFJcF 2x" /><td><a href="https://steemit.com/steemfest/@steemitboard/i06trehc" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Be ready for the next contest!<tr><td><a href="https://steemit.com/halloween/@steemitboard/trick-or-treat-publish-your-scariest-halloweeen-story-and-win-a-new-badge" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link"><img src="https://images.hive.blog/768x0/https://steemitimages.com/64x128/http://i.cubeupload.com/RUyB3u.png" srcset="https://images.hive.blog/768x0/https://steemitimages.com/64x128/http://i.cubeupload.com/RUyB3u.png 1x, https://images.hive.blog/1536x0/https://steemitimages.com/64x128/http://i.cubeupload.com/RUyB3u.png 2x" /><td><a href="https://steemit.com/halloween/@steemitboard/trick-or-treat-publish-your-scariest-halloweeen-story-and-win-a-new-badge" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Trick or Treat - Publish your scariest halloween story and win a new badge<tr><td><a href="https://steemit.com/steemitboard/@steemitboard/steemitboard-notifications-improved" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link"><img src="https://images.hive.blog/768x0/https://steemitimages.com/64x128/http://i.cubeupload.com/NgygYH.png" srcset="https://images.hive.blog/768x0/https://steemitimages.com/64x128/http://i.cubeupload.com/NgygYH.png 1x, https://images.hive.blog/1536x0/https://steemitimages.com/64x128/http://i.cubeupload.com/NgygYH.png 2x" /><td><a href="https://steemit.com/steemitboard/@steemitboard/steemitboard-notifications-improved" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">SteemitBoard notifications improved <blockquote> <p dir="auto">Support <a href="https://steemit.com/@steemitboard" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">SteemitBoard's project! <strong><a href="https://v2.steemconnect.com/sign/account-witness-vote?witness=steemitboard&approve=1" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Vote for its witness and <strong>get one more award!Hi @abh12345!
Your UA account score is currently 6.689 which ranks you at #120 across all Steem accounts.
Your rank has not changed in the last three days.Your post was upvoted by @steem-ua, new Steem dApp, using UserAuthority for algorithmic post curation!
In our last Algorithmic Curation Round, consisting of 310 contributions, your post is ranked at #1. Congratulations!
Evaluation of your UA score:
Feel free to join our @steem-ua Discord server
In Depth Analysis and in my opinion this post can highlight important aspect of Witness, Witness Votes and Inactive Witness Votes. Keep up the great work. Wishing you an great day and stay blessed. 🙂@abh12345, In my opinion you've really deep dived and done an
Hey, @abh12345!
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!
Get higher incentives and support Utopian.io!
SteemPlus or Steeditor). Simply set @utopian.pay as a 5% (or higher) payout beneficiary on your contribution post (via
Want to chat? Join us on Discord https://discord.gg/h52nFrV.
Vote for Utopian Witness!
Hey, @abh12345.
We probably need to shy away from inactivity as a all or nothing kind of thing and maybe address it from a number of levels. I would imagine anyone with stake here who cares about it and what it's doing will think they have some level of activity, rather than disinterest or a lack of passion or whatever inactivity might actually measure.
At the same time, those that are on most days, for most of the day, posting, commenting and/or curating will be doing so differently. Perhaps different terms for different levels of activity is in order? That probably complicates things, but maybe it gets us where we want to go?
I would think that there would be a difference in the way activity is viewed between the person commenting 20-plus times a day, curating at least that much, plus three posts a week, versus the account that delegates its SP to various other accounts and does little else, popping in from time to time to check in how things are doing based on whatever standards they have for delegatees.
I don't know. Those are thoughts that come to mind, late on a Saturday night as I try to wrap up another week of engagement. :)