pipl people

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla Meta

Q&A about the site for Joomla! administrators, users, developers and designers

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;

Enter Parameters

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