Percentage of questions in given two tags per month

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 @sql nvarchar(max) -- will hold the final sql statement
declare @cols nvarchar(max) -- gets the first columns
declare @pvt nvarchar(max)  -- gets the pivot columns
declare @tags nvarchar(max) = ##tags:string?statistics,probability##

-- string aggragate the tags wit the percentage calculation
select @cols = string_agg(
       concat('(convert(float,[' 
            , value
            , ']) *100) /  
              (select cnt
               from qpm /* this is a CTE  */
               where eom = [date]) '
            , '['
            , value
            , ']')
       , ',')
from string_split(@tags, ',')

-- string aggragaye the pivot columns
select @pvt = string_agg(concat('[' , value,']'), ',')
from string_split(@tags, ',')

-- glue everythig together
set @sql = concat(N'
-- CTE with just the last date of a month and the question count
;with qpm as (
  select eomonth(creationdate) eom
       , count(*) cnt
  from posts where posttypeid = 1 
  group by eomonth(creationdate)
)
-- the pivot table query
SELECT [date],',
@cols,
N'
FROM 
    (
     select eomonth(p.creationdate) [date]
          , tagname
          , p.id
          from posts p
          inner join posttags pt on pt.postid = p.id
          inner join tags t on t.id = pt.tagid
          -- the date params work here because of a 
          -- SEDE feature of full string replacement enclosed in a single quote
          -- to make it work here we have to add a single quote ourselves
          where p.creationdate between '##date1:string?2016-01-01##'  
                                   and '##date2:string?2018-09-01##'
    )   
    AS src  
PIVOT  
(  
    count(src.id) 
FOR   
    tagname   
    IN (',
@pvt
,N')  
) AS pvt
ORDER BY date;')

-- we can inspect in the Messages tab
print @sql

-- execute what we created
exec(@sql)

Enter Parameters

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