Query Votes table top items based on a percentage

Question

I have a table of Votes that looks like:

id, type, scope, region, UserId, ItemId, updatedAt, createdAt

I am trying to total the top items by percentage based on up votes / total votes.

Last piece is I want a WHERE clause for the votes for createdAt between two timestamps.

I feel like this is a very normal thing to query for getting statistics. But I am not sure how to go about this.

The closest I have gotten is:

SELECT "Votes"."ItemId", count("Votes"."ItemId") tots, count("Votes"."type" = 'up') AS yes, count("Votes"."type" = 'down') AS NO
FROM "Votes"
WHERE "Votes"."ItemId" IN (
    SELECT "Votes"."ItemId" FROM "Votes"
)
GROUP BY "Votes"."ItemId"

Which is a long ways from what is need. Hence why I would love some help here. Been having a really hard time finding good sql resources on this kind of stuff.

Answer 1

You can use a CASE statement to use 1 for each up vote and -1 for down vote and get sum of them

SELECT ItemId, SUM(CASE [type] WHEN 'up' THEN 1 WHEN 'down' THEN -1 END)
FROM Votes
WHERE createdAt >= 'startTime'
AND createdAt <= 'endTime'
GROUP BY ItemId