SQL & PL/SQL

Hi,

I have a logical problem that I am trying to solve using analytical function.

Given a table with student name, score date and score - I am trying to find their last score (sorted by score date) in the previous month.

for example, I want to find the last_month_marks column value

Sample query:

with data_set (name, score_date, marks) as
(
select 'SAM', date '2025-1-10', 10 from dual union all
select 'SAM', date '2025-1-16', 100 from dual union all
select 'SAM', date '2025-2-10', 1 from dual union all
select 'SAM', date '2025-2-13', 2 from dual union all
select 'TOM', date '2025-1-1', 4 from dual union all
select 'TOM', date '2025-1-6', 6 from dual union all
select 'TOM', date '2025-2-5', 8 from dual union all
select 'TOM', date '2025-2-9', 3 from dual 
)
select name, score_date, marks
 from data_set
order by name, score_date;

My attempt below which does not work :'(

with data_set (name, score_date, marks) as
(
select 'SAM', date '2025-1-10', 10 from dual union all
select 'SAM', date '2025-1-16', 100 from dual union all
select 'SAM', date '2025-2-10', 1 from dual union all
select 'SAM', date '2025-2-13', 2 from dual union all
select 'TOM', date '2025-1-1', 4 from dual union all
select 'TOM', date '2025-1-6', 6 from dual union all
select 'TOM', date '2025-2-5', 8 from dual union all
select 'TOM', date '2025-2-9', 3 from dual 
)
SELECT name,
       score_date,
       marks,
       FIRST_VALUE(
           CASE
               WHEN score_date BETWEEN trunc(add_months(score_date, - 1),
                                             'MM') AND last_day(trunc(add_months(score_date, - 1),
                                                                      'MM')) THEN marks
               ELSE NULL
           END
       ) IGNORE NULLS
       OVER(PARTITION BY name
            ORDER BY score_date DESC
       )            prev_month_last_val
FROM data_set
ORDER BY name,
         score_date;