Q&A about the site for amateur radio enthusiasts
WITH TempTable AS ( SELECT OwnerUserId, COUNT(CASE WHEN CreationDate BETWEEN '2019-09-01' AND '2019-11-24' THEN Id ELSE NULL END) AS CountPeriodA, COUNT(CASE WHEN CreationDate BETWEEN '2019-12-01' AND '2020-02-23' THEN Id ELSE NULL END) AS CountPeriodB 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 ), FINAL As( SELECT ROW_NUMBER() OVER (ORDER BY posts.OwnerUserId DESC) AS IndexColumn, posts.OwnerUserId, FORMAT(posts.CreationDate, 'yyyy-MM-dd') AS CreationDate, COUNT(posts.OwnerUserId) As Q, AVG(posts.Score) As avg_score, AVG(posts.Viewcount) As Viewm, AVG(posts.AnswerCount) As apq, COUNT(CASE WHEN AnswerCount > 0 THEN 1 ELSE NULL END) AS hasanswer, COUNT(CASE WHEN AcceptedAnswerId IS NOT NULL THEN 1 ELSE NULL END) AS hasaccept FROM posts INNER JOIN TempTable ON posts.OwnerUserId = TempTable.OwnerUserId WHERE PostTypeId=1 AND posts.CreationDate BETWEEN '2019-09-01' AND '2020-02-23' GROUP BY posts.OwnerUserId, FORMAT(posts.CreationDate, 'yyyy-MM-dd') ) Select top 5 * From FINAL order by IndexColumn desc