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
	ROW_NUMBER() OVER (ORDER BY question.creationdate DESC) AS RowNumber,
  question.Id AS QuestionId,
  question.Title AS QuestionTitle,
  -- SUBSTRING(question.Body, 1, LENGTH(question.Body) - 1) AS QuestionBody,
  question.Body AS QuestionBody,
  question.Tags AS QuestionTags, 
  -- LENGTH(question.Body) AS QuestionBodyLength
  -- COUNT(REGEXP_EXTRACT(question.Body, r"(https?://[^\s]+)")) AS hrefTagCount
  -- COUNT(REGEXP_EXTRACT(question.Body, r"(<code>)")) AS codeTagCount
  users.reputation AS UserReputation,
  badge_counts.gold AS GoldBadges,
  badge_counts.silver AS SilverBadges,
  badge_counts.bronze As BronzeBadges,
  
  question.ViewCount AS QuestionViewCount,
  question.FavoriteCount AS QuestionFavoriteCount,
  
  question.AnswerCount AS AnswerCount,
  question.Score AS QuestionScore
  -- 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
LEFT JOIN
  PostTags post_tag ON question.Id = post_tag.PostId
LEFT JOIN
  Tags tags ON post_tag.TagId = tags.Id
LEFT 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
  tags.TagName IN ('sql')
ORDER BY
  question.Score DESC
OFFSET 0 ROWS
FETCH NEXT 1000 ROWS ONLY;

Enter Parameters

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