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;