0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

-- Select only posts that have 2 tags associated with them
SELECT
PostId
INTO #posts_with_2_tags
FROM (
    SELECT
    PostId
    FROM PostTags
    GROUP BY PostId
    HAVING COUNT(DISTINCT TagId) = 2
) AS subquery;

-- Pull those posts and their respective tags into separate table
SELECT
PostId,
TagId
INTO #double_tagged_posts_with_their_tags
FROM PostTags
WHERE PostId IN (SELECT PostId FROM #posts_with_2_tags); 

-- self-join to put those two tags as separate attributes in one row per post
WITH tags_aside AS (
    SELECT
    t1.PostId AS PostId,
    t1.TagId AS Tag1,
    t2.TagId AS Tag2
    FROM #double_tagged_posts_with_their_tags t1
    INNER JOIN #double_tagged_posts_with_their_tags t2
    ON t1.PostId = t2.PostId
    AND t1.TagId <> t2.TagId
), 
-- ensure that pairs of the same tags are paired only in certain order,
-- so that identical pairs listed in opposite order don't do appear as unique pairs
tags_concatenated AS (
    SELECT
    PostId,
    Tag1,
    Tag2,
    CASE
        WHEN Tag1 < Tag2 THEN CONCAT(Tag1, ', ', Tag2)
        ELSE CONCAT(Tag2, ', ', Tag1)
    END AS tag_pair
    FROM tags_aside
    )
-- count occurrence of the pair and return 10 most popular
SELECT TOP 10
tag_pair,
COUNT(*) AS pair_count
FROM tags_concatenated
GROUP BY tag_pair
ORDER BY pair_count DESC;

fork query

edited nov 28

created nov 28 DanyDant