HiveSQL - Querying Json_metadata with App = "Ecency"

Hey All,

First a disclaimer here - I am learning to mine data via the HiveSQL so there could be chances that my queries may be wrong or not very optimized like an experience developer. I am giving it a try to mine data and extract some useful information from it. This time I have tried to extract data that is tied to the - "Ecency App Usage". One of my friend here on the HIVE chain @untilwelearn requested me if I could get him active users of the "Ecency App". First I was wondering how could I get him this information and I said will try to see what I can get but no promises. So this post is to answer his question and at the same time exploring my journey with HiveSQL. Please don't consider the results of the query as final there could be more better ways of extracting the data which I am may be not aware of due to my little knowledge here as I am still exploring. But the logic that I have used to get here is the best based on my understand and the tables information available on HiveSQL. This time around I have explored mainly the table - "TxComments" - Blockchain operations - comment

Here is the Table - TxComments Information.

source:

Query for extracting records tied to Posts (Original Content)

Here is the query that I triggered to get me authors, using the ecency App to post. From the "json_metadata" column I am looking for "app:ecency" to different if from other apps that may be used for posting like - peadk, inleo, hive.blog, waivio and other platforms.


SELECT  
    author,  
    'post' AS action_type,  
    CONCAT('https://ecency.com/@', author, '/', permlink) AS post_link  
FROM TxComments WITH (NOLOCK)   -- preventing blocking
WHERE parent_author = ''  -- Selecting only post not comments
AND json_metadata LIKE '%"app":"ecency/%'  
AND CAST(timestamp AS DATE) = '2025-03-14'  
GROUP BY author, permlink  
ORDER BY author;

Output of the Query

#	Author	Action Type	Post Link
1	abeerhunter	post	Nelly Maui - Badlands Brewing
2	abelarte	post	Descubriendo el verdadero concepto de
3	aburihan1	post	A Beautiful Display of Fresh
4	adaezeinchrist	post	The Beginning of a New
5	adrianh1	post	Sky C38c4cbf3afe2
6	adrirxd	post	La Depresion y Autolesiones
7	aetaitae	post	Don't Tnod
8	aiuna	post	Three Winners - Holozing Contest
9	akdx	post	Happy Holi to Everyone
10	alberto0607	post	Vprbcqpedf
...	... Getting Top 10 and Last 10 rows data...	...	...
531	yundie8710	post	History Collector
532	yundie8710	post	Welcome to Villa
533	zakludick	post	Teamwork: Hive Community
534	zakludick	post	Warhammer 40K Review
535	zhula	post	Genus Anoplocnemis
536	zuliquijada22	post	Breakfast Outside
537	zuliquijada22	post	Ladies of Hive Contest

A total of 537 posts were made using the Ecency App and now we need to check on the comments as well since there would be folks who are interacting with the App for commenting too. Lets see with what query I came up with.

Query for extracting records that is tied to Comments


WITH UniqueUserComments AS (
    SELECT  
        author,  
        'comment' AS action_type,  
        CONCAT('https://ecency.com/@', parent_author, '/', parent_permlink, '#', author) AS comment_link,  
        ROW_NUMBER() OVER (PARTITION BY author ORDER BY timestamp) AS rn  -- # symbol for pointing to a specific comment in the post 
    FROM TxComments WITH (NOLOCK)  
    WHERE parent_author <> ''  --  getting only comments
    AND json_metadata LIKE '%"app":"ecency/%'  
    AND CAST(timestamp AS DATE) = '2025-03-14'  
)  
SELECT author, action_type, comment_link  
FROM UniqueUserComments  
WHERE rn = 1  
ORDER BY author;

Output of the Query...

S.No	Author	Action Type	Comment Link
1	a07	comment	Link
2	abdul-qudus	comment	Link
3	abelarte	comment	Link
4	abelfotografia	comment	Link
5	abenad	comment	Link
6	abu78	comment	Link
7	aburihan1	comment	Link
8	acgalarza	comment	Link
9	ach67	comment	Link
10	adaezeinchrist	comment	Link

S.No	Author	Action Type	Comment Link
701	yuslenismolina	comment	Link
702	yzamazing	comment	Link
703	zain-ab001	comment	Link
704	zakludick	comment	Link
705	zhangyan-123	comment	Link
706	zo3d	comment	Link
707	zubygeee	comment	Link
708	zuliquijada22	comment	Link

I am considering only one comment from the user only here. Since we are more interested in getting active users so there is no point counting comments made by a user multiple times. Ideally post should also have the same structure but for now I have made no selection criteria there since its a limited number no one is going to write two or three posts in a day. Now that we have total comment numbers lets add then up to get total active users on Ecency App for 14th March, 2025

Active users on Ecency on 14 March 2025 = Users who posted [537] + Users who commented [708] = 1245

Yes, I am aware that there could be further elimination that can do done by removing folks from the comments who have already appeared as Author. So not making it further complex and based on the above two queries results on post and comments I can say that the active users using ecency App of 14th March, 2025 was around 1245 give and take +- 50. So at least we get some idea here on the active user level engagement using the Ecency App. Let me know your views and if you have any other approach in getting to this numbers. In my next post I will perfrom a similar execrise and see what I get for other portals [peakd, inleo, hive.io and others].. @untilwelearn bhai check out the numbers which you were requesting..hope this helps to an extent... Happy Learning... cheers

HiveSQL - Querying Json_metadata with App = "Ecency" - Figuring Out Active Users Number by Post & Comment Numbers....

#hivesql #DBeaver #learning #hivestatistics #data #datamining #hiveaccount #liquidhive #DynamicGlobalProperties #ecency #TxComments

Refer my previous posts around querying HiveSql::

image source:: hive.io, hive x, canva

Best Regards
Paras