SELECT p.OwnerUserId as UserId, DATEPART(Year, v.Creation...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics Meta

Q&A about the site for people studying math at any level and professionals in related fields

SELECT 
    p.OwnerUserId as UserId,
    DATEPART(Year, v.CreationDate) as Year,
    DATEPART(Month, v.CreationDate) as Month,
    SUM(CASE WHEN  v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS QUpVotes,
    SUM(CASE WHEN  v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS AUpVotes,
    SUM(CASE WHEN  v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS QDownVotes,
    SUM(CASE WHEN  v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS ADownVotes,
    SUM(CASE WHEN v.VoteTypeId in (1,5) AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS QFavoriteVotes,
    SUM(CASE WHEN v.VoteTypeId in (1,5) AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS AFavoriteVotes,
    SUM(CASE WHEN v.VoteTypeId in (4,6,10,12) AND p.PostTypeId = 1 THEN 1 ELSE 0 END) AS QPeeveVotes,
    SUM(CASE WHEN v.VoteTypeId in (4,6,10,12) AND p.PostTypeId = 2 THEN 1 ELSE 0 END) AS APeeveVotes
FROM Posts as p
inner join Votes v ON v.PostId=p.Id
WHERE p.owneruserid > 0 
AND p.PostTypeId in (1,2)
AND DATEPART(Year, v.creationdate) >= 2010 
AND DATEPART(Year, v.creationdate) <= 2020
GROUP BY p.OwnerUserId, DATEPART(Year, v.CreationDate), DATEPART(Month, v.CreationDate)

Enter Parameters

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