RE: HiveSQL: Author Rewards & Curation Rewards

avatar

You are viewing a single comment's thread:

I can't separate post votes and comment votes. The columns of the table txvotes in hive-sql are arranged this way.

ID,tx_id,voter,author,permlink,weight,timestamp

Can you tell me how to separate post votes and comment votes? Do you think that is not possible?

0
0
0.000
7 comments
avatar

You may have to combine TxVotes table with Comments table using JOIN. Here is an example.

    SELECT v.author, v.permlink, c.title, v.timestamp
    FROM Comments c
    JOIN TxVotes v
    ON c.permlink = v.permlink
    WHERE v.voter = 'geekgirl'
    AND v.author = c.author
    AND c.parent_author = ''
    AND v.timestamp BETWEEN '2022-02-01' AND '2022-03-01'
    ORDER BY v.timestamp ASC

If parent_author is empty or nothing, it would mean it is a post, otherwise it is a comment.

0
0
0.000
Reply
avatar
(Edited)

You should not use parent_author='' to differentiate posts from comments but depth=0!
The depth column has its own index and numeric value comparisons are way faster than string comparisons, especially on a big number of rows.

It's also good practice to explicitly specify which JOIN type you use and to have all join conditions in the JOIN clause rather than splitting them into the WHERE clause.

Finally, you are making your JOIN in the wrong order. The main table should be TxVotes (table in which you are looking for info) and Comments should come next as it's a table you use to filter and get alternate data.

SELECT 
    TxVotes.author,
    TxVotes.permlink, 
    Comments.title,
    TxVotes.timestamp
FROM 
    TxVotes
    INNER JOIN Comments ON 
        Comments.Author = TxVotes.Author
        AND Comments.permlink = TxVotes.permlink
WHERE 
    TxVotes.voter = 'geekgirl'
    AND Comments.depth = 0
    AND TxVotes.timestamp BETWEEN '2022-02-01' AND '2022-03-01'
ORDER BY 
    TxVotes.timestamp ASC
0
0
0.000
Reply
avatar

Thank you Arcange! This is super helpful.

@topbooster, see above comment by Arcange for a correct answer to your question.

0
0
0.000
Reply
avatar

This is great @arcange , I would be more benefited if you elaborate on what the depth column indicates and how to separate the only comment votes data.

0
0
0.000
Reply
avatar

depth is the depth level in the comments tree, where depth=0 indicates a root post and depth > 0 are comments

0
0
0.000
Reply