Closed Questions 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

-- Closed Questions stats

WITH weeksyears 
     AS (SELECT w.NUMBER AS MONTH, 
                y.NUMBER AS YEAR 
         FROM   (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 1 AND 12) w, 
                (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 2008 AND 2022) y), 
     counts 
     AS (SELECT wy.MONTH, 
                wy.YEAR, 
                CAST(COUNT(p.id) as decimal (15,5))postcount, 
                CAST(SUM(CASE 
                      WHEN closeddate IS NULL THEN 0 
                      ELSE 1 
                    END)as decimal (10,5))    closecount ,
                 AVG(CASE WHEN closeddate IS NOT NULL THEN cast( SCORE as decimal (10,5)) ELSE NULL END) AvgClosedQuestionScore
         FROM   posts p 
                INNER JOIN weeksyears wy 
                  ON MONTH(p.creationdate) = wy.MONTH 
                     AND YEAR(p.creationdate) = wy.YEAR 
         GROUP  BY wy.YEAR, 
                   wy.MONTH) 
, CloseVotesButNotClosed as 
(
SELECT
    Cast(Count(id) as decimal (10,5)) PostCount,
    month(CreationDate) Month,
    Year(CreationDate) Year
  
FROM 
   POSTS p
   
WHERE
    p.ClosedDate is not null
    and 
    exists (SELECT 'A' 
            FROM  VOTES v 
            WHERE VoteTypeID = 6 --Close
                    and v.PostID = p.ID)
GROUP BY     
    month(CreationDate) ,
    Year(CreationDate) )


SELECT c.MONTH, 
       c.YEAR, 
        c.AvgClosedQuestionScore,
        c.postcount, 
       c.closecount ,
       cv.postcount as  CloseVotesButNotClosed_PostCount,
       (c.closecount  * 100) / c.postCount ClosePercent,
      (cv.postcount  * 100) / c.postCount CloseVotesButNotClosedPercent

     
FROM   counts c
       LEFT join CloseVotesButNotClosed  cv
      ON C.month = cv.month 
        and c.year = cv.year
WHERE  closecount <> 0
ORDER  BY YEAR, 
          MONTH 

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results