1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

SELECT
top 1000
tt,
tagname,
total_popularity,
pop2,
score2,
viewcount2,
pop1,
pop2,
viewcount2,
pop2-pop1 as popdiff,
convert(float,pop2-pop1) / convert(float,total_popularity) as popchange

FROM
 
(SELECT 
*
FROM
(
  SELECT
  tagid as t2,
  count(*) as pop2,
  sum(score) as score2,
  sum(viewcount) as viewcount2
  FROM
  (
    SELECT id, score, viewcount
    FROM Posts 
    WHERE CreationDate >= DATEADD(yy,-2,getdate())
  ) as p   
  INNER JOIN
  (
    SELECT tagid, postid
    FROM posttags
  ) as pt
  ON p.id = pt.postid
  GROUP BY tagid
) as last_two

INNER JOIN
(
  SELECT
  tagid as t1,
  count(*) as pop1,
  sum(score) as score1,
  sum(viewcount) as viewcount1
  FROM
  (
    SELECT id, score, viewcount
    FROM Posts 
    WHERE CreationDate >= DATEADD(yy,-1,getdate())
  ) as p   
  INNER JOIN
  (
    SELECT tagid, postid
    FROM posttags
  ) as pt
  ON p.id = pt.postid
  GROUP BY tagid
) as last_one

ON t1 = t2) as recent_tags

LEFT JOIN

(
SELECT
tagid as tt,
count(*) as total_popularity
FROM posttags
GROUP BY tagid
) as total_tags

ON recent_tags.t1 = total_tags.tt

LEFT JOIN

(SELECT 
id,
tagname
FROM Tags) as t

ON t1 = t.id

WHERE total_popularity >= 1000
ORDER BY popchange DESC

fork query

edited nov 16

created nov 15 anonymous