With a full time job and other new hobbies taking up much of my time these days, I'm a long way from the 10 or so hours I used to spend on Steem each day. Back then I rarely missed a post of note and felt I had much more awareness of what was going on, at least on the surface.
Today though, I received a comment from (Witness) @arcange asking politely if I could review his proposal to....
Make HiveSQL Free Again!!!!!!!!!!
How did I miss this one!? It is, for me (but perhaps not for you), a hugely beneficial proposal (to the tune of 40 HBD a month), and I have obviously cast my vote on it today.
And it's so close to being funded :)
As @arcange writes in the proposal, SteemSQL started out free and with a little background knowledge in SQL (although @arcange would say differently and frequently lambastes me for writing inefficient queries), it was a great opportunity to dig into the blockchain data to see if I could find anything worth presenting.
I would guess that over 200 of my 937 (now 938) posts have used this tool, and many of them have proven quite popular over the years, even if what they present is just for shits n giggles.
And of course, the Engagement League uses HiveSQL.
For over 3 years now, this weekly post has been produced each Sunday (bar 2/3) and thanks to those taking part, is often the most commented post of the day.
When @arcange did start charging, the cost was low and as the post received good support I soaked up the cost, and the prizes for a while. In 2018-19, I was lucky to get funding from @curie for the prizes and the post continued receiving good support - it was worth my time and effort for sure to pay the 10 SBD (2018) and the 20 SBD (2019).
These past twelve months, the cost has been 40 HBD for 30 days, which was was a reasonable hike up from 10 and 20 SBD. Again though, I have been fortunate with support on the post and with prize sponsorship funds from @azircon, @justineh, and @innerhive.
This sponsorship though is unlikely to continue forever (with 2 of the above seemingly not around much any more) and the funds they donated will run out in 11 weeks time. Paying the 40 HBD a month as well as 200 odd HIVE each month in prizes has been a worry of mine. I would like to continue the league and would (if the proposal didn't appear) continue paying the subscription, but I think the prizes each week would be much lower, or 0.
If @HiveSQL was to become a free service again and post support continued, I would likely be able to fund the weekly prize pool myself.
And so, if you are a part of the Engagement League, or happen to think that it has encouraged engagement and boosted retention during this long winter, both I and @arcange would appreciate a vote on the proposal.
https://peakd.com/me/proposals/138
https://wallet.hive.blog/proposals
If this proposal is funded, the door is open for those of you who do fancy picking up a little SQL and having a look at the blockchain data. It's honestly quite easy to get started!
All you need is a tool to connect to the database: https://www.linqpad.net/Download.aspx
A look around the tables (or views), to figure out what information is stored where, and then a little googling. There are a load of SQL resources online, and if you add hive.blog or peakd (or even abh12345) to the search, you'll find some queries related to the database structure to get you started.
Want to know how many (top level) posts someone has written?
SELECT COUNT(*) FROM COMMENTS WHERE AUTHOR = 'abh12345' AND DEPTH = 0
Or comments? (any post which isn't at top level)
SELECT COUNT(*) FROM COMMENTS WHERE AUTHOR = 'abh12345' AND DEPTH <> 0
Or how much you've sent someone?
select amount_symbol,
sum(amount)
from txtransfers where [from] = 'abh12345' and ([to] = 'steemsql' or [to] = 'hivesql')
group by amount_symbol
Or where your votes have been going?
select author, count(*), sum(weight)/10000 -- /10000 will give equivalent of 100% votes
from txvotes
where voter = 'abh12345'
and timestamp > '03/21/2020 15:00:00' -- Hive blockchain arrived!
group by author
order by sum(weight) desc
There is a lot to play with, and @arcange has always been helpful to me when I've got stuck with something. And, I'm sure there are other people knocking around who could help in the Hive chain Discord: https://discord.com/invite/kyEFDfT
I would love to see the EL crew push this one over the line :)
https://peakd.com/me/proposals/138
https://wallet.hive.blog/proposals
Cheers
Asher