Percentage of posts with "thanks" in them to number of posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

declare @type as tinyint;
declare @offset as date;

set @type = iif( ##type:string?answer## = 'answer' , 2 , 1 );
set @offset = dateadd(##metric?day##, ##numDays?-7##, getdate());

select 
  Created,
  sum([Number of Comments])*100/sum([Number of Posts]) as Percentage
  from (
    select
      cast(C.CreationDate as Date) as Created,
      count(C.Id) as [Number of Comments],
      '' as [Number of Posts]
    from
      Comments as C
      left join Posts as P on P.Id = C.PostId
    where
      lower(C.Text) like ##word:string?%thank%##
      and
      C.CreationDate >= @offset and P.PostTypeId = @type
    group by
      cast(C.CreationDate as Date)

  union all

    select 
      cast(PA.CreationDate as Date) as Created,
      '' as [Number of Comments],
      count(PA.Id) as [Number of Posts]
    from
      Posts as PA
    where
      PA.CreationDate >= @offset and PA.PostTypeId = @type
    group by
      cast(PA.CreationDate as Date)
  ) as Results
group by
Created
order by
Created

Enter Parameters

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