title: How can i write subquery to finish?
body: <p>I have two tables:
  1. "authorCollection" contains key and author
  2. "book" contains key and year.</p>
  
  <p>Every book has a unique key and a book may have multi-authors.
  For example:</p>
  
  <pre><code>book:
  key  year
  1    1999
  2    2000
  3    2001
  
  authorCollection
  key  author
  1    Tom
  2    Tim
  2    Tony
  2    Mary
  3    Tim
  3    Tony
  3    Alex
  </code></pre>
  
  <p>I hope to get the average number of different collaborators of authors after year 2000(inclusive).</p>
  
  <p>After year 2000(inclusive),
  For Tim, his collab are Tony and Mary and Alex. it is 3 collabs.</p>
  
  <p>For Mary her collab are Tim and Tony. it is 2 collabs.</p>
  
  <p>For Tony, his collab are Tim , Mary and Alex. it is 3 collabs.</p>
  
  <p>For Alex, his collab are Tim and Tony. it is 2 collabs.</p>
  
  <p>The average is (3 + 2 + 3 + 2) / 4 = 2.5</p>
  
  <p>How can I write query statement to achieve?
  I can write query use "join" to filt book after 2000, and get a table like following:</p>
  
  <pre><code>SELECT key, sub1.author
  FROM (
      SELECT key, author FROM authorCollection
  ) sub1
  JOIN (
      SELECT * FROM book
      WHERE year &gt;= 2000
  ) sub2 USING (key)
  
  key author
  
  2  Tim
  2  Tony
  2  Mary
  3  Tim
  3  Tony
  3  Alex
  </code></pre>
  
  <p>How can I achieve the following? Thanks.</p>