18

Please login or register to vote for this query.

(click on this box to dismiss)

Received votes etc

Q&A about the site for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

-- 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;

fork query

created oct 25 10 sth