Login
Signup
Write a post
Switch light

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

80
7 years ago
in#utopian-io

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: @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
utopian-ioanalysiswitnesswitness-categoryblockchainbi
7 years ago
abh1234580
Posted from spendHBD
$ 191.48
173
H2
H3
H4
Upload from PC
Video gallery
3 columns
2 columns
1 column
37 Comments