0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for computer graphics researchers and programmers

WITH tagAndUserStats AS (
    SELECT
                t.tagname,
                p.owneruserid       AS userid,
                SUM (p.score)       AS tagScore
    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, u.reputation, u.creationdate, u.displayname, u.lastaccessdate, u.websiteurl, u.location, u.aboutme, u.views, u.upvotes, u.downvotes, u.profileimageurl, u.emailhash, u.accountid,
            (   
                SELECT          COUNT(p.owneruserid)
                FROM            posts p        
                WHERE           p.OwnerUserId = u.id AND p.posttypeid IN (1, 2)
            ) AS [post_number],
            
            (   SELECT TOP 1    tu.tagname
                FROM            tagAndUserStats tu
                WHERE           tu.userid = u.id
                ORDER BY        tu.tagScore DESC
            ) AS [top_tag_1],
            (   SELECT TOP 1    tu.tagScore
                FROM            tagAndUserStats tu
                WHERE           tu.userid = u.id
                ORDER BY        tu.tagScore DESC
            ) AS [top_tag_score],

            (   SELECT TOP 1    t.tagname
                FROM (
                  SELECT TOP 2  *
                  FROM          tagAndUserStats tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.tagScore DESC
                ) t
                ORDER BY t.tagScore
            ) AS [top_tag_2],
           
            (   SELECT TOP 1    t.tagname
                FROM (
                  SELECT TOP 3  *
                  FROM          tagAndUserStats tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.tagScore DESC
                ) t
                ORDER BY t.tagScore
            ) AS [top_tag_3],
            
            (   SELECT TOP 1    t.tagname
                FROM (
                  SELECT TOP 4  *
                  FROM          tagAndUserStats tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.tagScore DESC
                ) t
                ORDER BY t.tagScore
            ) AS [top_tag_4],
            
            (   SELECT TOP 1    t.tagname
                FROM (
                  SELECT TOP 5  *
                  FROM          tagAndUserStats tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.tagScore DESC
                ) t
                ORDER BY t.tagScore
            ) AS [top_tag_5],
            
            (   SELECT TOP 1    t.tagname
                FROM (
                  SELECT TOP 6  *
                  FROM          tagAndUserStats tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.tagScore DESC
                ) t
                ORDER BY t.tagScore
            ) AS [top_tag_6]
            
    
FROM        users u
WHERE       u.id = 3315

view current version

edited aug 15 20

created aug 15 20 jon.doe33734