0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional robotic engineers, hobbyists, researchers and students

WITH TagPopularity AS (
    SELECT TOP 30
        t.Id AS TagId,
        t.TagName
    FROM 
        Tags t
    JOIN 
        PostTags pt ON t.Id = pt.TagId
    JOIN 
        Posts p ON pt.PostId = p.Id
    GROUP BY 
        t.Id, t.TagName
    ORDER BY 
        COUNT(p.Id) DESC
),
TopPosts AS (
    SELECT
        p.Id AS PostId,
        tp.TagName AS PopularTag
    FROM 
        Posts p
    JOIN 
        PostTags pt ON p.Id = pt.PostId
    JOIN 
        Tags t ON pt.TagId = t.Id
    JOIN 
        TagPopularity tp ON t.Id = tp.TagId
),
RankedPosts AS (
    SELECT
        PostId,
        PopularTag,
        ROW_NUMBER() OVER (PARTITION BY PopularTag ORDER BY p.ViewCount DESC) AS rn
    FROM TopPosts tp
    JOIN Posts p ON tp.PostId = p.Id
)
SELECT
    rp.PostId,
    rp.PopularTag,
    STRING_AGG(t.TagName, ', ') AS AllTags
FROM RankedPosts rp
JOIN PostTags pt ON rp.PostId = pt.PostId
JOIN Tags t ON pt.TagId = t.Id
WHERE rp.rn <= 5000
GROUP BY rp.PostId, rp.PopularTag
ORDER BY rp.PopularTag, rp.PostId;

fork query

edited aug 13

created aug 13 anonymous