WITH users_active_14_days_ago AS (
    SELECT
        rc.database,
        rc.performer.user_text,
        max(rc.meta.dt) AS latest_edit
    FROM event.mediawiki_revision_create AS rc
    WHERE
        -- Feb 2022 is fine, because this is executed on 2022-02-17, so all edits made at most 14 days ago were made in February
            rc.year = 2022
        AND rc.month = 2

        AND rc.database IN ('arwiki', 'bnwiki', 'cswiki', 'eswiki', 'frwiki')

        -- only users registered in last 6 months
        AND rc.performer.user_registration_dt >= current_timestamp - interval '6' month

    GROUP BY rc.database, rc.performer.user_text

    -- active in the last 14 days
    HAVING max(rc.meta.dt) >= current_timestamp - interval '14' day
)

SELECT
    rtc.database,
    meta.dt,
    rtc.rev_id,
    rtc.performer.user_text,
    rtc.performer.user_registration_dt,
    performer.user_edit_count

FROM event_sanitized.mediawiki_revision_tags_change AS rtc

-- active in the last 14 days
JOIN users_active_14_days_ago AS ar ON ((ar.database = rtc.database) AND (ar.user_text = rtc.performer.user_text))

WHERE
    -- we're only interested in edits made by users created less than 6 months ago, ie. the first edit can be in August 2021
    (
        rtc.year = 2022 OR
        (
                rtc.year = 2021
            AND rtc.month >= 8
        )
    )
    
    -- made in one of the required wikis
    AND rtc.database IN ('arwiki', 'bnwiki', 'cswiki', 'eswiki', 'frwiki')
    -- only add a link edits
    AND array_contains(rtc.tags, 'newcomer task add link')
    -- user registered in last 6 months
    AND rtc.performer.user_registration_dt >= current_timestamp - interval '6' month

ORDER BY rtc.database, rtc.meta.dt