Votes and Posts Per Week

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

WITH ppw          as 
         (
         SELECT Dateadd(week, Datepart(week, creationdate), '1/1/' + 
                    CAST(YEAR(creationdate) AS 
                                CHAR(4))) 
                    cdate,
                    id 
         FROM   posts 
         WHERE  OwneruserID = ##UserID##) , 
vpw as
         (  SELECT Dateadd(week, Datepart(week, v.creationdate), '1/1/' + 
                    CAST(YEAR(v.creationdate) AS 
                                CHAR(4))) 
                    cdate,
                    v.id 
         FROM   Votes v 
                INNER JOIN posts P
                ON v.postId = p.id
                WHERE  p.OwneruserID = ##UserID##)
, PostsPerWeek as (SELECT cdate, count(id) posts FROM ppw  GROUP BY cdate)
, VotesPerWeek as (SELECT cdate, count(id) votes FROM vpw  GROUP BY cdate)
                
SELECT 
COALESCE(p.cdate,v.cdate) cdate,
votes,
posts
FROM 
PostsPerWeek p
FULL OUTER JOIn VotesPerWeek v
ON p.cdate = v.cdate
ORDER BY COALESCE(p.cdate,v.cdate)

Enter Parameters

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