ERROR: missing FROM-clause entry for table "max

I want to get the maximum id from the home_history table and filtered home_history by this value. I used it with the operator. where did I mistake it?

with max_table as (select max(id) as max_id from home_history),
     current_data as (
         select Cast(created_at As date), count(id)
         from home_history
         where id > (max_table.max_id - 30 * 500000)
           and created_at >= CAST((now()   (INTERVAL '-30 day')) AS date)
           and home_history.created_at < CAST(now() AS date)
         group by CAST(created_at AS date)
         order by CAST(created_at As Date)
     )
SELECT *
from current_data;

[42P01] ERROR: missing FROM-clause entry for table "max_table" Position: 201

CodePudding user response：

Even if it is obvious to you, you have to explicitly state in your main query that you select from the CTE.

Think of a CTE as a view defined just for a single query. You'd need a FROM clause to indicate the view you select from.