Create new column in view based on calculated result from previous columns

Question

I am trying to create a view which will display an additional column based on the output of previous two columns like below

Current view:

test_id	reported_date
12345	2021-05-07
12345	2022-05-07
23456	2022-05-07

New view: I want to create a view which will have the data like below

test_id	reported_date	flag
12345	2021-05-07	Y
12345	2022-05-07	N
23456	2022-05-07	Y

A new column 'flag' will be added based on the test_id and reported date columns conditions are as followed:

1. If duplicate test_ids are present--> get the ealiest one based on reported date and mark flag as Y
2. Mark the other duplicate as N
3. If test_id is not duplicate mark flag as Y

Note: Flag column is not present in any table.

Answer 1

You could utilise a correlated exists query:

Select Test_Id, reported_date,
  case when exists (
    select * from t t2 
    where t2.Test_Id = t.Test_Id 
      and t2.reported_date < t.reported_date
  ) then 'N' else 'Y' end as Flag
from t;