SQL : Finding duplicates and flag in separate column with Case

Question

I am trying to find duplicates in the table and wanted to add a new flag column. Sample data below :

Column A        Column B
1                888
1                999
1                777
2                444
2                444
3                555
4                222

Desired output :

Column A        Column B         Flag_output
1                888              DUPLICATE
1                999              DUPLICATE
1                777              DUPLICATE
2                444              NULL
2                444              NULL
3                555              NULL
4                222              NULL

case 1: When Column A has the same values with different values in Column B (e.g. value 1 in Column A) - should be marked as DUPLICATE

case 2: When Column A has the same values with the same values in Column B in multiple rows(e.g. value 2 in column A) - should be marked as NULL

case 3: When Column A and Column B has unique values (e.g. value 3 and 4 in Column A) - Also should be marked as NULL

Any help would be appreciated.

Answer 1

You can use window functions:

select t.*,
       (case when min(b) over (partition by a) <> max(b) over (partition by a)
             then 'DUPLICATE'
        end) as flag_output
from t;