Data_Collection_6Months

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

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-5-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-5-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-5-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-5-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-5-27 00:00:00' AND  '2014-11-27 00:00:00'
GROUP BY U.ID
) FOO

WHERE FOO. BEFORE_NUMBER_OF_ANSWERS>=100 
AND FOO.AFTER_NUMBER_OF_ANSWERS>=100

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results