0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

WITH RankedPosts AS (
    SELECT
        U.Id AS UserId,
        U.DisplayName AS Username,
        P.Id AS PostId,
        P.Title AS PostTitle,
        P.Body AS PostBody,
        P.Tags AS PostTags,
        ROW_NUMBER() OVER (PARTITION BY U.Id ORDER BY P.Id) AS RowNum,
        CONVERT(VARCHAR(7), P.CreationDate, 126) AS YearMonth -- Extracting year and month from post creation date
    FROM
        Users AS U
    JOIN
        Badges AS B ON U.Id = B.UserId
    JOIN
        Posts AS P ON U.Id = P.OwnerUserId
    WHERE
        B.Class = 1 -- Class 1 represents gold badges
        AND U.Reputation > 20000 -- Filter for users with a reputation score above 20,000
        AND B.Date >= '2018-01-01' -- Filter for badges awarded on or after January 1, 2018
        AND B.Date < '2020-01-01' -- Filter for badges awarded before July 1, 2018 (end of June)
        AND P.CreationDate >= '2018-01-01' -- Filter for posts created on or after January 1, 2018
        AND P.CreationDate < '2020-01-01' -- Filter for posts created before July 1, 2018 (end of June)
        AND P.PostTypeId = 1 -- Filter for questions (change to 2 for answers if needed)
)
SELECT
    UserId,
    Username,
    PostId,
    PostTitle,
    PostBody,
    PostTags,
    YearMonth
FROM
    RankedPosts
WHERE
    RowNum = 1;

fork query

edited apr 17

created apr 17 anonymous