1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

--Get questions that fullfill criteria
with Questions AS (SELECT a.Id
  , COUNT(DISTINCT b.Id) AS Answers
FROM Posts AS a --questions
  INNER JOIN Posts AS b--answers
    ON a.Id = b.ParentId
    AND b.PostTypeID = 2 --answers
WHERE a.PostTypeID = 1 -- questions
AND (YEAR(a.CreationDate) = YEAR(GETDATE()))--only get for last year
    OR (YEAR(a.CreationDate) = YEAR(GETDATE())-1
      AND MONTH(a.CreationDate) >= MONTH(GETDATE()))
GROUP BY a.Id
HAVING COUNT(DISTINCT b.Id) >= 3)--only get questions with at least 3 answers

--count the number of times a tag was used per month
, Most AS(SELECT CONCAT(DATENAME(MONTH, a.CreationDate), ' ', YEAR(a.CreationDate)) AS Created
  , TagName
  , MONTH(a.CreationDate) AS Months
  , YEAR(a.CreationDate) AS Years
  , COUNT(DISTINCT a.Id) AS Time_Used
FROM Posts AS a
  INNER JOIN Questions AS b
    ON a.Id = b.Id
  INNER JOIN PostTags AS c
    ON a.Id = c.PostId
  INNER JOIN Tags AS d
    ON c.TagId = d.Id
GROUP BY CONCAT(DATENAME(MONTH, a.CreationDate), ' ', YEAR(a.CreationDate))
  , TagName
  , MONTH(a.CreationDate)
  , YEAR(a.CreationDate))

--count votes for each user, include 0s as that may be the highest
, topuser AS (SELECT a.OwnerUserId
  , e.Created
  , e.Months
  , e.Years
  , COUNT(DISTINCT f.Id) AS Votes
FROM Posts AS a
  INNER JOIN Questions AS b--info about questions for last year that got 3 answers
    ON a.Id = b.Id
  INNER JOIN PostTags AS c--info about questions for last year that got 3 answers
    ON a.Id = c.PostId
  INNER JOIN Tags AS d--info about questions for last year that got 3 answers
    ON c.TagId = d.Id
  INNER JOIN Most AS e --limiting to only the top tags
    ON MONTH(a.CreationDate) = e.Months
    AND YEAR(a.CreationDate) = e.Years
    AND d.TagName = e.TagName
    AND e.Time_Used = (SELECT MAX(z.Time_Used)--most times tagged per month
                    FROM Most AS z
                     WHERE MONTH(a.CreationDate) = z.Months
                        AND YEAR(a.CreationDate) = z.Years)
  LEFT JOIN PostFeedback AS f--get votes include posts with no votes
    ON a.Id = f.PostId
WHERE a.OwnerUserId IS NOT NULL--only get users with non-deleted accounts
GROUP BY a.OwnerUserId
  , e.Created
  , e.Months
  , e.Years)
  
--determine which top user has the highest reputation
, Reputation AS (SELECT Created
    , MAX(Reputation) AS Highest_Reputation
  FROM topUser AS a
    LEFT JOIN Users AS b
      ON a.OwnerUserId = b.Id
  WHERE Votes = (SELECT MAX(z.Votes)--get best reputation for time period
                    FROM topUser AS z
                    WHERE a.Created = z.Created)
 GROUP BY Created)

--get final top users
SELECT a.OwnerUserId AS UserID
  , a.Created
  , a.Votes
  , CASE WHEN c.Highest_Reputation IS NOT NULL THEN 1 ELSE 0 END AS Reputation_Flag --turn on flag for user with highest reputation
FROM topuser AS a
  INNER JOIN Users AS b
      ON a.OwnerUserId = b.Id
  LEFT JOIN Reputation AS c--top reputation
    ON a.Created = c.Created
    AND b.Reputation = c.Highest_Reputation
  WHERE Votes = (SELECT MAX(z.Votes)--top user by votes
                    FROM topUser AS z
                    WHERE a.Created = z.Created)
ORDER BY Years, Months;

fork query

edited apr 29

created apr 29 jon.doe41815