0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software quality control experts, automation engineers, and software testers

/*
WITH YearlyTagCounts AS (
    SELECT TOP 5
        t.TagName
    FROM PostLinks pl,
         PostTags pt,
         Tags t
    WHERE pl.PostId = pt.PostId
    AND pt.TagId = t.Id
    AND pl.CreationDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
    AND pl.CreationDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
    GROUP BY t.TagName
    ORDER BY COUNT(pl.PostId) DESC
),
DailyTagPosts AS (
    SELECT
        CAST(pl.CreationDate AS DATE) AS CDate,
        t.TagName,
        COUNT(pl.PostId) AS DailyPostCount
    FROM PostLinks pl,
         PostTags pt,
         Tags t,
         YearlyTagCounts ytc
    WHERE pl.PostId = pt.PostId
    AND pt.TagId = t.Id
    AND t.TagName = ytc.TagName
    AND pl.CreationDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
    AND pl.CreationDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
    GROUP BY CAST(pl.CreationDate AS DATE), t.TagName
),
CumulativeTagPosts AS (
    SELECT
        CDate,
        TagName,
        SUM(DailyPostCount) OVER (PARTITION BY TagName ORDER BY CDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativePosts
    FROM DailyTagPosts
)
SELECT
    CDate,
    TagName AS Theme,
    CumulativePosts AS CumulativeNumberOfPosts
FROM CumulativeTagPosts
ORDER BY TagName, CDate;*/

WITH PostTagCombinations AS (
    SELECT 
        pt.PostId,
        STRING_AGG(t.TagName, ',') WITHIN GROUP (ORDER BY t.TagName) AS TagCombination
    FROM PostTags pt,
         Tags t
    WHERE pt.TagId = t.Id
    GROUP BY pt.PostId
),
CombinationCounts AS (
    SELECT 
        TagCombination,
        COUNT(*) AS CombinationCount
    FROM PostTagCombinations
    GROUP BY TagCombination
)
SELECT TOP 10
    TagCombination,
    CombinationCount
FROM CombinationCounts
ORDER BY CombinationCount DESC;

fork query

edited nov 4 jon.doe46194

created nov 4 anonymous