WITH BadgeCounts AS ( SELECT userid, sum(case when class ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Server Fault

Q&A about the site for system and network administrators

WITH BadgeCounts AS (
    SELECT
        userid,
        sum(case when class = 1 then 1 else 0 end) AS gold,
        sum(case when class = 2 then 1 else 0 end) AS silver,
        sum(case when class = 3 then 1 else 0 end) AS bronze
        -- COUNT(*) AS badge_count
    FROM
        badges
    GROUP BY
        userid
)

SELECT DISTINCT
  -- ROW_NUMBER() OVER (ORDER BY question.creationdate DESC) AS RowNumber,
  question.*,
 
  users.reputation AS UserReputation,
  badge_counts.gold AS GoldBadges,
  badge_counts.silver AS SilverBadges,
  badge_counts.bronze As BronzeBadges
 
  -- MIN(question.CreationDate) AS QuestionCreationDate, (MIN(question.CreationDate) - question.CreationDate) AS AnswerIntervalInDays, MIN(post_history.CreationDate) AS AcceptedAnswerCreationDate, (AcceptedAnswerCreationDate - question.CreationDate) AS AnswerIntervalInDays
  -- question.CreationDate AS QuestionCreationDate,
  -- tags.TagName AS QuestionTagName,

FROM
  Posts question
JOIN
  PostTags post_tag ON question.Id = post_tag.PostId
JOIN
  Tags tags ON post_tag.TagId = tags.Id
JOIN
  PostHistory post_history ON question.Id = post_history.PostId
JOIN 
    BadgeCounts badge_counts ON question.owneruserid = badge_counts.userid
JOIN 
    users users ON question.owneruserid = users.id
    
WHERE
  question.CreationDate >= '2018-01-01' AND
  question.CreationDate <= '2020-12-31' AND
  question.PostTypeId = 1 AND
  question.tags LIKE '%sql%'
  -- tags.TagName IN ('sql')
ORDER BY
  question.creationdate DESC
OFFSET 0 ROWS
FETCH NEXT 1000 ROWS ONLY;

Enter Parameters

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