title: Subquerying from the same table with aggregates
body: <p>I'm learning SQL and I'm trying to answer the following question:</p>
  <blockquote>
  <p>Which customer spent more money in the 3rd biggest theatre w.r.t. number
  of orders?</p>
  </blockquote>
  <p>This is my <strong>orders</strong> table:</p>
  <pre><code>+-------------+-------+-----+
  | Field       | Type  | Key |
  +-------------+-------+-----+
  | customer_id | int   | PRI |
  +-------------+-------+-----+
  | theatre     | text  |     |
  +-------------+-------+-----+
  | ticket_cost | float |     |
  +-------------+-------+-----+
  </code></pre>
  <p>This was my first attempt:</p>
  <pre class="lang-sql prettyprint-override"><code>select customer_id, theatre, sum(ticket_cost) as total_paid_eur 
  from orders
  where theatre= (select theatre
                  from orders
                  group by theatre
                  order by count(*) desc
                  limit 1 offset 2) 
  group by customer_id, theatre
  order by total_paid_eur
  limit 1;
  </code></pre>
  <p>The problem with this solution is that I'm going through the <strong>orders</strong> table twice and I hope I could do it in a single pass. I already looked into may questions on subquerying and greatest-n-per-group, but as I'm selecting based on aggregates (sum and count) most solutions do not fit.</p>
  <p>Is there a way to do it in a single pass or am I bound to it that way?
  Many thanks.</p>
  <p>UPDATE:
  I tried to add an even better description of the problem and simplified it to a &quot;minimum example&quot;.</p>