0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

Q&A for scientists using computers to solve scientific problems

-- Enter Query Title
-- Enter Query Description

WITH data 
     AS (SELECT 
                u.id,
                Datediff(d, u.CreationDate, b.DATE) DaysEarned_cp, 
                ob.other_badge_count, 
                p.post_count,
                Row_number() over (order by Datediff(d, u.CreationDate, b.DATE)) rn_earned_cp,
                Row_number() over (order by ob.other_badge_count) rn_ob_count,
                Row_number() over (order by p.post_count) rn_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 
       d.median,
       data_e.DaysEarned_cp,
       data_oc.other_badge_count,
       data_pc.post_count
       
FROM   (SELECT (count(id)/2) as median from data) d
       inner join data data_e on 
       data_e.rn_earned_cp = median
       inner join data data_oc on 
       data_oc.rn_ob_count= median
       inner join data data_pc on 
       data_pc.rn_post_count= median

fork query

created dec 12 11 conradfrix