You are viewing a single comment's thread from:
RE: Trending Page & Bid Bots - Analysis of Top 100 Posts For The Month of May, 2018
a more efficient SQL query would be directly using created >= '2018/06/01'instead of CONVERT.
How many posts were upvoted by bid bots that appeared in the trending section? (Top 100 Posts in terms of author rewards were taken from the data)
Great effort, but it would be better if you written a script instead of checking it manually.
In case u need list of bid bots, here's steemhunt repository: https://github.com/Steemhunt/api/blob/5dd78186c115c73ceebb27dfaf496304d4c9a143/db/bid_bot_ids.json
I actually tried to get the posts writing this query but it didn't give me the accurate results.
SELECTDISTINCT.comments.author,
COUNT(comments.permlink) AS [LINK],
SUM(comments.total_payout_value) AS [REWARDS],
COUNT(txvotes.voter) AS [BOT UPVOTES]
FROM comments
JOIN txvotes ON comments.id = txvotes.id
WHERE txvotes.voter IN ('booster', 'upme', 'upmewhale', 'boomerang', 'pushup', 'rocky1', 'postpromoter', 'mercurybot', 'sneaky-ninja', 'promobot', 'brupvoter', 'smartsteem', 'appreciator', 'therising', 'emperorofnaps', 'oceanwhale', 'inciter', 'buildawhale', 'estabond', 'jerrybanfield', 'tipu', 'speedvoter', 'bumper', 'lays', 'treeplanter', 'thundercurator', 'drotto', 'flagship', 'siditech', 'promotedpost', 'byresteem', 'moonbot', 'minnowpond', 'postdoctor', 'steemvote', 'withsmn', 'hottopic', 'echowhale', 'okankarol', 'danzy', 'hugewhale', 'upvotewhale', 'earthnation-bot', 'morwhale', 'coolbot', 'big-whale', 'friends-bot', 'steemlike', 'astrobot', 'microbot', 'queqtra', 'tisko', 'bidseption', 'talhadogan', 'postresteem', 'refresh', 'steemthat', 'blockgators', 'ottoman', 'cleansingpoetry', 'resteemyou', 'bestvote', 'resteemr', 'zerotoherobot', 'schoolofminnows', 'superbot', 'crystalhuman', 'growingpower', 'alphaprime', 'earnmoresteem', 'giftbox', 'highvote', 'jeryalex', 'fresteem', 'otobot', 'oceansbot', 'red-rose', 'botcoin', 'drewardsresteem', 'bidbot', 'gangvote', 'thehumanbot', 'minnowbooster')
AND created >= CONVERT(DATE,'2018-05-01') and created < CONVERT(DATE,'2018-06-01')
AND total_payout_value > 100
GROUP BY comments.author
Not sure what I am doing wrong here, may be you can guide me.
I think is the issue of GROUP BY. Not too sure, maybe when I had time I might take a look
Hey @superoo7
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!