Do 'inactive' account votes for witnesses affect the witness ranks?

in #utopian-io • 6 years ago

Repository

https://github.com/steemit/steem

This is an analysis to try to determine if 'dead' or inactive (I'll use inactive from this point forward) accounts have any impact on the witness positions.

Contents

  • Background
  • Assumptions / Criteria used
  • Results
  • Overview of results
  • Re-ordering the top 20
  • Inactivity grouped by witness standings
  • 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.

Assumptions / Criteria used

For the purpose of this analysis, 180 days has been chosen as the number days that constitute inactivity. More background on the reasoning behind this analysis and the choice of 180 day can be found here: https://steemit.com/witness-category/@pharesim/re-abh12345-re-sircork-re-abh12345-re-sircork-re-abh12345-re-sircork-re-abh12345-re-pharesim-a-fundamental-change-to-my-witness-voting-behavior-20180507t092325061z

This number has been used against the following criteria:

  • A union of all of the below
  • Last witness vote change
  • Last bandwidth update
  • Last vote
  • 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 6th June 2018 at 8:30 am UTC.

Results

The following table (ordered by MV) displays columns described as:

  • Witness - Name of the witness
  • MV - Current MVESTS without any criteria applied
  • MV_180 - MVESTS with all criteria applied*

*This means that if an account has voted for a witness, received a bandwidth update, voted, or commented in the past 180 days, they are considered active and their MVESTS are a part of the final count.

  • MV_BW - Sum of MVESTS of accounts that have received a bandwidth update within the last 180 days
  • MV_wit_v - Sum of MVESTS of accounts that have made a change to their witness votes within the last 180 days
  • MV_vote - Sum of MVESTS of accounts that have voted on content within the last 180 days
  • MV_post - Sum of MVESTS of accounts that have posted/commented within the last 180 days

Data was collected from SteemSQL and stored locally on the 6th June 2018 at 8:30am UTC.

MVESTS are rounded to 2 decimal places for display purposes - 5 decimal places are used for all further calculations.

WitnessMVMV_180MV_BWMV_wit_vMV_voteMV_post
gtg75704.2274993.2274741.4269642.7655205.4550308.81
jesta74580.4774540.9874238.2470926.7455085.6248266.71
roelandp71472.7171378.2671111.1763728.3451875.1345111.00
timcliff69655.4669642.0469524.5566942.3852309.9344257.85
good-karma68019.4067930.9167729.1160566.3249441.8241446.11
ausbitbank64034.8264028.3063797.8662221.7246715.2240487.73
thecryptodrive61854.3261787.4861521.8959025.4443840.0638802.92
someguy12361318.6961241.4361040.1157853.5543538.6636834.34
aggroed61033.8061028.9960807.7759111.4543647.6338430.63
clayop61002.5460017.5159896.9456219.8857492.5651853.04
smooth.witness60398.5159329.7559136.8155991.4256016.1351450.59
blocktrades59925.4658344.0158226.2452034.0155465.4847077.12
lukestokes.mhth59831.9059828.9959698.0059018.2742481.1138338.88
netuoso59513.7359453.9359156.0358142.0940625.6735104.42
anyx58975.5458883.4158597.2155036.3839246.5933229.99
curie58595.0558533.4758528.1253363.7642125.5133491.11
pharesim58346.8757364.4657114.4253498.5354702.9148347.42
followbtcnews58183.4758180.2057950.4757877.5240950.9134740.28
xeldal56526.0655842.9255733.2451337.0937545.8828133.77
pfunk54925.7254832.0254542.2951700.3051616.1146310.40
jerrybanfield54581.3554575.2454510.1550081.6237383.9533432.26
riverhead52690.0751641.5251375.9147912.3847933.9044261.17
drakos50222.6050217.9050044.7549748.4032095.3230290.76
cervantes49432.9449372.3149125.0847214.7929961.4528888.82
furion47998.5147988.1547859.9645672.5628818.4526971.21
utopian-io47395.3347395.2147274.1246938.1431216.4827522.67
liondani46237.0046213.2646131.8943845.4128088.8825517.23
bhuz46131.6345194.3545035.7442341.0325674.1322712.89
busy.witness46076.5746010.1445979.6844076.9329369.3725682.63
teamsteem42062.0542052.5741854.3740279.4440590.1438237.16
yabapmatt39658.2639658.2639530.2239482.5123769.6922954.69
abit30505.9429476.7229429.2425378.6128324.7723006.77
klye30338.2330312.4530143.7629313.0830215.3625322.36
ihashfury26470.6926362.1526359.8424417.9710230.289380.32
aizensou26225.8926218.3026122.9725858.359161.727821.23
blockbrothers26182.7026182.7026052.6526131.3726102.4725190.77
therealwolf25097.1225097.1225095.1225097.129292.868279.61
reggaemuffin25044.8925034.0424868.7124716.0324907.2221550.32
rival23738.1123737.9023734.9323575.467936.587851.74
ocd-witness23186.6423186.6423057.1323186.6423152.8719050.93
prc22746.2722744.6422743.8322599.186926.236755.93
patrice22485.2422485.2422415.7022266.0321330.0717539.11
steemed21459.3220470.5420369.5519178.1919132.9415635.94
themarkymark20203.2620202.9520025.7919940.6719006.2717351.26
nextgencrypto20087.6019129.3219062.7418285.4717790.0515664.51
fyrst-witness19368.1819348.9719217.0318082.6419296.7918283.08
asbear18603.3218603.3218540.6118603.3217524.1616653.72
adsactly-witness16883.3516883.0416879.0716880.8316876.6614920.69
arcange15769.2315709.4915586.3914153.8515384.5113675.98
joseph15680.5515580.1315515.3513715.9013606.9511599.33
noisy.witness14947.3414947.3414767.8914947.3413837.0912879.81
liberosist14896.8114896.7914859.9714888.3814858.7912006.19
steemgigs14287.7814287.3014172.1213765.5114286.6813402.29
firepower14106.3614106.3614094.6514106.3614094.7113246.71
ats-witness13104.1413104.1413067.2713071.7312999.579220.75
neoxian12979.5012977.0012972.8912866.5012972.3412324.29
charlieshrem12477.8212453.1612452.917398.0111624.088045.99
jackmiller12188.7112188.7112175.8312188.7112174.8611658.82
dragosroua12186.9312183.1012145.8211821.5812128.569928.18
justyy11116.8211116.8211069.1911116.8211067.828936.77
sircork9968.019967.399964.439937.569962.359575.59
steemychicken19869.349827.169763.468108.217591.946373.23
chitty9244.519176.029169.717391.638833.627317.90
boatymcboatface8957.758939.348899.088462.548824.245970.46
emrebeyler8253.498253.498118.198253.418167.666069.17
privex8194.358194.358191.418194.358193.047848.24
complexring7323.267219.967159.635960.375939.855008.81
blockchained6446.976388.266386.285129.566078.635408.88
ura-soul6334.126334.116331.336297.656329.206310.73
qurator5877.065877.065762.835877.065876.925452.86
blackwidow5861.305860.905769.175684.064647.453682.48
felixxx5824.075821.715820.405784.655754.565626.18
yuriks20005480.365480.365479.535479.835480.044077.98
jatinhota5479.225479.225477.805475.855478.625400.68
ro-witness5398.415398.415397.295398.415332.304968.83
guiltyparties5387.815387.715386.025375.825383.863941.70
mahdiyari5121.505121.445119.835070.385116.724823.12
helo4980.474980.474865.664980.474980.244299.80
krnel4978.354972.404958.754489.834948.824869.28
b0y2k4858.484858.484798.254857.903727.293208.51
block-buster4832.844832.844832.744832.844832.744831.19
demotruk4404.974404.944401.984398.174403.134327.92
jacor-witness4070.884070.884070.844070.884070.843706.05
steem-bounty3991.123991.123991.113991.123991.123973.41
bue3723.142803.652803.362269.362782.482626.20
bitrocker20203711.763711.733710.983687.753711.593343.27
chainsquad.com3468.283409.043396.042231.222630.102573.67
steemcommunity3168.443168.443168.303168.443168.273153.47
sc-steemit3147.753145.373142.983037.433124.103133.87
masteryoda3076.353024.462967.632511.351850.852555.75
comedyopenmic3035.963035.963035.273035.963035.721642.91
anarcho-andrei2946.892946.792945.302883.542946.122835.44
swelker1012946.472942.992941.222904.132941.132894.34
yehey2928.192928.192926.262923.592924.132835.15
windforce2598.462598.462593.912588.792594.221980.92
jrswab2446.542446.542445.132446.542445.872364.72
samrg4722351.032351.032350.362351.032350.872334.42
enginewitty2331.922331.922331.322331.922331.782327.32
intelliwitness2074.212074.212073.962074.212073.732070.58
silversteem2062.042001.341944.681761.22818.251566.78

Overview of results

The table above has been presented so that it can be copied out to a spreadsheet if the reader wishes to do so, but it is not that easy to see what it represents. The next table displays the various percentages each of the criteria holds against the total voting 'MV'.

For 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 relating to the above criteria in the last 6 months
  • 'bue' is a notable exception with 75% of the MVESTS voting for this witness having performed an activity in the last 180 days
  • The activity %'s generally slide as we move from 'any activity' (MV_180_%) to a bandwidth update, witness voting, content voting, and posting.
  • New witnesses of the past 6 months (correctly, phew!) show 100% in the 'MV_180_%' column

Re-ordering the top 20

To do this, we need to go back to the first table - criteria used will be 'MV_180' and 'MV_Wit_v'.

'MV_180' - Any of the criteria met

To refresh, 'MV_180' is total MVESTS owned by accounts supporting a witness that have performed any activity, of the a fore mentioned criteria, in the past 180 days.

The top 10 witnesses are unchanged using this criteria, but there are a couple of changes further down with MVESTS supporting 'lukestokes.mhth' and 'netuoso' being more active in the past 6 months than those supporting 'smooth.witness'. The MVESTS of the accounts supporting 'blocktrades' are less active than 'lukestokes.mhth', 'netuoso', 'anyx', and 'curie'.

It is possible then that 'blocktrades' holds more votes, in terms of MVESTS held, of inactive accounts than the witnesses mentioned above.

'MV_Wit_v' - Witness vote changes

'MV_Wit_v' is the total MVESTS of the accounts that have performed a change to their witness votes in the past 180 days.

Most of the top 20 are re-ordered in some way. 'jesta' regains top place, but perhaps more notable are the moves from 'lukestokes.mhth' and 'netuoso' into the top 10, and the move from 18th to 11th from 'followbtcnews'.

If we look back at the table showing the percentage 'drop-off', this confirms that less witness voting activity has taken place by the accounts voting for 'blocktrades', than the names mentioned above.

Of course, this doesn't mean that the accounts are dead/locked-out/inactive though.

Inactivity grouped by witness standings

Well, every analysis needs at least one chart ;)

