0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

SELECT
  a.OwnerUserId [User Link],
  MONTH(v.CreationDate) + (YEAR(v.CreationDate) * 100) 'Date',
  SUM
  (
    CASE v.VoteTypeID
    WHEN 2 THEN 1
    WHEN 3 THEN -1
    END
  ) as Score
FROM 
  votes v
    INNER JOIN
  Posts a ON v.PostID = a.ID
   INNER JOIN
  Posts q ON a.parentId = q.id
   INNER JOIN
  PostTags pt ON q.id = pt.PostId
   INNER JOIN
  Tags T ON pt.TagId = t.id
WHERE
  v.voteTypeId in (2,3) AND
  a.PostTypeID = 2 AND
  (
    a.CommunityOwnedDate is null or 
    a.CommunityOwnedDate > v.CreationDate
  ) AND
  t.TagName = 'c++' AND
  a.OwnerUserId IN
  (
    SELECT TOP 100
      a.OwnerUserId
    FROM 
      Votes v
        INNER JOIN
      Posts a ON v.PostID = a.ID
       INNER JOIN
      Posts q ON a.parentId = q.id
       INNER JOIN
      PostTags pt ON q.id = pt.PostId
       INNER JOIN
      Tags t ON pt.TagId = t.id
    WHERE
      v.voteTypeId in (2,3) AND
      a.PostTypeID = 2 AND
      (
        a.CommunityOwnedDate IS NULL OR
        a.CommunityOwnedDate > v.CreationDate
      ) AND
      a.OwnerUserId IS NOT NULL AND
      t.TagName = 'c++'
    GROUP BY
       a.OwnerUserId
    )
GROUP BY
   a.OwnerUserId,
   MONTH(v.CreationDate) + (YEAR(v.CreationDate) * 100),
   t.TagName

fork query

edited jul 26 19

created jul 22 19 anonymous