title: SQL Snowflake FILTER function - update row conditionally
body: <p>I am trying to write a query in which I update a counter based on other conditions. For example:</p>
  <pre><code>with table1 as (select *, count from table1)
  
  select box_type, 
  case when box_type = lag(box_type) over (order by time) 
  then 
    count, update table1 set count = count + 1
  else
    count
  end as identifier
  </code></pre>
  <p>Here's the basic jist of what I'm trying to do. I want a table that looks like this:</p>
  <pre><code>box_type    identifier
  small        1
  small        1
  small        1
  medium       2
  medium       2
  large        3
  large        3
  small        4
  </code></pre>
  <p>I originally needed to do this in Postgresql. The solution was</p>
  <pre><code>select t1.*,
         count(*) filter (where box_type is distinct from prev_box_type) over (order by time) as count
  from (select t1.*,
               lag(box_type) over (order by time) as prev_box_type
        from table1 t1
       ) t1
  </code></pre>
  <p>But I am unable to get this to work in Snowflake syntax.</p>
  <p>Thank you!</p>