Page MenuHomePhabricator

[REQUEST] Data for blocks made on Dutch wikipedia
Closed, ResolvedPublic

Description

What's requested: We need some numbers for blocks that are currently carried out on Dutch wikipedia (nlwiki).
Specifically:

  • Number of blocks made over the past 90 days
  • Length of blocks made
  • Type of blocks made (if applicable)

Why it's requested: This is required for the partial blocks project. Dutch wikipedia has requested to see some statistics about their current blocking practices before they decide to go for partial blocks.

When it's requested: The sooner, the better. Ideally before end of next week, 6 Sept.

Other helpful information:

Event Timeline

Niharika created this task.Aug 29 2019, 7:51 PM
Restricted Application added a subscriber: MGChecker. · View Herald TranscriptAug 29 2019, 7:51 PM
DannyS712 added a subscriber: DannyS712.

Assuming 'types of blocks' refers to account/ip/range block, and 'length of blocks made' includes a count of "indefinite" vs specific times

Restricted Application added a project: User-DannyS712. · View Herald TranscriptAug 31 2019, 5:41 AM
DannyS712 added a comment.EditedAug 31 2019, 6:56 AM

@Niharika: Going based on the nlwiki_p.ipblocks table, for entries that are not autoblocks, since June 1 00:00:00:

+---------------------+---------+--------+
| Length              | Type    | Number |
+---------------------+---------+--------+
| infinity            | ip      |  12391 |
| infinity            | range   |    408 |
| infinity            | account |    190 |
| 3 months            | ip      |     15 |
| 2 year(s)           | ip      |     15 |
| 2 year(s)           | range   |     15 |
| 1 year(s)           | ip      |     14 |
| 6 months, 1 hour    | ip      |      8 |
| 1 week              | ip      |      6 |
| 3 months            | range   |      5 |
| 1 year(s)           | range   |      5 |
| 1 month             | ip      |      2 |
| 1 month             | range   |      2 |
| 6 months, 1 hour    | range   |      2 |
| 1 week              | range   |      1 |
| 1 day               | range   |      1 |
| 1 day               | ip      |      1 |
| 3 days              | account |      1 |
| 1 month             | account |      1 |
+---------------------+---------+--------+

is this what you wanted?

@Niharika: Going based on the nlwiki_p.ipblocks table, for entries that are not autoblocks, since June 1 00:00:00:

This over-samples infinity blocks because when a block expires, it is removed from that table. The only way to get this data (I think) would be with the block log.

IMHO, Blocks should exist as wiki pages (see: T208175) which would give them an entire revision history, but that is a different issue. :)

DannyS712 added a comment.EditedSep 2 2019, 12:06 AM

From the block log:

