For a day to start with, but I couldn't resist having a look around....
HiveSQL by @arcange
For those that aren't aware SteemSQL, and now HiveSQL, are a copy of the blockchain data, stored in a SQL Server database. Witness @arcange runs and manages the service, which is 4 SBB/HBD for 24 hours, or 40 SBD/HBD for a month.
Each database consists of over 60 views (a virtual table based on the result set of a SQL statement) which range from account information, content votes, witness data, rewards, transfers, and much more.
In my time I've come to know some of these views quite well, but there are also a bunch that i've not looked at all, such as TxSMTCreates and the other SMT data. Perhaps these will come into play soon ™.
Today, I'll just pull some basic stuff from tables I've used frequently (on the old chain), which may or may not be of interest to you.
The self-lovers...
select top 20 author, '', count(voter)
from txvotes where author = voter
and timestamp > getdate()-7
group by author
order by count(voter) desc
Top 20 voters of own content, past 7 days
| Author(Voter) | Vote Count | Total Weight |
|---|---|---|
| crystalliu | 285 | 2820000 |
| supergiant | 81 | 810000 |
| happydolphin | 72 | 403400 |
| likwid | 52 | 5200 |
| steemcleaners | 51 | 235500 |
| atnazo | 47 | 470000 |
| ralph-rennoldson | 47 | 247500 |
| magnapolonia | 47 | 470000 |
| ssjsasha | 44 | 440000 |
| firefly2020 | 38 | 370000 |
| dirapa | 37 | 370000 |
| ervin-lemark | 35 | 350000 |
| xels | 35 | 350000 |
| krevasilis | 34 | 260000 |
| livenow | 34 | 340000 |
| cyberdemon531 | 34 | 315200 |
| hot-women | 32 | 320000 |
| sergiomendes | 32 | 300200 |
| ace108 | 32 | 78800 |
| goldmatters | 31 | 310000 |
A 100% vote is 10000, a 1% vote is 100. Probably some piss-taking there, but i'll leave that up to you to decide who.
Top 20 incoming pay-days...
select top 20 author, sum(pending_payout_value) from comments
where depth = 0 and created > getdate()-7
group by author
order by sum(pending_payout_value) desc
Top 20 authors by pending payouts, posts only.
| Author | Pending Payout |
|---|---|
| blocktrades | 678.6750 |
| tarazkp | 462.5040 |
| theycallmedan | 316.2760 |
| themarkymark | 307.9710 |
| taskmaster4450 | 292.0330 |
| oflyhigh | 287.6380 |
| joythewanderer | 271.5470 |
| priyanarc | 270.5990 |
| good-karma | 248.8120 |
| nonameslefttouse | 242.6570 |
| coruscate | 237.3800 |
| kingscrown | 236.7330 |
| derangedvisions | 218.6130 |
| firefly2020 | 214.9120 |
| claudio83 | 214.2190 |
| peakd | 210.1490 |
| d-pend | 196.0590 |
| gtg | 186.2380 |
| holger80 | 185.8240 |
| anggreklestari | 184.6440 |
Well done (4?) ladies and gentlemen. Many HIVE coming your way from week one :)
Top 20 communities by subscribers
select top 20 community, '|', count(subscriber)
from CommunitiesSubscribers
group by community
order by count(subscriber) desc
| Community | Subscribers |
|---|---|
| hive-196037 | 3109 |
| hive-174578 | 2379 |
| hive-131812 | 1806 |
| hive-100421 | 1634 |
| hive-167922 | 1160 |
| hive-119845 | 1135 |
| hive-148441 | 1129 |
| hive-114105 | 1013 |
| hive-140217 | 868 |
| hive-175001 | 830 |
| hive-122108 | 822 |
| hive-177682 | 796 |
| hive-193552 | 781 |
| hive-120078 | 767 |
| hive-161155 | 753 |
| hive-175254 | 721 |
| hive-184437 | 699 |
| hive-102880 | 651 |
| hive-156509 | 593 |
| hive-133872 | 566 |
This might well be detailed somewhere on PeakD, and if so, with friendlier names!
Top 20 accounts handing out upvotes
select top 20 voter,'|', count(distinct author) from txvotes
where timestamp > getdate()-7
and weight > 0
group by voter
order by count(author) desc
| Account | Unique accounts upvoted |
|---|---|
| innerhive | 3069 |
| techken | 1850 |
| payroll | 1782 |
| pixelfan | 838 |
| steembasicincome | 831 |
| nobyeni | 788 |
| bert0 | 756 |
| bewithbreath | 733 |
| arcange | 708 |
| raphaelle | 707 |
| sbi2 | 693 |
| memeteca | 679 |
| tresor | 679 |
| jlsplatts | 652 |
| mys | 632 |
| tombstone | 603 |
| majes.tytyty | 601 |
| tshering-tamang | 590 |
| knaveen | 585 |
| sbi3 | 568 |
And last one....
Top 20 accounts dishing out downvotes
select top 20 voter,'|', count(distinct author) from txvotes
where timestamp > getdate()-7
and weight < 0
group by voter
order by count(distinct author) desc
| Voter | Unique author count |
|---|---|
| camillesteemer | 1202 |
| theycallmedan | 507 |
| spaminator | 318 |
| bordulez | 239 |
| steemcleaner | 181 |
| certhas | 152 |
| jorjis | 148 |
| fierolma | 147 |
| drujik | 143 |
| irsaz | 142 |
| graedu | 141 |
| pigmlit | 135 |
| hurjem | 135 |
| bristac | 131 |
| frimold | 129 |
| hujipy | 129 |
| usial | 129 |
| wistan | 128 |
| morduk | 120 |
| jemics | 116 |
I make that 3 with a plan, and 17 without :)
Just a note on the Engagement League. I'm still undecided if I'll have another week off, but I think it'll be back sooner rather than later.....
Does anyone want any data pulling specific to their account?
Cheers
Asher