Electorate badge achievers stats

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT u.Id, 
       u.Reputation, 
       DATEDIFF(DAY, b_silver.Date, b_gold.Date) as days_since_silver,
       c.count_comments,
       u.UpVotes + u.DownVotes as total_votes,
       q.count_questions,
       a.count_answers
FROM users u
INNER JOIN badges b_gold
ON (
  u.Id = b_gold.UserId AND
  b_gold.Name='Electorate' AND 
  b_gold.Date > '2020-06-01'
)
INNER JOIN badges b_silver
ON (
  u.Id = b_silver.UserId AND
  b_silver.Name = 'Civic Duty'
)
INNER JOIN (SELECT UserId, count(Id) as count_comments 
            FROM comments GROUP BY UserId) c
ON (
  u.Id = c.UserId
)
INNER JOIN (SELECT OwnerUserId, count(Id) as count_questions 
            FROM Posts WHERE PostTypeId=1 GROUP BY OwnerUserId ) q
ON (
  u.Id = q.OwnerUserId 
)
INNER JOIN (SELECT OwnerUserId, count(Id) as count_answers 
            FROM Posts WHERE PostTypeId=2 GROUP BY OwnerUserId ) a
ON (
  u.Id = a.OwnerUserId 
)

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results