title: SQL To Find Most Popular Category
body: <p>I have 3 tables in my DB (MySQL).</p>
  
  <pre><code>categories (name:string)
  items (name:string, category_id:int)
  votes (value:int, item_id:int, created_at:datetime)
  </code></pre>
  
  <p>So a category has many items, and an item has many votes.</p>
  
  <p>I want to write a query to get the most popular categories, which means getting the categories whose items have gotten the most number of votes (both up or down) over the last week.</p>
  
  <p>I started off trying something simpler, just getting popular items, but I'm really just guessing at this point and it doesn't work.</p>
  
  <pre><code>SELECT *, COUNT(votes.item_id) AS score
  FROM items
  JOIN votes USING(item_id)
  WHERE votes.created_at &gt; #{1.week.ago}
  ORDER BY COUNT(votes.item_id) DESC LIMIT 5;
  </code></pre>
  
  <p>I don't really know what I'm doing, any ideas?  Also, if anyone knows of a good write up on doing more advanced selects like this I'd love to read it.  The MySQL documentation is a bit cryptic and I don't really understand 'AS' and 'JOINS'.</p>