Presto Weighted Moving Average Syntax Error

Question

I'm trying to run the weighted moving average Silota query with similar data in a Presto database but am encountering an error. The same query in the Redshift database has no issues, however in Presto I receive a syntax error:

Query failed (#20220505_230258_04927_5xpwi): 
line 14:14: Column 't2.row_number' cannot be resolved io.prestosql.spi.PrestoException: 
line 14:14: Column 't2.row_number' cannot be resolved.

The data is the same in both databases, why does the query run in Redshift while Presto throws the error?

WITH t AS
    (select date_trunc('month',mql_date) date, avg(mqls) mqls, row_number() over ()
     from marketing.campaign
        WHERE date_trunc('month',mql_date) > date('2021-12-31')
        GROUP BY 1)
select t.date, avg(t.mqls),
       sum(case
    when t.row_number - t2.row_number = 0 then 0.4 * t2.mqls
    when t.row_number - t2.row_number = 1 then 0.3 * t2.mqls
    when t.row_number - t2.row_number = 2 then 0.2 * t2.mqls
    when t.row_number - t2.row_number = 3 then 0.1 * t2.mqls
end) weighted_avg
       from t
join t t2 on t2.row_number between t.row_number - 3 and t.row_number
group by 1
order by 1

Answer 1

I suspect it is because your SQL assumes that the result of the row_number() window function will be called "row_number". This is true in Redshift but other databases may infer a different name onto it. You should alias this to some defined name such as "rn".

Also you have no "order by" clause in your row_number() function which will make the row numbers unpredictable and possibly varying between invocations.