0

Please login or register to vote for this query.

(click on this box to dismiss)

Show 100+ rep, 50+ vote, and 10+ vote/month users

Q&A for programmers

-- Distribution of Users Creation by Month with Rep and Votes
-- Show 100+ rep, 50+ vote, and 10+ vote/month users

select COUNT(1) as [All Users], 
       SUM(CASE WHEN Reputation >= 100 THEN 1 ELSE 0 END) as [100+ Rep Users], 
       ((100 * SUM(CASE WHEN Reputation >= 100 THEN 1 ELSE 0 END))/COUNT(1)) as RepPercent,
       SUM(CASE WHEN Upvotes + Downvotes >= 50 THEN 1 ELSE 0 END) as [50+ Votes],
       ((100 * SUM(CASE WHEN Upvotes + Downvotes >= 50 THEN 1 ELSE 0 END))/COUNT(1)) as VotePercent,
       SUM(CASE WHEN ((Upvotes + Downvotes)/DateDiff(Month, CreationDate, GETDATE())) >= 10 THEN 1 ELSE 0 END) as [10 Votes per Month],
       ((100 * SUM(CASE WHEN ((Upvotes + Downvotes)/DateDiff(Month, CreationDate, GETDATE())) >= 10 THEN 1 ELSE 0 END))/COUNT(1)) as [10 Vote Percent],
       Month(CreationDate) as Month, 
       Year(CreationDate) as Year
from Users
group by  Month(CreationDate), Year(CreationDate)
order by Year(CreationDate), Month(CreationDate)

fork query

created jul 1 11 anonymous