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;