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)

Home Improvement Meta

Q&A about the site for contractors and serious DIYers

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 Id), 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, '|', c.Text) AS [Comment Link],
    c.*
FROM
    ModNominations mn
        JOIN Comments c ON mn.Id = c.PostId
ORDER BY Score;

Enter Parameters

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