Q&A for experienced flyers, pilots, and builders of drones and model aircraft
WITH TempTable AS ( SELECT OwnerUserId FROM posts WHERE PostTypeId = 1 GROUP BY OwnerUserId HAVING COUNT(CASE WHEN CreationDate BETWEEN '2019-09-01' AND '2019-11-24' THEN Id ELSE NULL END) >= 1 AND COUNT(CASE WHEN CreationDate BETWEEN '2019-12-01' AND '2020-02-23' THEN Id ELSE NULL END) >= 1 ), UserTagFirstUsage AS ( SELECT p.OwnerUserId, pt.TagId, MIN(p.CreationDate) AS FirstTagUsageDate FROM posts p JOIN PostTags pt ON p.Id = pt.PostId JOIN TempTable t ON p.OwnerUserId = t.OwnerUserId WHERE p.PostTypeId = 1 GROUP BY p.OwnerUserId, pt.TagId ), NewTagsPerDate AS ( SELECT ROW_NUMBER() OVER (ORDER BY u.OwnerUserId DESC) AS IndexColumn, u.OwnerUserId, u.FirstTagUsageDate AS CreationDate, COUNT(u.TagId) AS NewTagsCount FROM UserTagFirstUsage u WHERE u.FirstTagUsageDate BETWEEN '2019-09-01' AND '2020-02-23' GROUP BY u.OwnerUserId, u.FirstTagUsageDate ) SELECT IndexColumn, OwnerUserId, CreationDate, NewTagsCount FROM NewTagsPerDate where IndexColumn > 49999 ORDER BY IndexColumn