best users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

-- Top Users by Country
-- Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)

SELECT
distinct
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
     users.id AS [User Link], 
    Reputation 
FROM
    posts
    left join users on OwnerUserId = users.id
WHERE
    LOWER(Location) LIKE LOWER('%jordan%') 
    AND LOWER(Location) LIKE LOWER('%amman%') 
    AND LOWER(Tags) LIKE LOWER('%php%')
   
ORDER BY
    Reputation DESC ;
    
    
    WITH tagAndUserStats AS (
    SELECT
                t.tagname,
                p.owneruserid       AS userid,
                SUM (p.score)       AS tagScore,
                COUNT (p.id)        AS postCount
    FROM        posts       p
    INNER JOIN  posttags    pt      ON pt.postid = COALESCE (p.parentid, p.id)
    INNER JOIN  tags        t       ON pt.tagid = t.id
    WHERE       p.owneruserid >= 1
    AND         p.posttypeid IN (1, 2)
    GROUP BY    t.tagname,
                p.owneruserid
)
SELECT
            u.id as [User Link],
            u.Reputation,
            (   SELECT TOP 1    tu.tagname
                FROM            tagAndUserStats tu
                WHERE           tu.userid = u.id
                ORDER BY        tu.tagScore DESC
            ) AS [Top Tag by Score],
            (   SELECT TOP 1    tu.tagname
                FROM            tagAndUserStats tu
                WHERE           tu.userid = u.id
                ORDER BY        tu.postCount DESC
            ) AS [Top Tag by Posts]
FROM        users u
WHERE       u.reputation > 300000 and 
LOWER(Location) LIKE LOWER('%jordan%') 
    AND LOWER(Location) LIKE LOWER('%amman%') 
ORDER BY    u.reputation DESC

Enter Parameters

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