select TOP 50 RecentCol.Tag, RecentCnt, row_number() over...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

select TOP 50
        RecentCol.Tag,
        RecentCnt,
        row_number() over (order by recentCol.recentCnt desc) as RecentRank,
        TotalCnt,
        row_number() over (order by totalCol.totalCnt desc) as TotalRank
    from
    (
        select b.TagName as Tag, count(a.PostId) as recentCnt
        from
            (select PostId
            from Tags, PostTags
            where Tags.Id = TagId 
                and TagName = 'python'
                ) as a
    
        INNER JOIN
    
            (select PostId, Tags.TagName
            from Tags, PostTags, Posts
            where Tags.Id = TagId
                and TagName not like '%python%'
                and PostId = Posts.Id
                and CreationDate > DATEADD(month , -6, GETDATE())
                and CreationDate < GETDATE()
                ) as b
        
        on a.PostId = b.PostId
        group by b.TagName
    ) as recentCol
    
    INNER JOIN
    
    (
        select d.TagName as Tag, count(c.PostId) as TotalCnt
        from
            (select PostId
            from Tags, PostTags
            where Tags.Id = TagId 
                and TagName = 'python'
                ) as c
        
            INNER JOIN
        
            (select PostId, Tags.TagName
            from Tags, PostTags, Posts
            where Tags.Id = TagId
                and PostId = Posts.Id
                and TagName not like '%python%'
                and CreationDate > DATEADD(year , -4, GETDATE())
                and CreationDate < DATEADD(year , -1, GETDATE())
                ) as d
        
            on c.PostId = d.PostId
            group by d.TagName
            having count(c.PostId) > 3000
        ) as TotalCol

    on RecentCol.Tag = TotalCol.Tag
    
    order by recentCol.recentCnt desc;

Enter Parameters

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