Snowflake For i in 1 to 10 - can't figure out variable scope

Question

I can't figure out Snowflake variables. I have:

Declare
max_count integer default 100;
product varchar default null;

BEGIN
for i in 1 to max_count
do
let product = (select product from mytable where id = :i);
insert into new_table select revenue from rev_table where product = $product;
end for;
end;

Maybe I should use a cursor but in my example -- the :i -- is always resolving to 1. I don't really get it.

I want to use an incrementing variable here.