0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for teachers and students of the Esperanto language

-- SELECT * FROM PostTypes
-- SELECT * FROM Tags WHERE TagName LIKE '%sql-server%'

DECLARE @SinceDate DATE = '2020-01-01'
DECLARE @TagNameLike VARCHAR(50) = 'sql-server%'

;WITH SelectedTagsIds AS (
    SELECT Id
    FROM Tags
    WHERE TagName LIKE @TagNameLike
),
TopDups AS (
    SELECT
        oqid.OriginalQuestionId,
        COUNT(*) AS DupRefs
    FROM dbo.PostHistory ph
    JOIN dbo.PostsWithDeleted pd
        ON pd.Id = ph.PostId
    CROSS APPLY OPENJSON(ph.Text) WITH (
        OriginalQuestionIds NVARCHAR(MAX) 'strict $.OriginalQuestionIds' AS JSON
    ) oqids
    CROSS APPLY OPENJSON(oqids.OriginalQuestionIds) WITH (
        OriginalQuestionId INT '$'
    ) oqid
    WHERE ph.PostHistoryTypeId = 10 -- closed
    AND ph.comment = 101 -- as duplicate (664339)
    AND ph.CreationDate >= @SinceDate
    AND EXISTS ( -- 7307 dups (351 since 2024)
        SELECT *
        FROM PostTags pt
        WHERE pt.PostId = ph.PostId
        AND pt.TagId IN (SELECT Id FROM SelectedTagsIds)
    )
    GROUP BY oqid.OriginalQuestionId
)
SELECT TOP 100
    td.DupRefs,
    td.OriginalQuestionId AS Id,
    td.OriginalQuestionId AS [Post Link],
    oq.CreationDate AS Created,
    oq.Score,
    oq.AnswerCount AS Answers,
    oq.ViewCount AS Views,
    oq.CommentCount AS Comments
FROM TopDups td
JOIN PostsWithDeleted oq
    ON oq.Id = td.OriginalQuestionId
ORDER BY td.DupRefs DESC

fork query

edited nov 5 anonymous

created sep 18 jon.doe45736