Tag creation dates

1

Please login or register to vote for this query.

(click on this box to dismiss)

Note that SEDE limits to 50K rows at a time. So, use the `EarliestDate` parameter to "page" through the results as desired.

Stack Overflow

Q&A for professional and enthusiast programmers

-- EarliestDate: Earliest date (yyyy-mm-dd)    "Tags must be created after this date."
DECLARE @cutoffDate datetime = {d##EarliestDate:string?2007-01-01##};

WITH oldestPost AS (
    SELECT
                t.Id,
                Min (q.CreationDate) AS [Oldest Post]
    FROM        Tags t
    LEFT JOIN   PostTags pt
    ON          t.Id            = pt.TagId
    INNER JOIN  Posts q
    ON          q.Id            = pt.PostId
    GROUP BY    t.Id
),
tagStats AS (
    SELECT      t.Id,
                t.TagName,
                Format (t.Count, 'N0') AS [Num Questions],
                CASE    WHEN e.CreationDate < w.CreationDate
                        THEN Coalesce (e.CreationDate, {ts'2666-04-01  12:00:00'})
                        ELSE Coalesce (w.CreationDate, {ts'2666-04-01  12:00:00'})
                END AS TagMetaCreation,
                e.CreationDate  AS [Excerpt creation],
                w.CreationDate  AS [Wiki creation]
    FROM        Tags t
    LEFT JOIN   Posts e
    ON          t.ExcerptPostId = e.Id
    LEFT JOIN   Posts w
    ON          t.WikiPostId    = w.Id
)
SELECT      ts.TagName,
            ts.[Num Questions],
            CASE    WHEN op.[Oldest Post] < ts.TagMetaCreation
                    AND  op.[Oldest Post] IS NOT NULL
                    THEN op.[Oldest Post]
                    ELSE ts.TagMetaCreation
            END AS [Tag "creation"],
            Coalesce (Convert (varchar(30), op.[Oldest Post]     , 120),  '{none}') AS [Oldest Post],
            Coalesce (Convert (varchar(30), ts.[Excerpt creation], 120),  '{none}') AS [Excerpt creation],
            Coalesce (Convert (varchar(30), ts.[Wiki creation]   , 120),  '{none}') AS [Wiki creation]
FROM        tagStats ts
LEFT JOIN   oldestPost op
ON          ts.Id               = op.Id
WHERE       @cutoffDate < CASE
                WHEN op.[Oldest Post] < ts.TagMetaCreation
                AND  op.[Oldest Post] IS NOT NULL
                THEN op.[Oldest Post]
                ELSE ts.TagMetaCreation
            END
ORDER BY    [Tag "creation"], ts.TagName

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results