The witnesses have been grouped (in original MVEST order), 20 at a time, and the percentages are averaged within these groups.

As far as 'any activity' ('180_%'), the percentages are fairly close throughout the top 100.

The witness voting activity (of the accounts holding the MVESTS) is more static within the top 20 witnesses, than any of the other groups.

The table above is also represented in this chart, in which we can see the general rise in activity with regards to voting on, and posting content as the witness ranks get lower.

Summary

For the most part, the accounts holding the MVESTS voting for witnesses are mainly active in some way.

Posting/Voting activity is associated less with the higher ranks than the lower, but this is likely to be views as less important criteria.

One more table - The top 100 witnesses ordered by the MVESTS of the supporting accounts that have made a change to their witness voting in the last 180 days.

Do 'inactive' account votes for Witnesses affect the Witness ranks?

A little, but not as much as expected?

Scripts used in the production of this report


--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

---------------------------

-- All data 

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_all
SELECT @witness, sum(cast(left(vesting_shares, len(vesting_shares)-6) as float)*491.965/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 with (nolock)
inner join [dbo].[tbl_witness] a2 on a1.proxy = a2.name
where a2.witness_votes like '%'+@witness+'%'
union
select a1.name from [dbo].[tbl_witness] a1 where witness_votes like '%'+@witness+'%'
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+'%'
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+'%'
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+'%'
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+'%'
)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF

