0

Please login or register to vote for this query.

(click on this box to dismiss)

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

Q&A about the site for operations research and analytics professionals, educators, and students

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

WITH JavaScriptUsers AS (
    -- Step 1: Get users who have contributed to JavaScript-tagged posts
    SELECT 
        DISTINCT p.OwnerUserId AS UserId
    FROM 
        Posts p
    INNER JOIN 
        PostTags pt ON p.Id = pt.PostId
    INNER JOIN 
        Tags t ON pt.TagId = t.Id
    WHERE 
        LOWER(t.TagName) = 'javascript'
        AND p.OwnerUserId IS NOT NULL
        AND t.Count > 100
)

-- Step 2: Rank users from Bangalore who have contributed to JavaScript posts
SELECT
    ROW_NUMBER() OVER(ORDER BY u.Reputation DESC) AS [#], 
    u.Id AS [User Link], 
    u.Reputation
FROM
    Users u
INNER JOIN 
    JavaScriptUsers ju ON u.Id = ju.UserId
WHERE
    LOWER(u.Location) LIKE LOWER('%##CountryName##%') 
    AND u.Reputation >= 2674
ORDER BY
    u.Reputation DESC;

fork query

edited aug 31 anonymous

created nov 19 11 Samuel Liew