0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for science fiction and fantasy enthusiasts

with user_tag as ( -- score per user, per tag
  SELECT t.TagName,
       a.OwnerUserId AS [User Link],
       SUM(a.Score) / 10.0 AS Score,
       COUNT(a.Score) AS [Count],
       ((SUM(a.Score) / 10.0) + COUNT(a.Score)) / 2.0 AS Total
  FROM Posts a
  JOIN Posts q on a.ParentId = q.Id
  JOIN PostTags qt ON q.Id = qt.PostId
  JOIN Tags t ON t.Id = qt.TagId
  WHERE a.PostTypeId = 2
    AND a.CommunityOwnedDate IS NULL
    AND a.OwnerUserId IS NOT NULL
  GROUP BY a.OwnerUserId, t.TagName
),
max_score as ( -- max score per tag
  select TagName, max(Total) as max_score
  from user_tag
  group by TagName
)
select 
    u.*
  from user_tag u
  join max_score m on m.TagName = u.TagName
                  and m.max_score = u.Total
  where u.[User Link] = '##userId##'
  order by u.Total desc, u.TagName

fork query

edited oct 17 18

created jan 9 18 anonymous