StackOverflow users by tag and location

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @myUsers TABLE
(
Id int,
Reputation int,
CreationDate datetime,
DisplayName nvarchar(40), 
LastAccessDate datetime, 
WebsiteUrl nvarchar(200),
Location nvarchar(100), 
AboutMe nvarchar(max), 
EmailHash varchar(32), 
Age int
)

DECLARE @myPosts TABLE
(
Id int,
ParentId int,
UserId int,
Tags nvarchar(150)
)

DECLARE @myComments TABLE
(
Id int,
PostId int,
UserId int
)

--INSERT INTO @myPosts (Id, ParentId, UserId, Tags)
--SELECT Id, ParentId, OwnerUserId, Tags
--FROM Posts p
--WHERE UPPER(p.tags) LIKE UPPER('%' + ##Tag:string## + '%')
    
--INSERT INTO @myPosts (Id, ParentId, UserId, Tags)
--SELECT Id, ParentId, OwnerUserId, Tags
--FROM Posts p
--WHERE p.posttypeid = 2 AND p.parentid IN (SELECT id from @myPosts)

--INSERT INTO @myComments (Id, PostId, UserId)
--SELECT Id, PostId, UserId
--FROM Comments c
--WHERE c.postid in (SELECT id from @myPosts)

INSERT INTO @myUsers (Id, Reputation, CreationDate, DisplayName, LastAccessDate, WebsiteUrl, Location, AboutMe, EmailHash, Age)
SELECT Id, Reputation, CreationDate, DisplayName, LastAccessDate, WebsiteUrl, Location, AboutMe, EmailHash, Age
--SELECT *
FROM Users u
WHERE UPPER(u.location) LIKE UPPER('%' + ##Location:string## + '%')

INSERT INTO @myPosts (Id, ParentId, UserId, Tags)
SELECT Id, ParentId, OwnerUserId, Tags
FROM Posts p
WHERE UPPER(p.tags) LIKE UPPER('%' + ##Tag:string## + '%') AND p.OwnerUserId IN (SELECT id FROM @myUsers)

INSERT INTO @myPosts (Id, ParentId, UserId, Tags)
SELECT Id, ParentId, OwnerUserId, Tags
FROM Posts p
WHERE p.ParentId IN (SELECT Id FROM @myPosts) AND p.OwnerUserId IN (SELECT Id FROM @myUsers)

--INSERT INTO @myComments (Id, PostId, UserId)
--SELECT Id, PostId, UserId
--FROM Comments c
--WHERE c.postid in (SELECT id from @myPosts) AND c.userid in (SELECT id FROM @myUsers)

--SELECT COUNT(id)
SELECT *
FROM @myUsers mu WHERE mu.Id In (SELECT UserId FROM @myPosts)

--SELECT COUNT(id)
--FROM @myComments

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results