4

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.

| |
7

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;
| |
  • Ok thanks but is it possible to generate the data for all users at one place? – Rickross Mar 28 '18 at 6:19
  • 1
    let me try @Rickross – Triyugi Narayan Mani Mar 28 '18 at 6:19
  • 1
    Thanks again.. can it be modified to arrange users according to the highest voting rates to lowest? – Rickross Mar 28 '18 at 7:08
  • @Rickross You can sort by clicking on desired heading in results. Like in your case you need to click on VotesPerDay. – Triyugi Narayan Mani Mar 28 '18 at 7:11
  • 1
    Ok that's right.. thanks – Rickross Mar 28 '18 at 7:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .