title: Django filtering on Window functions
body: <p>I have two Models in Django, <code>A</code> and <code>B</code>.</p>
  <p>Each <code>A</code> has several <code>B</code>s assigned to it, and the <code>B</code>s are ordered, which is done with a field <code>B.order_index</code> that counts upwards from zero for any <code>A</code>.</p>
  <p>I want to write a query that checks if there is any <code>A</code> where some of the <code>B</code>s have a gap or have duplicate <code>order_index</code> values.</p>
  <p>In SQL, this could be done like this:</p>
  <pre><code>SELECT order_index, RANK() OVER(PARTITION BY a_id ORDER BY order_index ASC) - 1 AS rnk
  WHERE rnk = order_index'
  </code></pre>
  <p>However, when I try this in Django with this code:</p>
  <pre><code>B.objects.annotate(rank=RawSQL(&quot;RANK() OVER(PARTITION BY a_id ORDER BY order_index ASC) - 1&quot;, [])).filter(rank=F('order_index'))
  </code></pre>
  <p>I get an error saying:</p>
  <pre><code>django.db.utils.ProgrammingError: window functions are not allowed in WHERE
  LINE 1: ...- 1) AS &quot;rank&quot; FROM &quot;main_b&quot; WHERE (RANK() OVE...
  </code></pre>
  <p>In SQL this would be easy enough to fix by wrapping the entire thing in a subquery and applying the Where clause to that subquery. How can I do the same in Django?</p>