Finding specific users by their location and a tag score

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/193641/finding-specific-users-by-their-location-and-a-tag-score

Iota

Q&A for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

select u.id as [User Link]
     , sum( case when posttypeid = 1 then 1 else null end)  [Questions]
     , sum( case when posttypeid = 2 then 1 else null end)  [Answers]
     , sum( case when posttypeid  in (1,2) then 1 else null end)  [Total]
     , sum( case when posttypeid = 1 then commentcount else 0 end)  [Q-cmts]
     , sum( case when posttypeid = 2 then commentcount else 0 end)  [A-cmts]
     , sum( case when posttypeid  in (1,2) then commentcount else 0 end)  [Tot-cmts]
     , sum( case when posttypeid = 1 then cast(Favoritecount as bigint) else null end)  [Tot-Bookmarked]
     , sum( case when posttypeid = 1 then cast(Viewcount as bigint) else null end)  [Tot-Views]
from posts p
inner join posttags pt on pt.postid = p.id 
                       or pt.postid = p.parentid
inner join tags t on t.id = pt.tagid
inner join users u on u.id = p.owneruserid
where tagname = ##tag1:string## collate SQL_Latin1_General_CP1_CI_AI 
and lower(location) like '%saudi%' or
(lower(location) like '%saudi arabia%' 
) 
group by u.id
order by sum(score) desc

Enter Parameters

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