WITH a AS ( SELECT IIF(DATEDIFF(DD, LAG(CreationDate) OVE...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Space Exploration

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

WITH a AS (
    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
    ),
     ModNominations AS (
         SELECT
             -- Take cumulative sum of flags to build election IDs
             SUM(NewElectionFlag) OVER (ORDER BY CreationDate) + 1 AS [ElectionNumber],
             *
         FROM
             a
         )
SELECT
    mn.ElectionNumber,
    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 Score;

Enter Parameters

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