0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for teachers and students of the Esperanto language

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 ('ios', 'swift', 'objective-c')
),

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
)

SELECT ParticipatingUsers.Tagname, COUNT(ParticipatingUsers.Id) FROM ParticipatingUsers
  INNER JOIN Users ON ParticipatingUsers.Id = Users.Id
  WHERE
  LOWER(Users.Location) LIKE '%kuopio%'
  GROUP BY ParticipatingUsers.Tagname;

fork query

edited oct 20 20

created oct 20 20 anonymous