title: BigQuery apply rank / percent_rank to column with a WHERE clause
body: <p>I have a fairly wide BigQuery table with ~20-30 different columns, each of which needs to receive a complementary <em>percentile</em> column, that shows the column's percentile value compared to all other rows in the table. However, each of the columns <strong>should only</strong> receive a percentile value <strong>if</strong> the value in another column meets a certain threshold. To showcase this, I created a reproducible example below:</p>
  <pre><code>WITH
    correct_games_played AS
      (
        SELECT &quot;a&quot; as name, 7 as num1, 0.4 as num2, 0.55 as num3
        UNION ALL SELECT &quot;b&quot; as name, 13 as num1, 0.53 as num2, 0.37 as num3
        UNION ALL SELECT &quot;c&quot; as name, 4 as num1, 0.42 as num2, 0.32 as num3
        UNION ALL SELECT &quot;d&quot; as name, 17 as num1, 0.6 as num2, 0.23 as num3
        UNION ALL SELECT &quot;e&quot; as name, 7 as num1, 0.3 as num2, 0.25 as num3
        UNION ALL SELECT &quot;f&quot; as name, 16 as num1, 0.7 as num2, 0.43 as num3
        UNION ALL SELECT &quot;g&quot; as name, 10 as num1, 0.53 as num2, 0.52 as num3
        UNION ALL SELECT &quot;h&quot; as name, 5 as num1, 0.54 as num2, 0.21 as num3
        UNION ALL SELECT &quot;i&quot; as name, 9 as num1, 0.56 as num2, 0.17 as num3
        UNION ALL SELECT &quot;j&quot; as name, 3 as num1, 0.75 as num2, 0.53 as num3
      )
      
    SELECT 
      a.*,
      -- RANK() OVER(ORDER BY a.num1 DESC) AS num1_rank,
      -- RANK() OVER(ORDER BY a.num2 DESC) AS num2_rank,
      -- RANK() OVER(ORDER BY a.num3 DESC) AS num3_rank
      RANK() OVER(ORDER BY a.num1 DESC) AS num1_rank,
      RANK() OVER(ORDER BY a.num2 WHERE a.num1 &gt; 4 DESC) AS num2_rank
      RANK() OVER(ORDER BY a.num3 WHERE a.num1 &gt; 3 DESC) AS num3_rank
    FROM correct_games_played as a
  </code></pre>
  <p>This script throws the error <code>Syntax error: Expected &quot;)&quot; but got keyword WHERE at [22:37]</code>, however this works if i replace the <code>rank()</code> with the commented out <code>rank()</code>. My objective is really quite simple:</p>
  <ul>
  <li><code>num2_rank</code>: only rank values in <code>a.num2</code> if <code>a.num1</code> is greater than 4, otherwise display a <code>null</code> value</li>
  <li><code>num3_rank</code>: only rank values in <code>a.num3</code> if <code>a.num1</code> is greater than 3, otherwise display a <code>null</code> value</li>
  </ul>
  <p>My table is quite wide, and there's a chance that each column will require its own condition to determine if each columns' row's values should be ranked or not.</p>