CODEWITHSUNDEEP
sqlamazon-redshiftwindow-functions
Sal
Sal

Reputation: 277

Calculate MAX for value over a relative date range

I am trying to calculate the max of a value over a relative date range. Suppose I have these columns: Date, Week, Category, Value. Note: The Week column is the Monday of the week of the corresponding Date.

I want to produce a table which gives the MAX value within the last two weeks for each Date, Week, Category combination so that the output produces the following: Date, Week, Category, Value, 2WeeksPriorMAX.

How would I go about writing that query? I don't think the following would work: 

SELECT Date, Week, Value, 
       MAX(Value) OVER (PARTITION BY Category 
                         ORDER BY Week 
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as 2WeeksPriorMAX

The above query doesn't account for cases where there are missing values for a given Category, Week combination within the last 2 weeks, and therefore it would span further than 2 weeks when it analyzes the 2 preceding rows.

Upvotes: 2

Views: 254

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Left joining or using a lateral join/subquery might be expensive. You can do this with window functions, but you need to have a bit more logic:

select t.*,
       (case when lag(date, 1) over (partition by category order by date) < date - interval '2 week'
             then value
             when lag(date, 2) over (partition by category order by date) < date - interval '2 week'
             then max(value) over (partition by category order by date rows between 1 preceding and current row)
             else max(value) over (partition by category order by date rows between 2 preceding and current row)
        end) as TwoWeekMax
from t;

Upvotes: 1

Related Questions