0

I am trying to find the 3 day moving average for customers. Here is my attempt.

    SELECT
        CUSTOMER_ID, AUTH_DATE, 
        AVG(AMOUNT) OVER(PARTITION BY CUSTOMER_ID, AUTH_DATE ORDER BY AUTH_DATE RANGE BETWEEN INTERVAL '3 DAYS' PRECEDING AND CURRENT ROW) AS MA_3_DAY
    FROM CUTOMER_TABLE
    ;

I have also tried

auth_date - interval '3 days' PRECEDING

and

current row - interval '3 days' PRECEDING

and

3 PRECEDING

I'm pretty sure I've used this code in other environments but Aqua Data Studio (connected to redshift) gives me a "syntax error at or near 'interval'"

Any thoughts?

Thanks in advance

nicodemusmd
  • 5
  • 2
  • You can only have `ROW` or `Range` in a windows function, so make a cte with days as rows and use that as basis – nbk Jul 12 '22 at 17:09
  • this explain the system quite comprhensive https://learnsql.com/blog/sql-window-functions-rows-clause/ – nbk Jul 12 '22 at 17:16

0 Answers0