Parenting contest

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

declare @mothers_day datetime = '2014-05-11';
declare @fathers_day datetime = '2014-06-15';

with ask_days as (
  select OwnerUserId,
         dateadd(day, datediff(day, @mothers_day, CreationDate), @mothers_day) day,
         min(Score) worst,
         max(Score) best,
         count(*) N
  from Posts
  where PostTypeId = 1
        and CreationDate between @mothers_day and @fathers_day
        and ClosedDate is null
  group by OwnerUserId,
           dateadd(day, datediff(day, @mothers_day, CreationDate), @mothers_day)
),

good_days as (
  select OwnerUserId, count(*) N
  from ask_days
  where best >= 1
  group by OwnerUserId
),
        
prizes as (
  select OwnerUserId, N, Reputation,
         case
           when N >= ##tshirt?14## then 1
         end tshirt,
         case
           when N >= ##stickers?7## then 1
         end stickers
  from good_days
       join Users u on u.Id = OwnerUserId
)

select b.OwnerUserId as [User Link], N, stickers, tshirt
from prizes b
order by N desc

Enter Parameters

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