I need a query for finding out users with high voting rates

Question

By voting rate I mean the ratio "number of votes cast:number of days member for".

In my own case, this ratio is approximately 2.933. If we take "number of days visited" instead of "number of days member for", then my voting rate is round about 3.533.

So, I need a query for finding out users with high voting rates. The query should list the users according to the highest voting rates to the lowest.

This will give an idea about how frequently a particular user is voting when compared to others.

Answer 1

I have created a query to get votes per day for particular user. Query is as follows:

DECLARE @UserID int = ##UserId##
select Id as UserId, CreationDate, LastAccessDate, UpVotes, DownVotes, 
DATEDIFF(DAY, CreationDate, LastAccessDate) AS MemberForDays, 
(UpVotes+DownVotes) AS TotalVotes, 
(CAST((UpVotes+DownVotes) AS float)/DATEDIFF(DAY, CreationDate, LastAccessDate)) AS VotesPerDay 
from Users where Users.Id=@UserID;

I am counting total number of days by subtracting the date of creation and last access date. It can be modified to current date also by using getDate() instead of LastAccessDate.

Now, I have created another query to display votes per day for all users. Query is as follows:

select Id as UserId,
DATEDIFF(DAY, CreationDate, LastAccessDate) AS MemberForDays,
(UpVotes+DownVotes) AS TotalVotes,
(CAST((UpVotes+DownVotes) AS float)/DATEDIFF(DAY, CreationDate, LastAccessDate)) AS VotesPerDay 
from Users 
where ((UpVotes+DownVotes) > 0 AND DATEDIFF(DAY, CreationDate, LastAccessDate) > 0)
ORDER BY Users.Id;