0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for project managers

SELECT q.Id,
              a.Id,
              a.AccountId,
              a.Reputation,
              q.CreationDate AS Question_Time,
              a.CreationDate AS Answer_Time,
              a.Score,
              a.OwnerUserId,
              a.CommentCount,
              a.UpVotes,
              a.DownVotes,
              a.TotalBounty
FROM Posts AS q
JOIN (
      SELECT p.Id, 
             u.AccountId,
             u.Reputation,
             p.ParentId,
             p.CreationDate,
             p.Score,
             p.OwnerUserId,
             p.CommentCount,
             v.UpVotes,
             v.DownVotes,
             v.TotalBounty
      FROM Posts as p
      JOIN Users as u ON p.OwnerUserId = u.AccountId
      JOIN (
            SELECT PostId,
                   SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
                   SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
                   SUM(CASE WHEN VoteTypeId = 9 THEN BountyAmount ELSE 0 END) AS TotalBounty
            FROM Votes
            WHERE CreationDate BETWEEN '2015-07-31' AND '2017-12-31'
            GROUP BY PostId
      ) AS v ON v.PostId = p.Id
      WHERE p.PostTypeId=2 AND p.CreationDate BETWEEN '2015-07-31' AND '2017-12-31'
) AS a ON a.ParentId = q.Id
WHERE q.PostTypeId=1 AND q.CreationDate BETWEEN '2015-07-31' AND '2017-12-31' AND q.AnswerCount=20
ORDER BY q.CreationDate, a.CreationDate;

fork query

edited oct 16 18

created oct 16 18 anonymous