---------------------------

-- MV 180


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_180
SELECT @witness, sum(cast(left(vesting_shares, len(vesting_shares)-6) as float)*491.965/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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a1.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
    )
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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a2.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
or
a2.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a3.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
or
a3.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a4.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
or
a4.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
   )
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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a5.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
or
a5.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
    )
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()-180 or a1.last_vote_time > getdate()-180 or a1.last_bandwidth_update > getdate()-180
or
a6.name in (select distinct account from [dbo].[tbl_witnessvotes] where timestamp > getdate()-180)
or
a6.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
    )

)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF

-------------------------------------

-- WITNESS CHANGES

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
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()-180)
)
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()-180)
or
a2.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180)
or
a3.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180)
or
a4.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180)
or
a5.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
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()-180)
or
a6.name in (select distinct account from [dbo].[tbl_witnessproxy] where timestamp > getdate()-180)
)
)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF


------------------------

-- POST(or comment)


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_comments
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_post > getdate()-180)
union
select a1.name from [dbo].[tbl_witness] a1  
where witness_votes like '%'+@witness+'%'
and (a1.last_post > getdate()-180)
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()-180)
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()-180)
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()-180)
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()-180)
)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF

---------------------

-- BANDWIDTH UPDATE


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_bandwidth
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_bandwidth_update > getdate()-180)
union
select a1.name from [dbo].[tbl_witness] a1  
where witness_votes like '%'+@witness+'%'
and (a1.last_bandwidth_update > getdate()-180)
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_bandwidth_update > getdate()-180)
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_bandwidth_update > getdate()-180)
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_bandwidth_update > getdate()-180)
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_bandwidth_update > getdate()-180)
)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF

