CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

How do I return the date range of a max rolling average value?

I have the following query :

SELECT account,
FLOOR(max(mov_avg_7d)) AS max_mov_avg_7d
FROM (
  SELECT account,date,items,
  AVG(items) OVER (PARTITION BY account ORDER BY date RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS mov_avg_7d,
  FROM [my_table] 
)
group by account

Here is a sample of my table : 

Account         Date        Items
accountxxxxxx   2009-01-01  235
accountxxxxxx   2009-01-02  261
accountxxxxxx   2009-01-03  186
accountxxxxxx   2009-01-04  173
accountxxxxxx   2009-01-05  273
accountxxxxxx   2009-01-06  254
accountxxxxxx   2009-01-07  386

With FLOOR(max(mov_avg_7d)) AS max_mov_avg_7d I'm able to retrieve the highest average number of Items an account can have over a 7 day rolling period.

I would like to be able to have for each account the date range (7 days) associated with the highest average number of Items over 7 days.

Output would be something like this :

Account        Date       Items   max_mov_avg_7d   min_date_range max_date_range     
accountxxxxxx  2009-01-01 235     635              2009-05-12     2009-05-19

Hopefully, I'm clear enough.

Thanks !

Simon.

Upvotes: 0

Views: 185

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33755

#standardSQL
SELECT
  account,
  ARRAY_AGG(STRUCT(date, items, mov_avg_7d) ORDER BY mov_avg_7d DESC LIMIT 1)[OFFSET(0)].*
FROM (
  SELECT account,date,items,
  FLOOR(AVG(items) OVER (PARTITION BY account ORDER BY date RANGE BETWEEN 6 PRECEDING AND CURRENT ROW)) AS mov_avg_7d
  FROM `my_table`
)
group by account

Upvotes: 2

Related Questions