+---------+-------------------------------------+-------+
| Type    | Expires                             | Count |
+---------+-------------------------------------+-------+
| Anon    | indefinite				| 32570 |
| Range   | indefinite				| 399   |
| Anon    | 1 day				| 124   |
| Account | infinite				| 110   |
| Account | indefinite				| 38    |
| Anon    | 3 days				| 35    |
| Anon    | 1 week				| 30    |
| Range   | indefinite				| 25    |
| Anon    | 1 day				| 24    |
| Account | infinite				| 22    |
| Range   | 1 day				| 20    |
| Anon    | 1 day				| 19    |
| Account | 1 day				| 18    |
| Anon    | 2 weeks				| 16    |
| Range   | 2 years				| 15    |
| Anon    | 2 hours				| 14    |
| Anon    | 3 days				| 13    |
| Anon    | 1 week				| 13    |
| Anon    | 2 years				| 12    |
| Anon    | 3 months				| 11    |
| Account | infinite				| 10    |
| Range   | 1 week				| 9     |
| Range   | 1 day				| 9     |
| Anon    | 1 month				| 8     |
| Anon    | 2 weeks				| 8     |
| Account | infinite				| 8     |
| Anon    | 6 months				| 7     |
| Account | infinite				| 7     |
| Range   | 1 week				| 7     |
| Anon    | 1 year				| 6     |
| Anon    | 12 hours				| 6     |
| Range   | 3 months				| 5     |
| Range   | 1 month				| 5     |
| Anon    | 1 year				| 5     |
| Account | 1 week				| 4     |
| Range   | 2 weeks				| 4     |
| Anon    | 1 hour				| 4     |
| Anon    | 1 year				| 4     |
| Anon    | 1 week				| 4     |
| Anon    | 2 years				| 4     |
| Anon    | 15 minutes				| 3     |
| Anon    | 3 months				| 3     |
| Anon    | 6 hours				| 3     |
| Account | 3 days				| 3     |
| Anon    | infinite				| 3     |
| Account | 1 month				| 3     |
| Anon    | 1 month				| 3     |
| Account | 1 hour				| 3     |
| Anon    | 3 days				| 3     |
| Account | 6 hours				| 2     |
| Account | infinite				| 2     |
| Range   | 3 days				| 2     |
| Anon    | 2 hours				| 2     |
| Anon    | 1 week				| 2     |
| Range   | 6 months				| 2     |
| Account | 2 weeks				| 2     |
| Anon    | 1 week				| 2     |
| Anon    | 1 month				| 2     |
| Range   | 2 weeks				| 2     |
| Range   | 1 year				| 2     |
| Range   | 1 year				| 2     |
| Range   | 1 month				| 2     |
| Anon    | infinite				| 2     |
| Anon    | indefinite				| 2     |
| Anon    | 1 month				| 2     |
| Anon    | 1 week				| 2     |
| Range   | 3 days				| 2     |
| Anon    | 6 hours				| 1     |
| Anon    | 3 months				| 1     |
| Account | 15 minuten				| 1     |
| Account | 1 minute				| 1     |
| Anon    | 12 hours				| 1     |
| Range   | 2 hours				| 1     |
| Account | 2 hours				| 1     |
| Range   | Thu, 27 Jun 2019 20:21:11 GMT 	| 1     |
| Anon    | 3 days				| 1     |
| Account | infinite				| 1     |
| Account | 15 minutes				| 1     |
| Anon    | infinite				| 1     |
| Account | 12 hours				| 1     |
| Anon    | infinite				| 1     |
| Anon    | infinite				| 1     |
| Range   | Sat, 03 Aug 2019 14:44:00 GMT  	| 1     |
| Account | Tue, 06 Aug 2019 21:29:51 GMT	| 1     |
| Anon    | 1 minute				| 1     |
| Anon    | 1 month				| 1     |
| Anon    | 6 months				| 1     |
| Anon    | 1 hour				| 1     |
| Range   | Mon, 24 Aug 2020 20:34:58 GMT	| 1     |
+---------+-------------------------------------+-------+
DannyS712 moved this task from Triage to Doing on the Product-Analytics board.Sep 2 2019, 12:07 AM
DannyS712 moved this task from Unsorted to In progress on the User-DannyS712 board.

From the block log:

