How to use a CTE consisting of a list of values to filter a query

Question

I want to use a CTE that provides nothing but integers within a query's WHERE x IN ___ clause. I've provided comments inside the code block - is there a way to fix this syntax error, or otherwise if what I am trying to do in #3 is impossible, can this be done in a decently performant way that doesn't require a subquery?

-- #1: this CTE gives each cat's meals a meal_idx
WITH cat_meals_in_order AS (
  SELECT id, cat_id, food_type, duration,
  RANK() OVER (PARTITION BY cat_id ORDER BY created_at ASC) AS meal_idx
  FROM cat_meals
),

-- #2: this CTE shows us all cat_id for which the first meal was salmon
cats_that_ate_salmon_first AS (
  SELECT cat_id
  FROM cat_meals_in_order
  WHERE meal_idx = 1
  AND food_type = 'salmon'
)

-- #3: I want to filter by cats that ate salmon as their first meal.
-- This one gives a syntax error because a table doesn't seem expected after IN.
-- I could delete #2, and use it as a subquery here, but the performance is terrible in a large table.
SELECT COUNT(*) as num_cats_ate_salmon_first_then_tuna
FROM cat_meals_in_order
WHERE cat_id IN cats_that_ate_salmon_first
AND meal_idx = 2
AND food_type = 'tuna'

Answer 1

After correcting your query syntaxes:

SELECT COUNT(*) as num_cats_ate_salmon_first_then_tuna
FROM cat_meals_in_order ord 
WHERE ord.cat_id IN (select cat_id from cats_that_ate_salmon_first)
AND ord.meal_idx = 2
AND ord.food_type = 'tuna'

I want to explain to you, the result of the subquery after IN command is if get large data, for example, a million records, then your query performance will be very very decreasing. In this situation, you can use inner join for best performance. For example:

SELECT COUNT(*) as num_cats_ate_salmon_first_then_tuna
FROM cat_meals_in_order ord 
inner join cats_that_ate_salmon_first frs on ord.cat_id = frs.cat_id 
WHERE 
    ord.meal_idx = 2 AND 
    ord.food_type = 'tuna';