SQL help – Trying to tag values which complement the same number with opposite sign

I am trying to flag the numbers in a column which has both positive and negative numbers and adds to zero if summed up. I would like to do this by partitioning one table into multiple chunks based on Account column in the table. I have posted a picture to further explain how my result (Flag column) is expected. I am novice to this, and would appreciate all the help I can get. Thanks All.

SQL help

Answer

This is rather tricky because of duplicates. The idea is to enumerate the values for each account and then use a window function for the flag:

select t.*,
       (case when count(*) over (partition by account, abs(amount), seqnum) = 2
             then 1 else 0 
        end) as flag
from (select t.*,
             row_number() over (partition by account, amount order by amount) as seqnum
      from t
     ) t;

This may look a little arcane (“where does that “2” come from?). The subquery is enumerating each amount for each account by adding a sequence number.

The outer query is then counting the number of values for the absolute value of the amount by sequence number. The number can only be 1 or 2. If it is 2, then there must be a positive and negative value (because row_number() prevents two values of the same sign from having the same sequence number).