Q&A about the site for amateur radio enthusiasts
WITH TempTable AS ( SELECT OwnerUserId FROM posts WHERE PostTypeId = 1 GROUP BY OwnerUserId HAVING COUNT(CASE WHEN CreationDate BETWEEN '2020-09-06' AND '2020-11-29' THEN Id ELSE NULL END) >= 1 AND COUNT(CASE WHEN CreationDate BETWEEN '2020-12-06' AND '2021-02-28' 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 '2020-09-06' AND '2021-02-28' GROUP BY u.OwnerUserId, u.FirstTagUsageDate ) SELECT IndexColumn, OwnerUserId, CreationDate, NewTagsCount FROM NewTagsPerDate ORDER BY IndexColumn desc;