HiveSQL: Monthly Active Users / User Registrations / Monthly Posts & Comments

EN

I wanted to see how active HiveChain has been in recent months, specifically since 2024. So here is the SQL query you can use for that:

How many users have registered per month since 2024? Or rather, how many accounts have been created, because 1 account does not necessarily equal 1 user, especially not a new user.

Registrations

  SELECT 
      FORMAT(created, ‘yyyy-MM’) AS Month,
      COUNT(*) AS Number
  FROM 
      Accounts
  WHERE 
      created >= ‘2024-01-01’
  GROUP BY 
      FORMAT(created, ‘yyyy-MM’)
  ORDER BY 
      FORMAT(created, ‘yyyy-MM’)

As a rule, fewer than 5,000 accounts were created each month, with two exceptions in December 24 and January 25. In December, four times more accounts were created than usual for that month. In January, there were twice as many registrations.

Active users

How active have Hive users been in the months since 2024? I look at how many have created at least one post or comment, how many have created at least three, and how many have created at least ten. I do not take votes into account, as these are often automated, even by users who have been inactive for a long time. Posts are more important because without them, there is nothing to vote on.

      WITH ActivityPerUserMonth AS (
    SELECT
        author AS user_id,
        YEAR([timestamp]) AS yr,
        MONTH([timestamp]) AS mth,
        COUNT(*) AS activity_count
    FROM txcomments
    WHERE [timestamp] >= ‘2024-01-01’
    GROUP BY author, YEAR([timestamp]), MONTH([timestamp])
)
SELECT
    CONCAT(yr, ‘-’, RIGHT(‘0’ + CAST(mth AS VARCHAR(2)), 2)) AS Month,
    SUM(CASE WHEN activity_count >= 1 THEN 1 ELSE 0 END) AS At_Least_1_Post_or_Comment,
    SUM(CASE WHEN activity_count >= 3 THEN 1 ELSE 0 END) AS At_Least_3_Post_or_Comment,
    SUM(CASE WHEN activity_count >= 10 THEN 1 ELSE 0 END) AS At_Least_10_Post_or_Comment
FROM ActivityPerUserMonth
GROUP BY yr, mth
ORDER BY yr, mth;

As you can see, there are only around 10k users who make at least 1 post or comment per month.
A post/comment is basically not an activity, and yet there are only around 10k.

In May and June, there was a major upward spike, but things are back to normal now.
It would be interesting to know why, whether it was bots!

Users with more than 3 posts are already around 20-30% fewer, and with more than 10 posts/comments, we are only at around 50%, with a downward trend.

Questions: Are new users not becoming active and/or are old users losing interest?

Number of posts/comments

How many posts and comments are posted per month? You can also run a simple SQL query for this.

mssql
SELECT
CONCAT(YEAR(created), ‘-’, RIGHT(‘0’ + CAST(MONTH(created) AS VARCHAR(2)), 2)) AS Month,
SUM(CASE WHEN depth = 0 THEN 1 ELSE 0 END) AS Posts,
SUM(CASE WHEN depth > 0 THEN 1 ELSE 0 END) AS Comments
FROM comments
WHERE created >= ‘2024-01-01’
GROUP BY YEAR(created), MONTH(created)
ORDER BY YEAR(created), MONTH(created);

https://img.leopedia.io/DQmS5ADwAagCDfxBMrvXSth3kDV155AhaaEMzu34n1rPVUc/grafik.png 


Unfortunately, there is no sign of an increase in activity; rather, it is declining, even though there were or will be significantly more comments last month and probably this month. Here, too, it would be necessary to examine the reasons for the increase.


Would you like more statistics? Is there anything specific you want to know? Let me know, and if I can, I'll create it.

Translated with DeepL.com (free version)
 
## DE 
Ich wollte mal schauen wie die Aktivität auf der HiveChain in den letzten Monaten war, also genau gesagt seit 2024. Daher hier mal die SQL, die man dafür nutzen kann:

Wie viele User haben sich pro Monat seit 2024 registriert? Besser gesagt wie viele Accounts wurden erstellt, denn 1 Account ist nicht zwangsläufig ein User, vor allem nicht ein neuer User.

