Received votes etc
Q&A for Drupal developers and administrators
-- Users statistics -- Received votes etc with Edits as ( select Users.Id as Id, count(Posts.Id) as Edits from Posts inner join Users on Users.Id = Posts.LastEditorUserId and Posts.OwnerUserId != Users.Id group by Users.Id ), Up as ( select Users.Id as Id, count(Votes.Id) as Votes from Users inner join Posts on Users.Id = Posts.OwnerUserId inner join Votes on Posts.Id = Votes.PostId where Votes.VoteTypeId = 2 group by Users.Id ), Down as ( select Users.Id as Id, count(Votes.Id) as Votes from Users inner join Posts on Users.Id = Posts.OwnerUserId inner join Votes on Posts.Id = Votes.PostId where Votes.VoteTypeId = 3 group by Users.Id ), Accept as ( select Users.Id as Id, count(Votes.Id) as Votes from Users inner join Posts on Users.Id = Posts.OwnerUserId inner join Votes on Posts.Id = Votes.PostId where Votes.VoteTypeId = 1 group by Users.Id ), Answers as ( select Users.Id as Id, count(Posts.Id) as [Amount] from Users, Posts where Users.Id = Posts.OwnerUserId and Posts.PostTypeId = 2 group by Users.Id ) select Users.Id as [User Link], Reputation as [Reputation], Up.Votes + Down.Votes as [Votes received], round(Down.Votes * 100.0 / (Up.Votes+Down.Votes), 1) as [% Downvoted], Edits as [Cleanup edits], Answers.Amount as [Answers], round(Accept.Votes * 100.0 / Answers.Amount, 1) as [% Accepted] from Users, Up, Down, Edits, Accept, Answers where Users.Id = Up.Id and Users.Id = Down.Id and Users.Id = Edits.Id and Users.Id = Accept.Id and Users.Id = Answers.Id and Users.Id = ##UserId## order by Users.Reputation desc;