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)

Drones and Model Aircraft Meta

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

-- 09137228992

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
    FROM
        badges
    GROUP BY
        userid
)

SELECT DISTINCT
  question.Id AS QuestionId,
  question.Title AS QuestionTitle,
  question.Body AS QuestionBody,
  question.Tags AS QuestionTags, 
  len(question.Body) AS QuestionBodyLength,
  -- url-tags + image
  -- line of codes between <code><code/>
  users.reputation AS UserReputation,
  badge_counts.gold AS GoldBadges,
  badge_counts.silver AS SilverBadges,
  badge_counts.bronze As BronzeBadges,
  -- accept rate
  question.ViewCount AS QuestionViewCount,
  question.FavoriteCount AS QuestionFavoriteCount,
  -- up vote count
  question.AnswerCount AS AnswerCount,
  question.Score AS QuestionScore,
  question.CreationDate AS QuestionCreationDate,
  --(select comments.CreationDate where comments.CreationDate=min(select CreationDate from Comments) ) AS AnswerIntervalInDays
  DATEDIFF(day, question.CreationDate, Min(comments.CreationDate)) as AnswerIntervalInDays
  --post_history.CreationDate AS AcceptedAnswerCreationDate
  --(MIN(post_history.CreationDate) - question.CreationDate) AS AcceptedAnswerIntervalInDays
 
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
JOIN
    Comments comments ON question.Id = comments.PostId

WHERE
  question.CreationDate >= '2018-01-01' AND
  question.CreationDate <= '2020-12-31' AND
  question.PostTypeId = 1 AND
  question.tags LIKE '%sql%'
GROUP BY
  question.Id,
  question.Title,
  question.Body,
  question.Tags,
  users.Reputation,
  badge_counts.gold,
  badge_counts.silver,
  badge_counts.bronze,
  question.ViewCount,
  question.FavoriteCount,
  question.AnswerCount,
  question.Score,
  question.CreationDate
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