0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for historians and history buffs

WITH OnTopicPosts AS (
  SELECT Posts.Id, Posts.OwnerUserId, Tags.Tagname FROM Posts
    INNER JOIN PostTags ON PostTags.PostId = Posts.Id
    INNER JOIN Tags ON Tags.Id = PostTags.TagId
    WHERE Tags.Tagname IN ('image-processing', 'signal-processing')
),

ParticipatingUsers AS (
    SELECT OnTopicPosts.Tagname, OnTopicPosts.OwnerUserId AS Id FROM OnTopicPosts
  UNION
    SELECT OnTopicPosts.Tagname, Comments.UserId AS Id FROM Comments
      INNER JOIN OnTopicPosts ON Comments.PostId = OnTopicPosts.Id
  UNION
    SELECT OnTopicPosts.Tagname, Votes.UserId AS Id FROM Votes
      INNER JOIN OnTopicPosts ON Votes.PostId = OnTopicPosts.Id
),

LocalUsers AS (
  SELECT Users.Id, COUNT(*) AS postcount FROM ParticipatingUsers
    INNER JOIN Users ON ParticipatingUsers.Id = Users.Id
    WHERE
      LOWER(Users.Location) LIKE '%tampere%' OR LOWER(Users.Location) LIKE '%helsinki%' OR LOWER(Users.Location) LIKE '%finland%'
    GROUP BY Users.Id
)
    
SELECT LocalUsers.postcount, 'https://stackoverflow.com/users/' + CAST(Users.Id AS VARCHAR(100)), Users.DisplayName, Users.WebsiteUrl, Users.Location, Users.UpVotes, Users.DownVotes
  FROM LocalUsers INNER JOIN Users ON LocalUsers.Id = Users.Id;

fork query

edited jan 22 20

created jan 22 20 anonymous