WSoR datasets/user activity first msg
This builds off of Staeiou's user_first_msg dataset by adding details about editor activity before and after they received their first message.
Location
editdb42:halfak.user_activity_first_msg
Fields
edithalfak@internproxy:~/data$ mysql -h db1047 -e "EXPLAIN user_activity_first_msg;SELECT * FROM user_activity_first_msg LIMIT 3" halfak +---------------------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------------+------------------+------+-----+---------+-------+ | user_id | int(10) unsigned | YES | | NULL | | | user_name | varbinary(255) | YES | | NULL | | | namespace | int(11) | YES | | NULL | | | reverting_edits_before | decimal(23,0) | YES | | NULL | | | reverted_reverting_edits_before | decimal(23,0) | YES | | NULL | | | add_edits_before | decimal(23,0) | YES | | NULL | | | len_added_before | decimal(32,0) | YES | | NULL | | | reverted_add_edits_before | decimal(23,0) | YES | | NULL | | | reverted_len_added_before | decimal(32,0) | YES | | NULL | | | remove_edits_before | decimal(23,0) | YES | | NULL | | | len_removed_before | decimal(32,0) | YES | | NULL | | | reverted_remove_edits_before | decimal(23,0) | YES | | NULL | | | reverted_len_remove_before | decimal(32,0) | YES | | NULL | | | noop_edits_before | decimal(23,0) | YES | | NULL | | | reverted_noop_edits_before | decimal(23,0) | YES | | NULL | | | reverting_edits_after | decimal(23,0) | YES | | NULL | | | reverted_reverting_edits_after | decimal(23,0) | YES | | NULL | | | add_edits_after | decimal(23,0) | YES | | NULL | | | len_added_after | decimal(32,0) | YES | | NULL | | | reverted_add_edits_after | decimal(23,0) | YES | | NULL | | | reverted_len_added_after | decimal(32,0) | YES | | NULL | | | remove_edits_after | decimal(23,0) | YES | | NULL | | | len_removed_after | decimal(32,0) | YES | | NULL | | | reverted_remove_edits_after | decimal(23,0) | YES | | NULL | | | reverted_len_remove_after | decimal(32,0) | YES | | NULL | | | noop_edits_after | decimal(23,0) | YES | | NULL | | | reverted_noop_edits_after | decimal(23,0) | YES | | NULL | | +---------------------------------+------------------+------+-----+---------+-------+ +---------+-----------+-----------+------------------------+---------------------------------+------------------+------------------+---------------------------+---------------------------+---------------------+--------------------+------------------------------+----------------------------+-------------------+----------------------------+-----------------------+--------------------------------+-----------------+-----------------+--------------------------+--------------------------+--------------------+-------------------+-----------------------------+---------------------------+------------------+---------------------------+ | user_id | user_name | namespace | reverting_edits_before | reverted_reverting_edits_before | add_edits_before | len_added_before | reverted_add_edits_before | reverted_len_added_before | remove_edits_before | len_removed_before | reverted_remove_edits_before | reverted_len_remove_before | noop_edits_before | reverted_noop_edits_before | reverting_edits_after | reverted_reverting_edits_after | add_edits_after | len_added_after | reverted_add_edits_after | reverted_len_added_after | remove_edits_after | len_removed_after | reverted_remove_edits_after | reverted_len_remove_after | noop_edits_after | reverted_noop_edits_after | +---------+-----------+-----------+------------------------+---------------------------------+------------------+------------------+---------------------------+---------------------------+---------------------+--------------------+------------------------------+----------------------------+-------------------+----------------------------+-----------------------+--------------------------------+-----------------+-----------------+--------------------------+--------------------------+--------------------+-------------------+-----------------------------+---------------------------+------------------+---------------------------+ | 146308 | ! | 0 | 0 | 0 | 17 | 559 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 56 | 1099 | 0 | 0 | 1 | -35 | 0 | 0 | 3 | 0 | | 146308 | ! | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | 1 | 59 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | | 146308 | ! | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 641 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +---------+-----------+-----------+------------------------+---------------------------------+------------------+------------------+---------------------------+---------------------------+---------------------+--------------------+------------------------------+----------------------------+-------------------+----------------------------+-----------------------+--------------------------------+-----------------+-----------------+--------------------------+--------------------------+--------------------+-------------------+-----------------------------+---------------------------+------------------+---------------------------+
Each row represents a user who received a message posting on his/her talk page and the activity that user engaged by namespace.
user_id: The identifier of the useruser_name: The username of the user PRIMARY KEY PARTnamespace: The namespace in which activity is being reported. PRIMARY KEY PART- Activity before receiving the message
reverting_edits_before: The number of reverting editsreverted_reverting_edits_before: The number of reverting edits that were themselves reverted.add_edits_before: The number of edits that increase rev_lenlen_added_before: The sum total length added by add editsreverted_add_edits_before: The number of add edits that were reverted.reverted_len_added_before: The sum total length added by add edits that were revertedremove_edits_before: The number of edits that decrease rev_lenlen_removed_before: The sum total length removed by remove editsreverted_remove_edits_before: The number of remove edits that were revertedreverted_len_remove_before: The sum total length removed by remove edits that were revertednoop_edits_before: The number of edits that (net) make no change in rev_lenreverted_noop_edits_before: The number of noop edits that were reverted.
- Activity after receiving the message
reverting_edits_after: he number of reverting editsreverted_reverting_edits_after: The number of reverting edits that were themselves reverted.add_edits_after: The number of edits that increase rev_lenlen_added_after: The sum total length added by add editsreverted_add_edits_after: The number of add edits that were reverted.reverted_len_added_after: The sum total length added by add edits that were revertedremove_edits_after: The number of edits that decrease rev_lenlen_removed_after: The sum total length removed by remove editsreverted_remove_edits_after: The number of remove edits that were revertedreverted_len_remove_after: The sum total length removed by remove edits that were revertednoop_edits_after: The number of edits that (net) make no change in rev_lenreverted_noop_edits_after: The number of noop edits that were reverted.
Reproduction
editDepends on:
To generate this dataset, run the following script:
CREATE TABLE halfak.user_activity_first_msg
SELECT
uf.user_id,
uf.user_name,
rlc.namespace,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NOT NULL
) AS reverting_edits_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NOT NULL AND
rvtd.revision_id IS NOT NULL
) AS reverted_reverting_edits_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change > 0
) AS add_edits_before,
SUM(IF(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change > 0,
len_change, 0
)) AS len_added_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change > 0
) AS reverted_add_edits_before,
SUM(IF(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change > 0,
len_change, 0
)) AS reverted_len_added_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change < 0
) AS remove_edits_before,
SUM(IF(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change < 0,
len_change, 0
)) AS len_removed_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change < 0
) AS reverted_remove_edits_before,
SUM(IF(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change < 0,
len_change, 0
)) AS reverted_len_remove_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change = 0
) AS noop_edits_before,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change = 0
) AS reverted_noop_edits_before,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NOT NULL
) AS reverting_edits_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NOT NULL AND
rvtd.revision_id IS NOT NULL
) AS reverted_reverting_edits_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change > 0
) AS add_edits_after,
SUM(IF(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change > 0,
len_change, 0
)) AS len_added_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change > 0
) AS reverted_add_edits_after,
SUM(IF(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change > 0,
len_change, 0
)) AS reverted_len_added_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change < 0
) AS remove_edits_after,
SUM(IF(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change < 0,
len_change, 0
)) AS len_removed_after,
SUM(
rlc.rev_timestamp < uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change < 0
) AS reverted_remove_edits_after,
SUM(IF(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change < 0,
len_change, 0
)) AS reverted_len_remove_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
len_change = 0
) AS noop_edits_after,
SUM(
rlc.rev_timestamp > uf.msg_timestamp AND
rvt.revision_id IS NULL AND
rvtd.revision_id IS NOT NULL AND
len_change = 0
) AS reverted_noop_edits_after
FROM halfak.user_first_msg uf
INNER JOIN halfak.rev_len_changed rlc
ON uf.user_name = rlc.user_text
LEFT JOIN halfak.revert_20110115 rvt
ON rvt.revision_id = rlc.rev_id
LEFT JOIN halfak.reverted_20110115 rvtd
ON rvtd.revision_id = rlc.rev_id
GROUP BY uf.user_name, rlc.namespace;
Notes
editBy adding up reverting_edits, add_edits, remove_edits and noop_edits, you'll get the total edits performed by a user.
By subtracting reverted_add_edits from add_edits you'll get the number of edits that were not reverted.