title: Redshift: Getting rank of a row, filtered by a condition
body: <p>Every time I add a row to a table, I want to know where it ranks in comparison with the table up to that point.  This is easily done with the RANK() window function.  However, I'm struggling to find a way to to discover where it ranks in comparison with the table up until that point <strong>filtered by a value</strong>.</p>
  
  <p>As an example, I'm wanting to end up with this highly contrived table:</p>
  
  <pre><code>    date    |   name  | animal_bought | num_sloths_bought_before | num_camels_bought_before
  ------------+---------+---------------+--------------------------+--------------------------
   2014-09-01 | Vincent | sloth         | 0                        | 0
   2014-09-01 | Luis    | camel         | 0                        | 0
   2014-09-02 | Vincent | sloth         | 1                        | 0
   2014-09-02 | Luis    | camel         | 0                        | 1
   2014-09-02 | Kevin   | sloth         | 0                        | 0
   2014-09-03 | Vincent | camel         | 1                        | 0
   2014-09-04 | Deo     | camel         | 0                        | 0
   2014-09-04 | Vincent | sloth         | 2                        | 1
   2014-09-05 | Luis    | camel         | 0                        | 2
   2014-09-05 | Andrew  | sloth         | 0                        | 0
  </code></pre>
  
  <p>I was initially looking to see whether I could apply a filter to the window function (eg. <code>RANK() OVER(PARTITION BY name WHERE animal_bought = 'sloth' ORDER BY date ASC) AS num_sloths_bought_before</code>) but this isn't syntactically correct. I then tried adding a sub-query, as follows:</p>
  
  <pre><code>SELECT
    date,
    name,
    animal_bought,
    ( SELECT
        RANK() OVER(PARTITION BY name ORDER BY date ASC) - 1
      FROM this_table
      WHERE animal_bought = 'sloth'
    ) AS num_sloths_bought_before
  FROM source_table
  </code></pre>
  
  <p>but Redshift threw this error:</p>
  
  <pre><code>ERROR:  This type of correlated subquery pattern is not supported yet
  </code></pre>
  
  <p>I've also tried putting the window function in a case statement (throws the same error) and calculating the ranks in a join query (not been able to make it work).  </p>