1

Please login or register to vote for this query.

(click on this box to dismiss)

Who's asking/answering the most questions on U.S. holidays?! (And why? ... just kidding, we know why...)

Q&A about Stack Overflow, Server Fault and Super User

-- Holiday Work Horse
-- Who's asking/answering the most questions on U.S. holidays?! (And why? ... just kidding, we know why...)

DECLARE @year int = ##Year##


SELECT
    @year                                 as 'Year'
    ,u.DisplayName                        as 'User Name'
    ,count(p.Id)                          as 'Number of Questions/Answers'
    ,(u.Reputation/(count(p.Id)))         as 'Reputation'
   
FROM
  users u
    INNER JOIN posts p on p.OwnerUserId = u.id


WHERE
  1=1
  AND (p.PostTypeId IN (1,2))           /* Questions and Answers */
  AND p.OwnerUserId IS NOT NULL         /* Registered Users      */
  AND u.Reputation > 250                /* Reputation min.       */
-- AND u.DisplayName = 'iight'          /* not me...             */
  AND year(p.CreationDate) = @year      /* Year input            */
  AND
  (
    (Month(p.CreationDate) = 12 AND Day(p.CreationDate) = 25)     /*     Xmas      */
     OR
    (Month(p.CreationDate) = 7 AND Day(p.CreationDate) = 4)       /*     July 4    */
    
    /* add a different 'holy-day' here ... maybe Jan 1 ? */
  )

GROUP BY
  u.DisplayName
  ,u.Reputation
  
  HAVING count(p.Id)> 0                 /* Prevent negative denominators */
  
ORDER BY
  'Number of Questions/Answers' DESC    /* Show the most active on top */

fork query

edited jan 3

created jan 3 iight