[SOLVED] Why can't I use my column alias in WHERE clause?

Why can't I use my column alias in WHERE clause?

I want to compare a value of my current row with the value of the row before. I came up with this, but it won't work. It can't find PREV_NUMBER_OF_PEOPLE so my WHERE clause is invalid. I'm not allowed to use WITH. Does anyone have an idea?

SELECT
   ID
   ,NUMBER_OF_PEOPLE 
   ,LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
   ,DATE 
FROM (
   SELECT * FROM DATAFRAME
   WHERE DATE>=CURRENT_DATE-90
   ORDER BY DATE DESC
) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE

Solution

You have several issues with your query:

The filtering conditions should be in the outer query.
The new column definition should be in the inner query.
The order by should be in the outer query.

With these changes, it should work fine:

SELECT ID, NUMBER_OF_PEOPLE, PREV_NUMBER_OF_PEOPLE, DATE
FROM (SELECT D.*,
             LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
      FROM DATAFRAME D
     ) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE AND
      DATE >= CURRENT_DATE - 90
ORDER BY DATE DESC;

You need the filtering after the LAG() so you can include the earliest day in the date range. If you filter in the inner query, the LAG() will return NULL in that case.

You need to define the alias in the subquery so you can refer to it in the WHERE. Aliases defined in a SELECT cannot be used in the corresponding WHERE. This is a SQL rule, not due to the database you are using.