@arcange proposal for HiveSQL got funded so it is now free to use HiveSQL. I know it is mostly intended for apps and developers but i have a weird love and hate relationship with numbers and correlation of them so i had to try it out. You can check the @arcange post by clicking on HiveSQL is free again - Thank you for your support!
How hard is it to use. I have no idea, so here is some background of my knowledge. Some 20 years ago i knew how to make a simple dBase database. Really, really simple. And yes we were learning dBase in 2002 in computer class at economic school. In the state contest of economic high schools from 40 people i was the only one using dBase, everyone else was using access. I attended because no one wanted and i got 7 days free from school to prepare for it :D and finished 6th, 4 points short to be 3.
I digress, so i have same vague idea how things work. First i had no idea what program to use to access the HiveSQL. After some clicking ended up with downloading Microsoft SQL Server management studio. After some time of clicking around with no idea of what am i doing i was sure that there is an easier way to filter data from the hive data base. Found Azure data studio. Think i installed it with Microsoft SQL Server, and it looks like something that is intended for what i wanted to do so... I am sure there are better programs out there, so if you know some and want to share feel free, would be appreciated.
I do understand that you use queries to extract data you want but i underestimated my lack of knowledge about how the base is structured, which table contains what data ( for the people that are professionals in this, i apologize for all wrong terms used ). So it was an try and error game.
So here are some random stats:
First thing i managed to find in my pokes in the dark were accounts. There you can see current info of every account. While looking through the columns one of the interesting things was Recovery account.
How many accounts that are active in the last 30 days (used the last vote time to filter the accounts, so all accounts that used the vote button in last 30 days) still have Steem as their recovery account?
- From 15783 accounts 7385 have steem as their recovery account. 46.79%
Next thing i wanted to check out, are those less experienced users, or is it just laziness? i knew the answer but wanted to confirm :)
- From first 100 users with most Hive Power, 49 of them still have Steem as recovery account. 49%
That reminded me, i need to do something :D
Some more randomness:
- 1267 accounts have more than 10.000 hp
3426 accounts were created in the last 30 days, 372 of them used upvote in last 10 days. So 10.86% are active on the social part of Hive. People that only play games on hive don't use upvote so they could be active but are not in those 10.86%
of those 3426 accounts, 603 used the upvote button, so they tried the social part of Hive. And in the last 3 days 214 of them were active. If that means 35.49% retention, that is not that bad.
23 of those 3426 accounts created in last 30 days have more than 100 hp staked. (not really sure what to think of this :) )
And here is one personal, because this was only one that i managed to make some kind of graph of it, and i need a photo for the post :D
HP accumulation from posts and comments (i think curation rewards are also there, not sure) for my account from creation till now.
There must be a way to add power up's in there, but i have no idea where to look for power up data :D
If you have some interesting queries code to share, feel free. It would help me to understand how all this works, and how is it structured. It would help me learn. Not sure why i need to learn it, but i do have a good amount of average skills that i don't need and will probably never use, so why not add another one.
Photography Basics: Learn Manual Mode (tutorial, Part 1)
Photography Basics: Learn Manual Mode, Shutter speed (tutorial, Part 2)
Photography Basics: Learn Manual Mode, Aperture and ISO (tutorial, Part 3)
Photography Basics: Learn Manual Mode - Thinking process (Tutorial Part 4)
Posted Using LeoFinance Beta
Just changed my recovery account yesterday. I kept on getting notifications from peaked and said what the heck... who knows what happens in the future.
Posted Using LeoFinance Beta
i am just in the process of doing that. i know that is a problem for a long time now but, laziness.
Justin Sun no like you any more.
Posted Using LeoFinance Beta
we had some trust issues :D
Posted Using LeoFinance Beta
I know, feels so sad...😥
Posted Using LeoFinance Beta
Posted Using LeoFinance Beta
He looks happy though.
Posted Using LeoFinance Beta
Nah, he has gas.
Posted Using LeoFinance Beta
He usually farts through his ears...
Posted Using LeoFinance Beta
Nice (random) stats. 😉
For the first time playing with HiveSQL, this is quite a good work.
thanks.
it is interesting. some pointers to program to use or simple queries would be appreciated :)
My main tool is SSMS (SQL Server Management Studio).
Another really good and free tool is HeidiSQL. The great advantage of this one is that it is compatible with multiple database systems.
And last but not least, you can simply use Microsoft Excel. Here is a link to a quick tutorial I created a long time ago. While a bit outdated (server and user credentials are no longer valid), it will provide you with a good start.
BTW, I see I miss your witness vote 😢
Would you mind casting one to me? It would be much appreciated!
Thanks.
I need to review my witness votes, not that my 10k will make much difference in the top witnesses.
Every vote count. Can't wait to get yours 😉
It's interesting to see these numbers and your choice as well. I'm in those numbers myself with a couple of accounts :)
Anyway, good shout-out for users to change their recovery account.
Posted Using LeoFinance Beta
some people don't know and some are lazy and it is all good until something happens. i did it today, so i am in the lazy part of the group :D
the choices were limited by my learning curve. as it is kinda first time using it. My biggest problem is finding out where is which data located. and mixing results from different tables (think they are called tables :) ) is still out of my knowledge.
No worries, there's plenty of time to play with it and learn. I'm glad it's free now as more people with the required knowledge can use it.
Posted Using LeoFinance Beta
Interesting stats here. Could you grab the data of how many Hive accounts share posts to Twitter? 😎
there is maybe a way to check @poshbot in the last x days, as it posts comments to the blockchain. not sure how would i do it, and how accurate will it be, but it would be a fun waste of time :D will get back to you if i find something :D
So in the last 30 days @poshbot made 13.697 comments. and from my understanding it should detect all links related to hive (maybe not from all frontends, not really sure). so there could be maybe 13.697 links of hive content shared on twitter in the last 30 days.
965 Unique authors in the last 7 days
1,793 Unique authors in the last 30 days
2,772 Unique authors lifetime
that sure, but sounds not that bad, if you think of how many active users hive has.
There are like 4000-5000 tweets a week last time I checked. I'd have to unique the authors to see how many unique authors.
so my number of 13.697 in a month could be close.
thanks
If that is a comma then yeah that sounds close to the monthly total but I can figure it out when I am on pc and figure out how many unique authors.
the joy of different parts of the world. in our part from forever we used dot for separation and comma for decimals. now translating it to english, we even say Zarez (comma) something, while in english you would say point.
Unique authors is over my pity knowledge of SQL :D
I can’t stand that some use . And some use , It is so damn confusing.
Thank you for this research. 😎 So, it's on average about 457 links per day. Is this good or bad?
No idea :D it will be interesting to see how many accounts are sharing it when Marky does his thing with data.
Agree. That could be useful and relevant.
I am not sure on this. It says the same thing twice. Did you mean that one of these GOT upvotes and the other used the upvote button?
Either way, 10% retention is actually pretty good. If we look at the number of accounts on a YouTube versus how many actually place a vote, comment, or upload a video, I bet it is less than 10%.
Honestly, I dont think our rates are too bad on Hive. The challenge is that we are not make a lot of progress with 3,400 new accounts in a month. Need to 10x that number to see some impact.
Posted Using LeoFinance Beta
I know dapps and front ends are claiming accounts. Is that included in account creation? I guess not, right?
Posted Using LeoFinance Beta
from what i managed to see, there is a claiming data, as claiming is not creating.
thing i could do, and probably would know how :D is see how many were created for splinterlands from that number.
Check for every dapp, would be interesting 🙂
Posted Using LeoFinance Beta
i am not sure where and if i can find from which app was the account created, or which acc was used. one that i managed to find is who is set up as recovery account, and i thing new users are not changing that :) so this is really not exact stat and probably is not showing anything :D
i need to fix that (try to make it more understandable)
so from 3426 that were created from 14 October till 14 November 372 accounts used the upvote function in last 10 days.
and from that 3426 accounts created in last 30 days, 603 used the upvote function at least 1 time from the time created. So you could assume that they visited some of the fronends and clicked the upvote.
214 is the number that used the upvote function last 3 days (from those registered in last 30 days)
wow i overcomplicated that :D
and some numbers from that 3426 were most probably created by a game so they never got to social part of it. they could be active i just did not found the good way to include them (lack of SQL knowledge)
Great stats, it is evidente that People are comment more and the interaction are grow up.
Posted Using LeoFinance Beta
leo is doing good with comments.
i find retention rate, if it is only 20% of those that registered and tried to use social part of hive, pretty good.