SQL query to find trends

Question

I have two tables - votes and blotes. Votes have a name, time and like/dislike parameter as table columns. Blotes are just some tags people voted for. They have a score (likes minus dislikes) and number_of_votes as fields.

I put together a query to find trending blotes -

SELECT name, number_of_votes, score FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY blote.number_of_votes DESC
LIMIT 25

Which is apparently wrong. It finds those Blotes which have more votes and at least one vote in last week. What I want is to find those which have more votes in last week. Hope it makes sense. Thank you.

score 1 · Accepted Answer · answered Mar 31 '11 at 19:49

Sometimes it helps a lot to write a question. I knew I need to use count(), and few moments after submitting a question, I saw how to do it.

Here it is:

SELECT count(time), blote.name, number_of_votes, score  FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY count(time) DESC
LIMIT 25

Any way to improve this query?

SQL query to find trends

1 Answers1