Most upvoted users within country x tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Operations Research Meta

Q&A about the site for operations research and analytics professionals, educators, and students

--Finds all users in a given country who have received
--at least one upvote on content related to a given tag

--Returns list sorted by upvotes on that tag

--NB:  The last column is the percentage of all upvotes
--awarded to the user that came from this tag.  It may
--exceed 100% because the "total upvotes" column excludes
--votes that were rescinded through either user action
--(re-clicking the upvote button in the UI) or moderation.


DECLARE @country nvarchar(100);
DECLARE @tag nvarchar(35);

SET @country = LOWER('##country?Poland##');
SET @tag = LOWER('##Tag?Android##');


WITH 
  uvs (OwnerUserId, Upvotes) AS
  (SELECT OwnerUserId, COUNT(Votes.Id) AS Upvotes
     FROM Posts     
     JOIN PostTags on Posts.Id = PostTags.PostId
     JOIN Tags on PostTags.TagId = Tags.Id
     JOIN Votes on Posts.Id = Votes.PostId
     WHERE Tags.TagName LIKE @tag
     AND Votes.VoteTypeId = 2
     GROUP BY OwnerUserId)
     
SELECT 
  Users.Id,
  Users.DisplayName,
  Users.CreationDate,
  Users.LastAccessDate,
  Users.Location,
  Users.Upvotes AS TotalUpvotes,
  uvs.Upvotes AS TagUpvotes,
    (
    CAST(uvs.Upvotes AS FLOAT) / 
    CAST((Users.Upvotes + 1) AS FLOAT) * 100.00
    )
  AS 'Pct_upvotes_from_tag',
  Users.Reputation,
  Users.Views,
  Users.DownVotes,
  Users.WebsiteUrl,
  Users.AboutMe
    
  FROM Users
  JOIN uvs ON uvs.OwnerUserId = Users.Id
  WHERE LOWER(Location) LIKE @country
        OR LOWER(Location) LIKE '%' + @country
        OR LOWER(Location) LIKE '%' + @country + '%'
        OR LOWER(Location) LIKE @country + '%'
  ORDER BY Users.Reputation DESC

Enter Parameters

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