+---------+-------------------------------------+-------+
| Type    | Expires                             | Count |
+---------+-------------------------------------+-------+
| Anon    | indefinite				| 32570 |
| Range   | indefinite				| 399   |
| Anon    | 1 day				| 124   |
| Account | infinite				| 110   |
| Account | indefinite				| 38    |
| Anon    | 3 days				| 35    |
| Anon    | 1 week				| 30    |
| Range   | indefinite				| 25    |
| Anon    | 1 day				| 24    |
| Account | infinite				| 22    |
| Range   | 1 day				| 20    |
| Anon    | 1 day				| 19    |
| Account | 1 day				| 18    |
| Anon    | 2 weeks				| 16    |
| Range   | 2 years				| 15    |
| Anon    | 2 hours				| 14    |
| Anon    | 3 days				| 13    |
| Anon    | 1 week				| 13    |
| Anon    | 2 years				| 12    |
| Anon    | 3 months				| 11    |
| Account | infinite				| 10    |
| Range   | 1 week				| 9     |
| Range   | 1 day				| 9     |
| Anon    | 1 month				| 8     |
| Anon    | 2 weeks				| 8     |
| Account | infinite				| 8     |
| Anon    | 6 months				| 7     |
| Account | infinite				| 7     |
| Range   | 1 week				| 7     |
| Anon    | 1 year				| 6     |
| Anon    | 12 hours				| 6     |
| Range   | 3 months				| 5     |
| Range   | 1 month				| 5     |
| Anon    | 1 year				| 5     |
| Account | 1 week				| 4     |
| Range   | 2 weeks				| 4     |
| Anon    | 1 hour				| 4     |
| Anon    | 1 year				| 4     |
| Anon    | 1 week				| 4     |
| Anon    | 2 years				| 4     |
| Anon    | 15 minutes				| 3     |
| Anon    | 3 months				| 3     |
| Anon    | 6 hours				| 3     |
| Account | 3 days				| 3     |
| Anon    | infinite				| 3     |
| Account | 1 month				| 3     |
| Anon    | 1 month				| 3     |
| Account | 1 hour				| 3     |
| Anon    | 3 days				| 3     |
| Account | 6 hours				| 2     |
| Account | infinite				| 2     |
| Range   | 3 days				| 2     |
| Anon    | 2 hours				| 2     |
| Anon    | 1 week				| 2     |
| Range   | 6 months				| 2     |
| Account | 2 weeks				| 2     |
| Anon    | 1 week				| 2     |
| Anon    | 1 month				| 2     |
| Range   | 2 weeks				| 2     |
| Range   | 1 year				| 2     |
| Range   | 1 year				| 2     |
| Range   | 1 month				| 2     |
| Anon    | infinite				| 2     |
| Anon    | indefinite				| 2     |
| Anon    | 1 month				| 2     |
| Anon    | 1 week				| 2     |
| Range   | 3 days				| 2     |
| Anon    | 6 hours				| 1     |
| Anon    | 3 months				| 1     |
| Account | 15 minuten				| 1     |
| Account | 1 minute				| 1     |
| Anon    | 12 hours				| 1     |
| Range   | 2 hours				| 1     |
| Account | 2 hours				| 1     |
| Range   | Thu, 27 Jun 2019 20:21:11 GMT 	| 1     |
| Anon    | 3 days				| 1     |
| Account | infinite				| 1     |
| Account | 15 minutes				| 1     |
| Anon    | infinite				| 1     |
| Account | 12 hours				| 1     |
| Anon    | infinite				| 1     |
| Anon    | infinite				| 1     |
| Range   | Sat, 03 Aug 2019 14:44:00 GMT  	| 1     |
| Account | Tue, 06 Aug 2019 21:29:51 GMT	| 1     |
| Anon    | 1 minute				| 1     |
| Anon    | 1 month				| 1     |
| Anon    | 6 months				| 1     |
| Anon    | 1 hour				| 1     |
| Range   | Mon, 24 Aug 2020 20:34:58 GMT	| 1     |
+---------+-------------------------------------+-------+

Which query you used?

Query
USE nlwiki_p;
SELECT
CASE
	WHEN log_title LIKE '%.%.%.%/%' THEN 'Range'
	WHEN log_title LIKE '%:%:%:%:%:%/%' THEN 'Range'
	WHEN log_title LIKE '%.%.%.%' THEN 'Anon'
	WHEN log_title LIKE '%:%:%:%:%:%' THEN 'Anon'
	ELSE 'Account'
END AS 'Type', logging.log_params AS 'Parameters', COUNT(*) AS 'Count' FROM logging
WHERE log_type = 'block'
AND log_action = 'block'
AND log_timestamp > 20190601000000
AND log_deleted = 0
GROUP BY
CONCAT(
  CASE
	WHEN log_title LIKE '%.%.%.%/%' THEN 'Range'
	WHEN log_title LIKE '%:%:%:%:%:%/%' THEN 'Range'
	WHEN log_title LIKE '%.%.%.%' THEN 'Anon'
	WHEN log_title LIKE '%:%:%:%:%:%' THEN 'Anon'
	ELSE 'Account'
  END,
  log_params
)
ORDER BY COUNT(*) DESC;

and then some cleanup of the parameters to just show the expiration, which is why there are some rows that are the same

Niharika added a subscriber: Ciell.Sep 3 2019, 5:30 PM

@DannyS712 Thanks a bunch for pulling these numbers! That seems like what was requested but let me check in with @Ciell.

Thanks! Let me add RonnieV and Danielle.

@RonnieV , @Ecritures
Could these numbers on the blocks on the Dutch Wikipedia help the community with rethinking the blocking policy? Maybe with consider partial blocking?
Or do we need more/different numbers?

I hope it will help the community with rethinking the blocking policy. The large number of indefinite blocks for anons and ranges should be a reason to rethink this, especially with [https://wikimania.wikimedia.org/wiki/2019:Research/Despite_the_ban:_doing_good_work_anonymously_on_Wikipedia this in mind]. The quality of edits by users of tor is about the same as the quality of all anonymous contributions.

DannyS712 closed this task as Resolved.Sep 13 2019, 3:29 AM

Closing as resolved based on rereading the above posts; if this isn't done yet please let me know