0

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows changes in the relative popularity of the current popular technologies over time in technologies related to a particular tag. This allows you to see technology in decline amongst a particular set of users, technology that is static, and rising stars. Note that this is for a particular starting point - so e.g. it is analysing the popularity of node.js questions amongst javascript users (should you start with the 'javascript' tag), not the popularity of node.js questions overall.

Q&A about the site for scientific skepticism

WITH PostUsers AS (
  SELECT DISTINCT p.OwnerUserId
  FROM Posts p
    INNER JOIN PostTags pt
      ON p.Id = pt.PostId
    INNER JOIN Tags t
      ON pt.TagId = t.Id
        AND t.TagName = ##TagName:string##
  WHERE DATEDIFF(mm, p.CreationDate, getdate()) <= ##MonthsOfHistory:int##
)
SELECT t.TagName, 
  DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
    1
  ) AS DateUsed,
  COUNT(*) AS ct
  INTO #AllTagTotals
  FROM Posts p
    INNER JOIN PostUsers pu
      ON p.OwnerUserId = pu.OwnerUserId
    INNER JOIN PostTags pt
      ON p.Id = pt.PostId
     INNER JOIN Tags t
       ON pt.TagId = t.Id
  WHERE DATEDIFF(mm, p.CreationDate, getdate()) <= ##MonthsOfHistory:int##
  GROUP BY t.TagName, DATEFROMPARTS(DATEPART(yyyy, CreationDate),DATEPART(mm, CreationDate),1);
  
WITH TopTags AS (
SELECT TOP ##NumberOfTechnologies:int## TagName, SUM(ct) AS total
  FROM #AllTagTotals
  GROUP BY TagName
  ORDER BY SUM(ct) DESC
)
SELECT * FROM TopTags

/*
SELECT tg.DateUsed, tg.TagName, tg.ct, tt.total
INTO #TagTotals
FROM #AllTagTotals tg
  INNER JOIN TopTags tt
    ON tg.TagName = tt.TagName;

WITH TotalByDate AS (
  SELECT DateUsed, SUM(ct) AS DateTotal
  FROM #TagTotals
  GROUP BY DateUsed
),
Total AS (
  SELECT SUM(ct) AS Total
  FROM #TagTotals
)
SELECT tt.DateUsed, tt.TagName, (CAST(t.Total AS FLOAT) / CAST(##NumberOfTechnologies:int## AS FLOAT)) * (CAST(tt.ct AS FLOAT) / (CAST(tt.total AS FLOAT) * CAST(tbd.DateTotal AS FLOAT)))
FROM #TagTotals AS tt
  INNER JOIN TotalByDate AS tbd
    ON tt.DateUsed = tbd.DateUsed
   CROSS JOIN Total AS t
   */

view current version

edited apr 26 18

created apr 26 18 Matt.Kimber