For a day to start with, but I couldn't resist having a look around....
HiveSQL by 
For those that aren't aware SteemSQL, and now HiveSQL, are a copy of the blockchain data, stored in a SQL Server database. Witness
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.
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.
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 :)
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!
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....
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