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.creationdate FirstDay,
       b.date EarnedCitizenPatrol,
       ob.other_badge_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
           
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(other_badge_count)
FROM data

fork query

created dec 9 11 conradfrix