--------------------------------------

-- VOTE ON CONTENT

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
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()-180)
union
select a1.name from [dbo].[tbl_witness] a1  
where witness_votes like '%'+@witness+'%'
and (a1.last_vote_time > getdate()-180)
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()-180)
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()-180)
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()-180)
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()-180)
)

FETCH NEXT FROM WIT_CURSOR
INTO @witness
END

CLOSE WIT_CURSOR
DEALLOCATE WIT_CURSOR

SET NOCOUNT OFF

-------------------------

  -- FOR DISPLAY
  select a.witness, a.vests as MV, b.vests as MV_180,  d.vests as MV_BW,  c.vests as MV_wit_vote,  e.vests as MV_vote,  f.vests as MV_post
  FROM [tbl_witness_all] a
  inner join [tbl_witness_180] b on a.witness = b.witness
  inner join [tbl_witness_witness_votes] c on a.witness = c.witness
  inner join [tbl_witness_bandwidth] d on a.witness = d.witness
  inner join [tbl_witness_votes] e on a.witness = e.witness
  inner join [tbl_witness_comments] f on a.witness = f.witness
#analysis #witness #witness-category #blockchainbi
6 years ago in #utopian-io by
$191.48
  • Past Payouts $191.48, 0.00 TRX
  • - Author $150.37, 0.00 TRX
  • - Curators $41.12, 0.00 TRX