### Registrierungen
 
```mssql
 SELECT 
     FORMAT(created, 'yyyy-MM') AS Monat,
     COUNT(*) AS Anzahl
 FROM 
     Accounts
 WHERE 
     created >= '2024-01-01'
 GROUP BY 
     FORMAT(created, 'yyyy-MM')
 ORDER BY 
     FORMAT(created, 'yyyy-MM')

In der Regel wurden monatlich unter 5000 Accounts erstellt, bis auf zwei Ausnahmen im Dezember 24 und Januar 25. Im Dezember wurden 4-mal mehr erstellt, als sonst in den Monaten. Im Januar waren es doppelt so viele Registrierungen.

Aktive User

Wie aktiv waren die HiveUser in den Monaten seit 2024? Dabei schaue ich, wie viele haben mind. 1 Beitrag erstellt oder Kommentar, wie viele mind. 3 und wie viele mind. 10 Stück. Votes berücksichtige ich nicht, da diese oft automatisiert sind, sogar von Usern, die schon lange inaktiv sind. Beiträge sind wichtiger, denn ohne die gibts auch nichts zu voten.

     WITH ActivityPerUserMonth AS (
   SELECT
       author AS user_id,
       YEAR([timestamp]) AS yr,
       MONTH([timestamp]) AS mth,
       COUNT(*) AS activity_count
   FROM txcomments
   WHERE [timestamp] >= '2024-01-01'
   GROUP BY author, YEAR([timestamp]), MONTH([timestamp])
)
SELECT
   CONCAT(yr, '-', RIGHT('0' + CAST(mth AS VARCHAR(2)), 2)) AS Monat,
   SUM(CASE WHEN activity_count >= 1 THEN 1 ELSE 0 END) AS At_Least_1_Post_or_Comment,
   SUM(CASE WHEN activity_count >= 3 THEN 1 ELSE 0 END) AS At_Least_3_Post_or_Comment,
   SUM(CASE WHEN activity_count >= 10 THEN 1 ELSE 0 END) AS At_Least_10_Post_or_Comment
FROM ActivityPerUserMonth
GROUP BY yr, mth
ORDER BY yr, mth;

Wie man sieht, gibt es lediglich um die 10k User, die mind. 1 Post oder Kommentar im Monat machen.
Ein Post/Kommentar ist ja im Grunde keine Aktivität und dennoch sind es nur um die 10k.

Im Mai und Juni gabs dabei einen größeren Ausbruch nach oben hin, aber ist inzwischen wieder normal.
Wäre interessant zu wissen wieso, ob das Bots waren!?

User mit mehr als 3 Posts sind bereits rund 20-30% weniger und bei mehr als 10 Posts/Kommentare sind wir nur noch bei ca. 50%, Tendenz sinkend.

Fragen: Werden neue User nicht aktiv und/oder verlieren alte User die Lust?

Anzahl Posts/Kommentare

Wie viele Posts und Kommentare werden im Monat gepostet? Dafür kann man auch eine Simple SQL ausführen.

           SELECT
   CONCAT(YEAR(created), '-', RIGHT('0' + CAST(MONTH(created) AS VARCHAR(2)), 2)) AS Monat,
   SUM(CASE WHEN depth = 0 THEN 1 ELSE 0 END) AS Posts,
   SUM(CASE WHEN depth > 0 THEN 1 ELSE 0 END) AS Comments
FROM comments
WHERE created >= '2024-01-01'
GROUP BY YEAR(created), MONTH(created)
ORDER BY YEAR(created), MONTH(created);

Eine Steigerung der Aktivität lässt sich leider nicht erkennen, eher sinkt diese, auch wenn es im letzten Monat und vermutlich diesen Monat deutlich mehr Kommentare gibt bzw. geben wird. Hier müsste man ebenfalls prüfen, woran die Steigerung liegt.

Möchtest du mehr Statistiken? Willst du etwas Bestimmtes wissen? Lass es mich wissen, wenn ich es kann, werde ich es erstellen.

Posted Using INLEO