QUERY = 
WITH revert_risks AS (
    SELECT
        rr.wiki_db AS wiki_db,
        rr.rev_id AS revision_id,
        rev_revert_risk AS revert_risk_score,
        rr.rev_timestamp AS edit_timestamp,
        mwh.event_user_text,
        mwh.event_user_revision_count AS user_edit_count,
        -- was it a newly created account. defined as user that created account 90 days before edit
        CASE WHEN rru.user_id IS NOT NULL
            AND unix_timestamp(rr.rev_timestamp) - unix_timestamp(rru.user_registration_timestamp) < 7776000
            THEN TRUE
            ELSE FALSE
        END AS is_new_account,
        -- was the user anonymous
        CASE
            WHEN event_user_is_anonymous THEN TRUE
            ELSE FALSE
        END AS is_anon,
        CASE
            WHEN SIZE(event_user_is_bot_by_historical) = 0  THEN FALSE
            ELSE TRUE
        END AS user_is_bot,
        -- edit reverts another edit
        revision_is_identity_revert AS is_revert,
        rr.page_title,
        page_namespace_is_content AS namespace_is_content,
        -- was this edit reverted
        CASE
            WHEN rr.rev_is_identity_reverted THEN TRUE
            ELSE FALSE
        END AS is_reverted,
    -- time to revert
        rr.rev_seconds_to_identity_revert AS time_to_revert,
    -- was the user extended confirmed
        CASE
            WHEN ARRAY_CONTAINS(event_user_groups, 'extendedconfirmed') THEN TRUE
            ELSE FALSE
        END AS is_extendedconfirmed,
        CASE
            WHEN bc.user_text IS NOT NULL THEN TRUE
            ELSE FALSE
        END AS is_blocked,
        CASE
            WHEN amr.amr_rev_parent_id IS NOT NULL THEN TRUE
            ELSE FALSE
        END AS was_reverted_by_automoderator
    FROM
        risk_observatory.revert_risk_predictions rr
    JOIN
        wmf.mediawiki_history mwh
        ON rr.wiki_db = mwh.wiki_db
        AND rr.rev_id = mwh.revision_id
    LEFT JOIN wmf_product.automoderator_monitoring_snapshot_daily amr
        ON rr.rev_id = amr.amr_rev_parent_id
    -- was reverted by automoderator edits
        AND is_amr_revert
        AND amr.wiki_db = 'enwiki'
        AND amr.snapshot_date = '2025-02-15'
    LEFT JOIN event.mediawiki_user_blocks_change  bc
    ON rr.user_name = bc.user_text
    AND rr.wiki_db = bc.`database`
        AND bc.`database` = 'enwiki'
    -- user blocked after edit
    -- AND bc.meta.dt > rr.rev_timestamp
    -- join to users dataset to identify new users
    LEFT JOIN mneisler.canonical_revertrisk_users_2024 rru
        ON mwh.event_user_id = rru.user_id
        AND mwh.wiki_db = rru.wiki_db
    WHERE
        mwh.snapshot = '2024-11'
    -- limit to indonesian Wikipedia
        AND rr.wiki_db == 'enwiki'
    -- limit to only revisions assigned revert risk score
        AND rev_revert_risk IS NOT NULL
    -- exclude adminstrators
        AND
        (
            event_user_groups IS NULL
            OR NOT ARRAY_CONTAINS(mwh.event_user_groups_historical, 'sysop')
                    )
        AND event_timestamp > "2024-01-01 00:00:00"
        AND event_timestamp < "2024-11-05 00:00:00"
    ),

    excl_self_reverts AS (
        SELECT
            rd.*
        FROM
            revert_risks rd
        JOIN
            wmf.mediawiki_history mwh
            ON rd.revision_id = mwh.revision_first_identity_reverting_revision_id
            AND rd.wiki_db = mwh.wiki_db
        WHERE
            snapshot = '2024-11'
            AND rd.is_revert
            -- exclude self reverts
            AND NOT rd.event_user_text = mwh.event_user_text
    )

SELECT
    *
FROM
    revert_risks
WHERE
    NOT revert_risks.is_revert
UNION ALL
SELECT
    *
FROM
    excl_self_reverts ;

============ - enwiki - ============                                                                                                                                               
 - Raw data shape: (0, 17)
 - Duplicate rows found and removed: 0 
 - Clean data shape: (0, 17)
 - Unique revision_ids: 0 | Data Shape: 0 |  Same? : -> True
 - Removing edits that are reverts from df | New Shape: (0, 17)
 - Is any revert_risk_score NA? : False
 - Is any user_edit_count NA? :  False
 - Is any time_to_revert NA? :  False
Traceback (most recent call last):
  File "/srv/home/gkyziridis/revert_risk_threshold_for_all_wikies.py", line 392, in <module>
    rc = ROC(actual=actual, probs=predicted, plot=True, name=wiki_name)
  File "/srv/home/gkyziridis/revert_risk_threshold_for_all_wikies.py", line 113, in ROC
    fpr, tpr, thresholds = roc_curve(actual, probs)
  File "/home/gkyziridis/.conda/envs/2025-05-08T10.39.42_gkyziridis/lib/python3.10/site-packages/sklearn/utils/_param_validation.py", line 216, in wrapper
    return func(*args, **kwargs)
  File "/home/gkyziridis/.conda/envs/2025-05-08T10.39.42_gkyziridis/lib/python3.10/site-packages/sklearn/metrics/_ranking.py", line 1150, in roc_curve
    fps, tps, thresholds = _binary_clf_curve(
  File "/home/gkyziridis/.conda/envs/2025-05-08T10.39.42_gkyziridis/lib/python3.10/site-packages/sklearn/metrics/_ranking.py", line 835, in _binary_clf_curve
    pos_label = _check_pos_label_consistency(pos_label, y_true)
  File "/home/gkyziridis/.conda/envs/2025-05-08T10.39.42_gkyziridis/lib/python3.10/site-packages/sklearn/utils/validation.py", line 2641, in _check_pos_label_consistency
    raise ValueError(
ValueError: y_true takes value in {} and pos_label is not specified: either make y_true take value in {0, 1} or {-1, 1} or pass pos_label explicitly.