RE: HiveSQL: Author Rewards & Curation Rewards

[geekgirl]

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.

Comments

[arcange]

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

[geekgirl]

Thank you Arcange! This is super helpful.

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

[topbooster]

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.

[arcange]

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

[topbooster]

Thank you and @geekgirl for the help.

[topbooster]

Thank you @geekgirl, your work is valuable