0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

WITH posts_with_total_2_tags AS
(
SELECT TOP 1000 PostId, COUNT(TagId) as tag_count
FROM PostTags
GROUP BY PostId
HAVING COUNT(TagId) = 2
),
posts_with_total_2_tags_separated AS
(
SELECT 
PostId,
MIN(TagId) OVER (PARTITION BY PostId) as tag_min_id,
MAX(TagId) OVER (PARTITION BY PostId) as tag_max_id
FROM PostTags
WHERE PostId IN (SELECT PostId FROM posts_with_total_2_tags)
),
posts_with_total_2_tags_separated_names_added AS
(
SELECT 
PostId, 
tag_min_id, 
tag_max_id, 
t1.TagName as tag_min_name,
t2.TagName as tag_max_name
FROM posts_with_total_2_tags_separated
LEFT JOIN Tags t1
ON t1.Id = tag_min_id
LEFT JOIN Tags t2
ON t2.Id = tag_max_id
),
posts_with_total_2_tags_separated_grouped AS
(
SELECT 
PostId
--CAST(tag_min_id AS VARCHAR) + ',' + CAST(tag_max_id AS VARCHAR) as min_max_tag
--CAST(tag_min_name AS VARCHAR) + ',' + CAST(tag_max_name AS VARCHAR) as min_max_tag_name
FROM posts_with_total_2_tags_separated_names_added
GROUP BY 
PostId
--CAST(tag_min_id AS VARCHAR) + ',' + CAST(tag_max_id AS VARCHAR) as min_max_tag
--CAST(tag_min_name AS VARCHAR) + ',' + CAST(tag_max_name AS VARCHAR) as min_max_tag_name
)
SELECT tag_min_id, tag_max_id, tag_min_name, tag_max_name, COUNT(*) as COUNT
FROM posts_with_total_2_tags_separated_names_added
GROUP BY tag_min_id, tag_max_id, tag_min_name, tag_max_name
ORDER BY COUNT(*) DESC

view current version

edited dec 6

created dec 6 user28646684