3

Please login or register to vote for this query.

(click on this box to dismiss)

How hot is a software technology? This query graphs the occurrence frequency of SO tags to measure the community's relative interest in the technology. DEFAULTS: *** The period measured is a year. If "current" specified, the last 52 weeks is used. Otherwise a calendar year is used such as "2012". *** Tag occurrence is normalized to the average frequency of the 12 weeks before the beginning of the specified year. *** "ALL_SO" for TagName2 represents all StackOverflow questions.

Q&A for ebook publishers and readers

DECLARE @Tag1Name nvarchar(max) = ##TagName1:string?javascript##;
DECLARE @Tag2Name nvarchar(max) = ##TagName2:string?ALL_SO##;
DECLARE @Year nvarchar(max) = ##Year:string?current##;

DECLARE @BaselineWeeks int = 12;  -- baseline before trend weeks.
DECLARE @TrendWeeks int = 54;
DECLARE @Tag1Id int = (SELECT Id FROM Tags WHERE TagName = @Tag1Name);;
-- We slug in 'javascript' to have something to count if ALL_SO is used.
DECLARE @Tag2Id int = (SELECT Id FROM Tags 
  WHERE TagName = IIF(@Tag2Name = 'ALL_SO', 'javascript', @Tag2Name));
DECLARE @LastDateNum int = IIF(@Year = 'current',
  CONVERT(integer, GETDATE()),
  CONVERT(integer, DATEADD(month, 0, (@Year + '/12/31'))));
DECLARE @TrendStart int = @LastDateNum - 7*@TrendWeeks;
DECLARE @BaselineStart int = @TrendStart - 7*@BaselineWeeks;

-- Check for valid tag names.
DECLARE @Msg nvarchar(max);
IF @Tag1Id >= 0
  SET @Msg = '';
ELSE
  SET @Msg = '"' + @Tag1Name + '" is not a valid Stack Overflow tag.';
IF @Tag2Id >= 0
  SET @Msg = @Msg;
ELSE
  SET @Msg = @Msg + ' "' + @Tag2Name + '" is not a valid Stack Overflow tag.';
IF @Msg != ''
  THROW 511111, @Msg, 1;

-- Need to create a temp table for the final EXEC string.
CREATE TABLE #ResultTable (
  week_start_date date, 
  tag1_growth float,  
  tag2_growth float, 
  so_growth float, 
  tag1_count int,  
  tag2_count int, 
  so_count int);


WITH PostTagJoin AS 
( -- All posts in date range.  Tag flags are attached.
SELECT  
  MAX(CONVERT(integer, CreationDate)) AS date_num,
  MAX(DATEPART(dayofyear, CreationDate)) AS dofy_num,
  SUM(IIF(TagId = @Tag1Id, 1, 0)) AS tag1_count,
  SUM(IIF(TagId = @Tag2Id, 1, 0)) AS tag2_count
  FROM (Posts JOIN PostTags ON Id = PostId)
  WHERE PostTypeId = 1 -- Questions Only --
    AND CONVERT(integer, CreationDate) >= @BaselineStart
    AND CONVERT(integer, CreationDate) < @LastDateNum 
  GROUP BY PostId
)
, PostCount AS
( -- Per Day Post and Tag Counts. The week_num is attached.
SELECT 
  DATEADD(day, date_num, 0) AS fdate,
  MAX(date_num) AS date_num,
  (MAX(date_num) - @BaselineStart)/7 AS week_num,
  SUM(tag1_count) AS tag1_count,
  SUM(tag2_count) AS tag2_count,
  COUNT(*) AS so_count,
  -- holiday_fudge is a multiplier to smooth out the 
  -- end and beginning of year dip.
  MAX(IIF(dofy_num > 356 OR dofy_num <= 3, 1.3, 1.0)) AS holiday_fudge
  FROM PostTagJoin
  GROUP BY date_num
  )
, BaseCount AS 
( -- Count of posts and tags in the baseline period.
SELECT 
  SUM(holiday_fudge*tag1_count)/(7.0*@BaselineWeeks) AS base_daily_tag1,
  SUM(holiday_fudge*tag2_count)/(7.0*@BaselineWeeks) AS base_daily_tag2,
  SUM(holiday_fudge*so_count)/(7.0*@BaselineWeeks) AS base_daily
  -- COUNT(*) AS week_count
  FROM PostCount
  WHERE week_num < @BaselineWeeks
)
, WeekCount AS
( -- Grouping of all tallies per week.  Baseline counts attached.
SELECT 
  MAX(week_num) - @BaselineWeeks AS week_num,
  SUM(holiday_fudge*tag1_count)/7.0 AS tag1_per_day,
  SUM(holiday_fudge*tag2_count)/7.0 AS tag2_per_day,
  SUM(holiday_fudge*so_count)/7.0 AS q_per_day,
  MAX(base_daily) AS base_daily,
  MAX(base_daily_tag1) AS base_daily_tag1,
  MAX(base_daily_tag2) AS base_daily_tag2,
  SUM(holiday_fudge*tag1_count) AS tag1_count,
  SUM(holiday_fudge*tag2_count) AS tag2_count,
  SUM(holiday_fudge*so_count) AS so_count,
  MIN(fdate) AS week_start_date
  FROM (PostCount JOIN BaseCount ON 1=1)
  GROUP BY week_num
)
INSERT INTO #ResultTable SELECT TOP 1000 
  week_start_date,
  tag1_per_day/base_daily_tag1,
  tag2_per_day/base_daily_tag2,
  q_per_day/base_daily,
  tag1_count,
  tag2_count,
  so_count
  FROM WeekCount
  WHERE week_num >= 0 
    AND (week_num < @TrendWeeks - 1) -- trim last incomplete week
  ORDER BY week_num ASC

-- The EXEC is needed to dynamically generate the column labels
-- from the tag names and counts.
DECLARE @Tag1Total nvarchar(max) =
  (SELECT SUM(tag1_count) FROM #ResultTable);
DECLARE @Tag2Total nvarchar(max) =
  (SELECT SUM(tag2_count) FROM #ResultTable);
DECLARE @SOTotal nvarchar(max) =
  (SELECT SUM(so_count) FROM #ResultTable);
DECLARE @Tag2Label nvarchar(max) = IIF(@Tag2Name = 'ALL_SO', 
  ('so_growth AS [SO Question Growth. Total SO Questions: ' + @SOTotal + ']'),
  ('tag2_growth AS [' + @Tag2Name + '" Tag Growth. Total Tagged: ' + @Tag2Total + ']') );

EXEC('SELECT week_start_date,tag1_growth AS ["' + @Tag1Name +
  '" Tag Growth. Total Tagged: ' + @Tag1Total + '], ' +
  @Tag2Label + ' FROM #ResultTable')
DROP TABLE #ResultTable

fork query

edited jan 22 14

created jan 7 14 mjhm