0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

WITH data 
     AS (SELECT u.creationdate firstday, 
                b.DATE         earnedcitizenpatrol, 
                ob.other_badge_count, 
                p.post_count 
         FROM   users u 
                INNER JOIN badges b 
                  ON u.id = b.userid 
                CROSS APPLY (SELECT COUNT(ob.id) other_badge_count 
                             FROM   badges ob 
                             WHERE  ob.DATE < b.DATE 
                                    AND ob.userid = b.userid) ob 
                CROSS APPLY (SELECT COUNT(p.id) post_count 
                             FROM   posts p 
                             WHERE  p.creationdate < b.DATE 
                                    AND p.owneruserid = b.userid)p 
         WHERE  b.name = 'Citizen Patrol' 
                AND u.creationdate >= '2008-09-15 8:55:05' 
        --ingoring users that signed on before the badge was created 
        ) 
SELECT 
       
       AVG(Datediff(d, firstday, earnedcitizenpatrol)) Avg_Days_BeforeEarningCP, 
       AVG(other_badge_count) Avg_OfBadges_BeforeEarningCP, 
       AVG(post_count)Avg_OfPosts_BeforeEarningCP
 

FROM   data 

fork query

created dec 9 11 conradfrix