0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

SELECT 
    u.Id as [User Link],
    u.DisplayName as 'Name',
    u.WebsiteUrl as 'URL',
    CASE WHEN u.WebsiteUrl like '%linkedin%' THEN 'Yes' ELSE '' END AS 'linkedin',
    pp.tag1qc as 'Tag1 Questions Count',
    pp.tag1ac as 'Tag1 Answers Count',
    pp.qc as 'total Questions Count',
    pp.ac as 'total Answers Count',
    u.Reputation,
    b.Gold, b.Silver, b.Bronze,
    u.Location,
    u.Views, u.UpVotes, u.DownVotes, u.CreationDate, u.LastAccessDate, u.AboutMe
FROM Users u
LEFT JOIN (SELECT
              UserId,
              COUNT(CASE WHEN class=1 THEN 1 ELSE NULL END) AS 'Gold',
              COUNT(CASE WHEN class=2 THEN 1 ELSE NULL END) AS 'Silver',
              COUNT(CASE WHEN class=3 THEN 1 ELSE NULL END) AS 'Bronze'
            FROM Badges
            GROUP BY UserId) b
      ON b.UserId = u.Id
LEFT JOIN (SELECT
              p1.OwnerUserId,
              SUM(CASE WHEN p1.PostTypeId = 1 AND p1.Tags like '%'+##Tag1:string?java##+'%'
                       THEN 1 ELSE NULL END) AS tag1qc,
              SUM(CASE WHEN p1.PostTypeId = 2 AND p2.Tags like '%'+##Tag1##+'%'
                       THEN 1 ELSE NULL END) AS tag1ac,
              SUM(CASE WHEN p1.PostTypeId = 1
                       THEN 1 ELSE NULL END) AS qc,
              SUM(CASE WHEN p1.PostTypeId = 2
                       THEN 1 ELSE NULL END) AS ac
            FROM Posts p1
            LEFT JOIN Posts p2 ON p1.ParentId = p2.Id
            GROUP BY p1.OwnerUserId) pp
      ON pp.OwnerUserId = u.Id
WHERE LOWER(location) like LOWER('%' + ##Location:string?israel## + '%')
ORDER BY Reputation DESC;

fork query

edited oct 17 18

created oct 17 18 anonymous