Mod Nomination Comments With Links

1

Please login or register to vote for this query.

(click on this box to dismiss)

Writing

Q&A for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

WITH ModNominations AS (
    SELECT
        -- Take cumulative sum of flags to build election IDs
        SUM(NewElectionFlag) OVER (ORDER BY CreationDate) + 1 AS [ElectionNumber],
        -- Take only populated columns from Posts
        Id,
        PostTypeId,
        CreationDate,
        Score,
        Body,
        OwnerUserId,
        OwnerDisplayName,
        LastEditorUserId,
        LastEditorDisplayName,
        LastEditDate,
        LastActivityDate,
        CommentCount,
        CommunityOwnedDate,
        ContentLicense
    FROM
        (
            SELECT
                -- Calculate the number of days since previous mod nomination
                -- if it's more than 30 days since the last nomination consider it a new election by marking a 1
                IIF(DATEDIFF(DD, LAG(CreationDate) OVER (ORDER BY CreationDate), CreationDate) > 30, 1, 0) AS [NewElectionFlag],
                *
            FROM
                Posts p
            WHERE PostTypeId = 6 -- Mod Nomination PostType
        ) t
)
SELECT
    CONCAT('site://election/', mn.ElectionNumber, '?tab=nomination#comment', c.Id, '_', mn.Id) AS [Comment Link],
    c.*
FROM
    ModNominations mn
        JOIN Comments c ON mn.Id = c.PostId
ORDER BY CreationDate;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results