SQL Window Max() function having issues in code

Question

I am writing a window function that is supposed create a month window and only grab records that has the max value in the update flag field within that said month window.

I am having issues with my window function its still showing all results in the window when it should be showing the only the max value.

I have left my code below. Please help.

SELECT 
  gb1.SKU_Id,
  gb1.Warehouse_Code,
  gb1.Period_Start,
  gb1.country,
  tm.c445_month,
  tm.report_date,
  gb1.update_flag,               
  max(gb1.update_flag) over (partition by tm.yearmonth order by gb1.update_flag range between unbounded preceding and current row ) as update_window,
  SUM(gb1.TOTAL_NEW_SALES_FORECAST) AS dc_forecast
FROM BAS_E2E_OUTPUT_GLOBAL_FCST gb1
  inner join (
    SELECT
       gb2.SKU_Id,
       gb2.Warehouse_Code,
       gb2.Period_Start,
       gb2.country,
       gb2.update_flag,
       gb2.report_date,
       tm1.week_date,
       tm1.c445_month,
       tm1.yearmonth
   FROM BAS_E2E_OUTPUT_GLOBAL_FCST as gb2
     left join (
       select distinct(week_date) as week_date,
              c445_month, 
              yearmonth 
       from "PROD"."INV_PROD"."BAS_445_MONTH_ALIGNMENT" 
       group by c445_month, week_date, yearmonth 
     ) as tm1 on gb2.report_date = tm1.week_date
   group by SKU_Id,
            Warehouse_Code,
            Period_Start,
            country,
            update_flag,
            report_date,
            tm1.week_date,
            tm1.c445_month,
            tm1.yearmonth
  ) as tm 
     on gb1.report_date = tm.week_date
    and  gb1.SKU_ID = tm.sku_id
    and gb1.Warehouse_Code = tm.warehouse_code
    and gb1.Period_Start  = tm.period_start
    and gb1.country = tm.country
GROUP BY
    gb1.SKU_Id,
    gb1.Warehouse_Code,
    gb1.Period_Start,
    gb1.country,
    tm.c445_month,
    tm.yearmonth,
    tm.report_date,
    gb1.update_flag

Answer 1

You are currently using MAX with the window being defined as every preceding row up and including the current one. Hence, rightfully the max value it returns should probably change for each record. Perhaps you wanted to take the max over a fixed partition:

MAX(gb1.update_flag) OVER (PARTITION BY tm.yearmonth) AS update_window

By the way, if you did really intend to use MAX with your current window logic, on most versions of SQL the ORDER BY clause can be simplified to:

MAX(gb1.update_flag) OVER (PARTITION BY tm.yearmonth ORDER BY gb1.update_flag) AS update_window

That is, the default range is unbounded preceding to the current row, so it is not necessary to say this.