Set flag in new column based on the max(version) of the record

Question

I have a table where it has multiple columns but need to be flagged as Y when the version is max compared to other record. For example a simple table with two rows below . I want to add another column to flag Y when the version is highest for the user_id , week_id and start_time combination or set to N where version is not highest . The activity time or extract id should not be included for the grouping.

USER	WEEK_ID	START_TIME	ACTIVITY_TIME	EXTRACT_ID	VERSION
User 1	2023-10-10	2023-10-12T00:00:00	2023-10-12T10:00:00	A	5
USer 1	2023-10-10	2023-10-12T00:00:00	2023-10-12T09:00:00	B	2

Let me know how this can be achieved.

Need solution for flagging only max version records.

Output expected is

USER	WEEK_ID	START_TIME	ACTIVITY_TIME	EXTRACT_ID	VERSION	FLAG
User 1	2023-10-10	2023-10-12T00:00:00	2023-10-12T10:00:00	A	5	Y
USer 1	2023-10-10	2023-10-12T00:00:00	2023-10-12T09:00:00	B	2	N

Answer 1

Window functions are perfect for this:

select *
  , iff(
      version = max(version) over(partition by user, week_id, start_time)
      , 'Y'
      , 'N'
  ) as flag
from table