Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
WITH RankedPosts AS ( SELECT U.Id AS UserId, U.DisplayName AS Username, P.Id AS PostId, P.Title AS PostTitle, P.Body AS PostBody, P.Tags AS PostTags, ROW_NUMBER() OVER (PARTITION BY U.Id ORDER BY P.Id) AS RowNum, CONVERT(VARCHAR(7), P.CreationDate, 126) AS YearMonth -- Extracting year and month from post creation date FROM Users AS U JOIN Badges AS B ON U.Id = B.UserId JOIN Posts AS P ON U.Id = P.OwnerUserId WHERE B.Class = 1 -- Class 1 represents gold badges AND U.Reputation > 20000 -- Filter for users with a reputation score above 20,000 AND B.Date >= '2018-01-01' -- Filter for badges awarded on or after January 1, 2018 AND B.Date < '2020-01-01' -- Filter for badges awarded before July 1, 2018 (end of June) AND P.CreationDate >= '2018-01-01' -- Filter for posts created on or after January 1, 2018 AND P.CreationDate < '2020-01-01' -- Filter for posts created before July 1, 2018 (end of June) AND P.PostTypeId = 1 -- Filter for questions (change to 2 for answers if needed) ) SELECT UserId, Username, PostId, PostTitle, PostBody, PostTags, YearMonth FROM RankedPosts WHERE RowNum = 1;