SQL To Find Most Popular Category

Question

I have 3 tables in my DB (MySQL).

categories (name:string)
items (name:string, category_id:int)
votes (value:int, item_id:int, created_at:datetime)

So a category has many items, and an item has many votes.

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.

I started off trying something simpler, just getting popular items, but I'm really just guessing at this point and it doesn't work.

SELECT *, COUNT(votes.item_id) AS score
FROM items
JOIN votes USING(item_id)
WHERE votes.created_at > #{1.week.ago}
ORDER BY COUNT(votes.item_id) DESC LIMIT 5;

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'.

Answer 1

try this. use group by with the name of the category. i have commented out the created at clause as you specified, you can uncomment it if you want to use it.

 SELECT c.name, SUM(ABS(v.item_id)) 
 FROM categories c,items i, votes v
 WHERE c.name = i.name
    AND i.item_id=v.item_id
    --AND v.created_at > #{1.week.ago}
 GROUP BY c.name 
 ORDER BY SUM(ABS(v.item_id)) DESC LIMIT 5;

you will notice that i did not use the JOIN keyword but instead filtered the results of the query using only WHERE clauses, which might be easier to understand. if you want to learn more about JOINs, here is a tutorial.

Here, too, is a tutorial on SQL aliases (the AS clause). in fact, there are a bunch more tutorials on this site for a bunch of different SQL topics that are not platform dependent.

edit: fixed as per comments, added the abs function,