How to use ROW_NUMBER() PARTITION BY()?

How would I implement ROW_NUMBER() PARTITION BY() for this dataset to get these results? I just wanted to see the current programs a person is currently enrolled in as of today.

Code:

SELECT
    PERSON_ID,
    PROGRAM_JOINED,
    EFFECTIVE DATE,
    PROGRAM_FINISHED_FLAG
    
FROM PROGRAM_DATE_TRACK

PROGRAM_DATE_TRACK

PERSON_ID	PROGRAM_JOINED	EFFECTIVE_DATE	PROGRAM_FINISHED_FLAG
1	Ballet	8/1/2023	N
1	Painting	8/1/2023	N
1	Ceramics	5/30/2023	Y
2	Figure Drawing	8/1/2023	N
2	Ballet	5/30/2023	Y
2	Ballet	8/1/2022	N
3	Tap	8/1/2023	N
3	Knitting	1/1/2021	Y

What I need to see:

PERSON_ID	PROGRAM_JOINED	EFFECTIVE_DATE	PROGRAM_FINISHED_FLAG
1	Ballet	8/1/2023	N
1	Painting	8/1/2023	N
2	Figure Drawing	8/1/2023	N
3	Tap	8/1/2023	N

I have tried this, but it's including program dates that have ended. Example, Person 2 started ballet on 8/1/2022 but ended on 5/30/2023.

SELECT
    PERSON_ID,
    PROGRAM_JOINED,
    EFFECTIVE DATE,
    PROGRAM_FINISHED_FLAG
    ROW_NUMBER() OVER (PARTITION BY PERSON_ID, PROGRAM_JOINED  ORDER BY EFFECTIVE DATEE DESC) AS RANK
FROM PROGRAM_DATE_TRACK
WHERE PROGRAM_FINISHED_FLAG = 'N'

Results:

PERSON_ID	PROGRAM_JOINED	EFFECTIVE_DATE	PROGRAM_FINISHED_FLAG	RANK
1	Ballet	8/1/2023	N	1
1	Painting	8/1/2023	N	1
2	Figure Drawing	8/1/2023	N	1
2	Ballet	8/1/2022	N	1
3	Tap	8/1/2023	N	1

Solution

You can do

select *
from (
  select t.*,
    row_number() over(partition by person_id, program_joined
                      order by effective_date desc) as rn
  from PROGRAM_DATE_TRACK t
) x
where rn = 1 and program_finished_flag = 'N'

Result:

PERSON_ID  PROGRAM_JOINED  EFFECTIVE_DATE  PROGRAM_FINISHED_FLAG  RN 
---------- --------------- --------------- ---------------------- -- 
 1         Ballet          2023-08-01      N                      1  
 1         Painting        2023-08-01      N                      1  
 2         Figure Drawing  2023-08-01      N                      1  
 3         Tap             2023-08-01      N                      1

See running example at db<>fiddle.