Tag Ranking per country

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

-- Tag Rankings Fun
-- Put blanks in for TagName or CountryName for all. Tag names should be in lower case
-- Put 0 for user ID for all users

DECLARE @tagName varchar(255) 
DECLARE @tagInput bit
SET @tagname = rtrim(##TagName:string##)
DECLARE @countryName varchar(255) 
DECLARE @countryInput bit
SET @countryName = rtrim(##CountryName:string##)
DECLARE @UserID int = ##UserId##

IF RTRIM(@tagname) = ''

   SET @tagInput = 0 
ELSE
   SET @tagInput = 1
   
IF RTRIM(@countryName) = ''

   SET @countryInput = 0 
ELSE
   SET @countryInput = 1
 
if @tagInput  = 0 and @UserID = 0 
  SELECT 'Don' + '''' + 't do that'
ELSE 
 
WITH UserTagScores
as 
(SELECT  
   a.OwnerUserId,
   t.TagName,
   u.CreationDate,
   SUM(CASE v.VoteTypeID
           WHEN 2 THEN 1
           WHEN 3 THEN  -1
   END) as  score

FROM 
   votes v
   INNER JOIN Posts a
   ON v.PostID = a.ID
   INNER JOIN Posts q
   ON a.parentId = q.id
   INNER JOIN PostTags pt
   ON q.id = pt.PostId
   INNER JOIN Tags T
   ON pt.TagId = t.id
   INNER JOIN Users u
   ON a.OwnerUserId = u.ID
WHERE
   v.voteTypeId in (2,3)  -- Up and down votes
   and
   a.PostTypeID in (1,2)  -- Question and Answer
   AND 
   (@tagInput = 0
    or t.TagName = @TagName)
    AND
    (@countryInput = 0
    or LOWER(u.Location) LIKE LOWER('%' + @countryName + '%'))
    AND a.OwnerUserId is not null
GROUP BY
   a.OwnerUserId,
   t.TagName,
   u.CreationDate)
, userTagRank as (
 SELECT
    TagName,
   OwnerUserId,
    rank() over (partition by TagName order by score desc) rank,
Score,
CreationDate
  FROM
    UserTagScores)
SELECT 
    uT.TagName,
    uT.Rank,
    uT.OwnerUserId ,
    uT.OwnerUserId [User Link],
    uT.Score,
    uT.CreationDate
FROM
    userTagRank uT
WHERE 
    (uT.OwnerUserId = @UserID or @UserID = 0)
ORDER BY
    uT.TagName,
    uT.rank

Enter Parameters

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