How to get the row number when using alias in orderby
The error is (from the column used inside the OVER) in SELECT. You can't use a column alias defined in the select list for another column expression in the same select list:

SELECT
      -- 
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY total_comments) AS rank    
                               -- ^^ this is throwing the error


You just have to duplicate the expression inside the OVER (ORDER BY ...):

SELECT
      -- 
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY product_positive + product_negative) AS rank
---
ORDER BY 
      total_comments DESC NULLS LAST ;       -- you can keep it here




Irrelevant to the issue but this condition will stop the optimizer from using any indexes on (created_at):

WHERE DATE(c.created_at) = DATE 'yesterday'


I'd write it like this, so indexes can be used more effectively:

WHERE c.created_at &gt;= DATE 'yesterday'
  AND c.created_at  &lt; DATE 'today'