In Snowflake, how to reference CTE in following table

We have the following working query in snowflake:

with
    latest_track_metrics as (
        select * from track_metrics
        where "week_id" = (select max("week_id") from track_metrics)
    )

select * from latest_track_metrics

in an effort to clean this code up a bit, we’d love to refactor the select max("week_id") from track_metrics into its own row with variable name, as such:

with
    max_weekid as (select max("week_id") from track_metrics),
    latest_track_metrics as (
        select * from track_metrics
        where "week_id" = max_weekid // error on this line, not recognizing max_weekid
    )

select * from latest_track_metrics

However the latter query returns the error Error: invalid identifier 'MAX_WEEKID' (line 5). We’ve tried to wrap the max_weekid with parenthesis, quotes, backticks, etc. all with no luck. Is it possible to call a CTE in this manner?

>Solution :

Using QUALIFY and windowed MAX:

with latest_track_metrics as (
   select * 
   from track_metrics
   qualify "week_id" = max("week_id") over()
)
select * 
from latest_track_metrics;