Hive Trends - What is Popular on the Hive Blockchain?

avatar

hive trends.png

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.

Screen Shot 2022-03-14 at 3.35.27 PM.png

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.

Screen Shot 2022-03-14 at 4.03.18 PM.png

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.

Screen Shot 2022-03-14 at 3.33.10 PM.png

Compare it to the CTP community tag which ranges between 550 and 677 posts using that tag per day!

Screen Shot 2022-03-14 at 3.22.24 PM.png

SQL Queries

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 WHERE with AND LOWER(Tags.tag)='tagname'

(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;


3 comments
avatar

The people doing V2K with remote neural monitoring want me to believe this lady @battleaxe is an operator. She is involved deeply with her group and @fyrstikken . Her discord is Battleaxe#1003. I cant prove she is the one directly doing the V2K and RNM. Doing it requires more than one person at the least. It cant be done alone. She cant prove she is not one of the ones doing it. I was drugged in my home covertly, it ended badly. They have tried to kill me and are still trying to kill me. I bet nobody does anything at all. Ask @battleaxe to prove it. I bet she wont. They want me to believe the V2K and RNM in me is being broadcast from her location. And what the fuck is "HOMELAND SECURITY" doing about this shit? I think stumbling over their own dicks maybe? Just like they did and are doing with the Havana Syndrome.

They are reckless and should have shown the proper media what they had before taking me hostage for 5 years.

What would you say while having a gun pointed at your head from an undisclosed location? Have people find it? My hands are tied while they play like children with a gun to my head. Its a terrorist act on American soil while some yawn and say its not real or Im a mental case. Many know its real. This is an ignored detrimental to humanity domestic threat. Ask informed soldiers in the American military what their oath is and tell them about the day you asked me why. Nobody has I guess. Maybe someone told ill informed soldiers they cant protect America from military leaders in control with ill intent. How do we protect locked up soldiers from telling the truth? https://ecency.com/fyrstikken/@fairandbalanced/i-am-the-only-motherfucker-on-the-internet-pointing-to-a-direct-source-for-voice-to-skull-electronic-terrorism

avatar

Strangely, this is the first I heard of Neoxian.

I see it in my wallet every now and then but never figured out what the community is all about. Even after 7 months on Hive i can't keep up with everything that is going on ffs

Posted Using LeoFinance Beta

0
0
0.000
avatar

A more accurate way to look at it might be to look only at the first tag. A lot of the general tags (proof of brain, etc.) are thrown in as later tags to collect tokens but they don't really provide much insight as to what topics are popular. I don't know if it would be better to ignore the community tags or not. Some are general in nature but others are more specific to a particular topic.

0
0
0.000