0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- Enter Query Title
-- Enter Query Description

DECLARE
    @UserId int = ##UserId##,
    @TagName varchar(255) = '##Tag##'


;WITH CTE AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY COUNT(v.Id)) AS Rank,
        u.Id AS UserId,
        u.DisplayName AS UserName,
        COUNT(v.Id) AS UpVotes
    FROM Tags t
    INNER JOIN PostTags pt ON pt.TagId = t.id
    INNER JOIN Posts p ON p.ParentId = pt.PostId
    INNER JOIN Votes v
        ON v.PostId = p.Id
        AND v.VoteTypeId = 2
    INNER JOIN Users u ON u.Id = p.OwnerUserId
    WHERE t.TagName = @TagName
    GROUP BY
        u.Id,
        u.DisplayName
)

SELECT
    (SELECT COUNT(*) FROM CTE) AS TotalCount,
    *
FROM CTE
WHERE UserId = @UserId

fork query

created jul 27 10 anonymous