Search code examples
sqlgoogle-bigquerywindow-functions

Getting maximum metric value over a period of latest 90 days only

I would like to find a way to add the maximum of a metrics on the last 90 days as a columns.

SELECT id1,
       id2,
       date,
       metric,
       MAX(metric) OVER(PARTITION BY id1, id2 WHERE date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)) max_l90
FROM table

I was wondering about doing something like that but it does not works. I also tried to have a look at window functions but it seems that we can't insert conditions in it.

Does any one have an idea ?

Solution

  • You can do it in a conditional way by embedding a CASE expression inside the MAX function:

    SELECT id1,
       id2,
       date,
       metric,
       MAX(CASE WHEN date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
                THEN metric 
           END) OVER(PARTITION BY id1, id2) max_l90
FROM table