Q&A about the site for professional robotic engineers, hobbyists, researchers and students
SELECT * FROM( SELECT U.ID AS USER_ID, COUNT(DISTINCT(VALUE)) AS BEFORE_NUMBER_OF_TAGS,COUNT(1) AS BEFORE_NUMBER_OF_ANSWERS, SUM(P.SCORE) AS BEFORE_SCORE,STRING_AGG(cast(VALUE as varchar(max)),'<') AS BEFORE_TAGS, (SELECT COUNT(DISTINCT(VALUE)) FROM POSTS AS INN CROSS APPLY STRING_SPLIT(INN.Tags, '<' ) WHERE INN.OWNERUSERID=U.ID AND CREATIONDATE BETWEEN '2014-11-27 00:00:00' AND '2015-2-27 00:00:00') AS AFTER_NUMBER_OF_TAGS, (SELECT COUNT(ID) AS TRY FROM POSTS AS INNERC WHERE INNERC.OWNERUSERID=U.ID AND CREATIONDATE BETWEEN '2014-11-27 00:00:00' AND '2015-2-27 00:00:00' ) AS AFTER_NUMBER_OF_ANSWERS, (SELECT SUM(SCORE) AS TRY FROM POSTS AS INNERC WHERE INNERC.OWNERUSERID=U.ID AND CREATIONDATE BETWEEN '2014-11-27 00:00:00' AND '2015-2-27 00:00:00' ) AS AFTER_SCORE, (SELECT STRING_AGG(cast(VALUE as varchar(max)),'<') FROM ( select DISTINCT value FROM POSTS INN CROSS APPLY STRING_SPLIT(INN.TAGS,'<') WHERE INN.OWNERUSERID=U.ID AND CREATIONDATE BETWEEN '2014-11-27 00:00:00' AND '2015-2-27 00:00:00' )X ) AS AFTER_TAGS, (SELECT SUM(SCORE) AS TRY FROM POSTS AS INNERC WHERE INNERC.OWNERUSERID=U.ID AND CREATIONDATE <='2014-11-27 00:00:00' ) AS CUMULATIVE_SCORE FROM POSTS P OUTER APPLY STRING_SPLIT(P.Tags, '<' ) S JOIN USERS U ON U.ID=P.OWNERUSERID WHERE P.CREATIONDATE BETWEEN '2014-8-27 00:00:00' AND '2014-11-27 00:00:00' GROUP BY U.ID ) FOO WHERE FOO. BEFORE_NUMBER_OF_ANSWERS>=25 AND FOO.AFTER_NUMBER_OF_ANSWERS>=25