172 votes
Reply 37
Sort:  
  • Trending
    • [-]
     6 years ago 

    This outcome is really surprising for me. I would expect much higher inactive votes for witnesses. But in fact above 99% of votes is done by active accounts.

    I would say the logical explanation is simple. If you take all orcas and whales you have... Ehm... 80% of the total votes or something like this. These accounts all have a value of USD 100k+. Like any of those would become inactive?

    The same also applies for the dolphins.
    Bots that keep upvoting and also voted for witnesses will also help.

    $4.31
    • Past Payouts $4.31, 0.00 TRX
    • - Author $3.23, 0.00 TRX
    • - Curators $1.07, 0.00 TRX
    2 votes
    Reply
    [-]
     6 years ago 

    Hey @crypto-econom1st
    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!

    $0.04
    • Past Payouts $0.04, 0.00 TRX
    • - Author $0.03, 0.00 TRX
    • - Curators $0.01, 0.00 TRX
    Reply
    [-]
     6 years ago 

    Yeah I think people would side with that logic.

    It's not much of a 'problem' right now it seems, but the inactive VESTS will grow in time I think.

    Cheers!

    $0.00
      Reply
      [-]
       6 years ago 

      Maybe it won't really grow. My assumption is that if a whale or orca quits, they power down and all everything for fiat money.

      I do wonder how many whale - orca - dolphin accounts don't use their SP to upvote?
      But that's a whole kind of different subject.

      $0.00
        Reply
        [-]
         6 years ago 
        $0.00
          Reply
          [-]
           6 years ago 

          I actually meant the upvotes for rewards. Those upvotes where you got 10 each day and which decrease your VP with 2% of you do an upvote with 100% VP.

          I think it will be clear now?

          The analysis you linked to was also very cool. Almost all of your investigations are. But I'm afraid I can't read all of them due to not having that much time. And lately I also found out that you have some really great colleagues.

          $0.00
            Reply
            [-]
             6 years ago 

            The message I was able to get out of this analysis:

            If we were to remove the votes (MVests) of users who were inactive the last 6-months, the rankings of the top 10 will be the same, but for the 11th to 20th, there will be change in rankings.

            A very interesting find and I think it may be something that Steemit, Inc. and witnesses may need to consider regarding the weight of votes cast on witnesses. I also liked your predicate regarding elections.

            A chart that may have been helpful is a pie chart showing the total MVESTS cast on the witnesses, what % of this is from inactive accounts so that we have a quick glimpse of the impact or the % share of MVESTS these inactive voters have.

            Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

            To view those questions and the relevant answers related to your post, click here.

            Need help? Write a ticket on https://support.utopian.io/.
            Chat with us on Discord.
            [utopian-moderator]

            $4.09
            • Past Payouts $4.09, 0.00 TRX
            • - Author $3.15, 0.00 TRX
            • - Curators $0.95, 0.00 TRX
            2 votes
            Reply
            [-]
             6 years ago (edited)

            Thanks @eastmael

            I think the chart you are describing may have been in another recent analsys of mine, which may have been good to link.

            Cheers!

            $0.06
            • Past Payouts $0.06, 0.00 TRX
            • - Author $0.04, 0.00 TRX
            • - Curators $0.01, 0.00 TRX
            1 vote
            Reply
            [-]
             6 years ago 

            I think inactive account's witness votes should be withdrawn (If Possible).

            $0.17
            • Past Payouts $0.17, 0.00 TRX
            • - Author $0.17, 0.00 TRX
            • - Curators $0.00, 0.00 TRX
            6 votes
            Reply
            [-]
             6 years ago 

            and what criteria would you use?

            $0.04
            • Past Payouts $0.04, 0.00 TRX
            • - Author $0.03, 0.00 TRX
            • - Curators $0.01, 0.00 TRX
            1 vote
            Reply
            [-]
             6 years ago 

            Simple, I vote for the witnesses whom I see adding benefit to this platform.
            I don't like to vote blindly.
            How to do this? I really don't know coz am not a developer but they might better know this.

            $0.28
            • Past Payouts $0.28, 0.00 TRX
            • - Author $0.21, 0.00 TRX
            • - Curators $0.07, 0.00 TRX
            2 votes
            Reply
            [-]
             6 years ago 

            Can't we really use the MV_180?

            $0.00
              Reply
              [-]
               6 years ago 

              anything is possible - just need an agreement from the important people :)

              $0.00
                Reply
                [-]
                 6 years ago 

                Amazing work Asher. You must have put in a lot of hard works. Hats off for that.

                Btw, Is it possible for a normal user like me to extract these informations? Or is it limited only to some witnesses? I would like to learn more. Where can I find informations about this?

                $0.04
                • Past Payouts $0.04, 0.00 TRX
                • - Author $0.03, 0.00 TRX
                • - Curators $0.01, 0.00 TRX
                1 vote
                Reply
                [-]
                 6 years ago 

                Yes, my longest to put together post ever!

                Anyone can, you can either talk to the Blockchain directly, or like me, access a copy and use @steemsql.

                Cheers!

                $0.00
                  Reply
                  [-]
                   6 years ago 

                  It would seem that the inactive accounts do not have much impact on the witness votes. From looking at your numbers, might be because most of them were small accounts, how many dead accounts in total were there that had a witness vote out there?

                  $0.04
                  • Past Payouts $0.04, 0.00 TRX
                  • - Author $0.03, 0.00 TRX
                  • - Curators $0.01, 0.00 TRX
                  1 vote
                  Reply
                  [-]
                   6 years ago 

                  Yeah not much at all really - a bit disappointing for some i think.

                  I actually didn't count the accounts this time, was focusing too much on the VESTS :D

                  $0.00
                    Reply
                    [-]
                     6 years ago 

                    Great data and research.

                    Definitely worth considering to maintain a healthy Delegated Proof of Stake ecosystem.

                    $0.04
                    • Past Payouts $0.04, 0.00 TRX
                    • - Author $0.03, 0.00 TRX
                    • - Curators $0.01, 0.00 TRX
                    Reply
                    [-]
                     6 years ago 

                    Thanks very much!

                    $0.00
                      Reply
                      [-]
                       6 years ago 

                      Great analysis.

                      @abh12345 I skimmed over the DB code. What are you querying? Do you have your own DB? That's not how you query the the steemd is it?

                      /noob

                      $0.04
                      • Past Payouts $0.04, 0.00 TRX
                      • - Author $0.04, 0.00 TRX
                      • - Curators $0.00, 0.00 TRX
                      Reply
                      [-]
                       6 years ago (edited)

                      Hi @petervroom

                      I query SteemSQL in the first instance to pull some data local. It allows me to run a couple of update queries which again reference SteemSQL to collate the vote data.

                      The local DB Is SQL Server Personal edition which is free and does all I need.

                      Thanks for your comments 🙂

                      $0.03
                      • Past Payouts $0.03, 0.00 TRX
                      • - Author $0.02, 0.00 TRX
                      • - Curators $0.01, 0.00 TRX
                      Reply
                      [-]
                       6 years ago (edited)

                      Got it, thanks @abh12345. And thank you for the generous upvote too.

                      $0.00
                        Reply
                        [-]
                         6 years ago 

                        Hey @abh12345
                        Thanks for contributing on Utopian.
                        We’re already looking forward to your next contribution!

                        Contributing on Utopian
                        Learn how to contribute on our website or by watching this tutorial on Youtube.

                        Want to chat? Join us on Discord https://discord.gg/h52nFrV.

                        Vote for Utopian Witness!

                        $0.04
                        • Past Payouts $0.04, 0.00 TRX
                        • - Author $0.04, 0.00 TRX
                        • - Curators $0.00, 0.00 TRX
                        1 vote
                        Reply
                        [-]
                         6 years ago 

                        Thanks @utopian-io, this one was time consuming to say the least and so I'm really grateful for your support 😁

                        $0.06
                        • Past Payouts $0.06, 0.00 TRX
                        • - Author $0.04, 0.00 TRX
                        • - Curators $0.01, 0.00 TRX
                        1 vote
                        Reply
                        [-]
                         6 years ago 

                        its really a informative post for us...thanks for sharing

                        $0.00
                          Reply
                          [-]
                           6 years ago 

                          Thank you, this one took a while!

                          $0.00
                            Reply
                            [-]
                             6 years ago 

                            Witnesses are people who provide us with wonderful services
                            Everything definitely affects

                            $0.00
                              Reply
                              [-]
                               6 years ago 

                              Can you give a few examples of those wonderful services?

                              And a bit off topic, but which one do you like best, Red Naomi, Avelanche or Penny Lanes?

                              $0.00
                                Reply

                                Coin Marketplace

                                STEEM 0.30
                                TRX 0.12
                                JST 0.034
                                BTC 63688.35
                                ETH 3125.30
                                USDT 1.00
                                SBD 3.97