Tags by friendlyness/hostility, complicated

1

Please login or register to vote for this query.

(click on this box to dismiss)

Cognitive Sciences Meta

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

CREATE TABLE #UserTags (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  PRIMARY KEY (UserId, TagId)
);

CREATE TABLE #Result (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  MedianScore FLOAT NOT NULL,
  PRIMARY KEY(UserId, TagId)
);

WITH Raw AS (
  SELECT Posts.OwnerUserId AS UserId, PostTags.TagId
  FROM Posts, PostTags, Tags
  WHERE
    Posts.Id = PostTags.PostId
    AND
    Tags.Id = PostTags.TagId
    AND
    Posts.OwnerUserId > 0
    AND (
      ##UserIdDiv## < 2
      OR
      Posts.OwnerUserId % ##UserIdDiv:int?100## = ##UserIdMod:int?0##
    )
    AND
    Tags.Count >= ##MinTagCount:int?10000##
  GROUP BY Posts.OwnerUserId, PostTags.TagId
  HAVING COUNT(*) >= ##MinQuestionPerTagAndUser:int?5##
),
Raw2 AS (
  SELECT *
  FROM Raw
  WHERE UserId IN (
    SELECT UserId
    FROM Raw
    GROUP BY UserId
    HAVING COUNT(*) >= ##MinTagPerUser:int?5##
  )
),
Raw3 AS (
  SELECT DISTINCT
    Raw2.UserId,
    Raw2.TagId,
    PERCENTILE_CONT(0.5)
      WITHIN GROUP (ORDER BY Posts.Score)
      OVER (PARTITION BY Raw2.UserId, Raw2.TagId) AS MedianScore
  FROM Raw2, PostTags, Posts
  WHERE
    Raw2.UserId = Posts.OwnerUserId
    AND
    Raw2.TagId = PostTags.TagId
    AND
    PostTags.PostId = Posts.Id
),
Raw4 AS (
  SELECT
    A.UserId,
    A.TagId,
    MIN(A.MedianScore) AS TagMedianScore,
    AVG(1.0 * B.MedianScore) AS AvgNonTagMedianScore
  FROM Raw3 A, Raw3 B
  WHERE
    A.UserId = B.UserId
    AND
    A.TagId <> B.TagId
  GROUP BY A.UserId, A.TagId
)
SELECT
  MIN(Tags.TagName) AS TagName,
  AVG(1.0 * TagMedianScore - AvgNonTagMedianScore) AS TagHostility
FROM Raw4, Tags
WHERE Raw4.TagId = Tags.Id
GROUP BY Raw4.TagId
ORDER BY TagHostility

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results