Interesting post. Glad to see you enjoy playing with HiveSQL.

Here are a few tips to improve your queries performance:

• WHERE parent_author = '' -- Selecting only post not comments
  Use the depth column instead of parent_author to distinguish a post from a comment. It is indexed and processing is way faster than string comparison.
  depth = 0 -> post
  depth > 0 -> comment

• WITH (NOLOCK)
  Useless. It is managed at the server level

• CAST(timestamp AS DATE) = '2025-03-14'
  This type of CAST is useless and will be managed by implicit type conversion
  -> timestamp = '2025-03-14' works perfectly and makes your query more readable