CODEWITHSUNDEEP
sqlamazon-redshiftaqua-data-studio
nicodemusmd
nicodemusmd

Reputation: 5

SQL Aqua Data Studio average over date by customer

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

Upvotes: 0

Views: 55

Answers (0)

Related Questions