Hive Trends - What is Popular on the Hive Blockchain?
I have been working on some reports using the amazingly wonderful HiveSQL service.
For my own purposes, it works great, but I am not quite ready for you all to hammer my pathetically weak server. Even running one of these reports takes a long time on my virtual machine, you might find things perform better.
Anyway, enough of my nerd moaning - already I have some interesting information out.
First I grabbed the top 20 tags by how often they were used over the last 30 days.
Strangely, this is the first I heard of Neoxian. I guess it is some kind of community. I thought it was a game (due to the references to Neoxian city) but seems more a community around a witness? No idea - there are many people using the tag with no connection to a dominant topic.
Next is Proof of Brain, which again doesn't seem to be about any kind of topic other than people are tagging for attention on anything and everything.
Further down are tags like Spanish and Photography, which is much more comfortable ground and those are self-explanatory.
I have to admit I expected Leofinance to be a lot higher, but here is a place where how people use Hive and how Hive is set up come into conflict. When you post to a community you don't necessarily use that community tag. When you do, it looks like the one down at the bottom
Hive-193552 and not something human-readable.
Turns out that is Actifit, but use of the Actifit tag spelled out ranks way lower than people posting to the community.
Trends over Time
This is the part that really takes a long time because it needs to aggregate data 30 times.
The way I do this is probably inefficient for two reasons, first the way I formulate the SQL query and secondly, I am recalculating every time data for a tag is requested.
Ideally I will start to collect data daily and then the rollup will be much, much simpler.
Here is the trend for Python, which I pictured to be an evergreen topic. I also thought it would be much more popular than it turns out to be.
Compare it to the CTP community tag which ranges between 550 and 677 posts using that tag per day!
First, get the most popular tags using the code below.
SELECT top(20) LOWER(Tag) as Tag, count(tag) as Total FROM Tags INNER JOIN Comments ON Tags.comment_id = Comments.ID WHERE DATEDIFF(day,comments.created,GETDATE()) between 0 and 30 group by tag order by total desc;
This will get the Top 20 tags used over the last 30 days.
To change how many are returned, change where it says
top(20), just keep in mind the more you request the higher the delay or chance of timeout there will be.
The time period is set by the
DATEDIFF which is the difference in days between the record date and today. In my example above we wanted between 0 and 30 but of course you could set that to 365 to get the full past year, and so on.
If you want just a single tag, add to the
(You will find the tag names are formatted in all kinds of funky ways which seemed to cause odd results, so using LOWER solves that quirk but adds extra processing time, which is a real pain)
To get the trend over time I currently run the same query over and over again. As I said before, it would make much more sense to store the values every day, but I would need to go back and generate figures for past data also which is a task for another time.
SELECT count(tag) as Total FROM Tags INNER JOIN Comments ON Tags.comment_id = Comments.ID WHERE DATEDIFF(day,comments.created,GETDATE()) between 29 and 30 AND Tag = 'ctp' group by tag order by total desc;