Editors: Female share in 38 languages

This query is marked as a draft This query has been published by Fabian.braesemann.

This query extracts the share of female editors in 38 languages

SQL

SELECT 'German' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM dewiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM dewiki_p.user_properties AS proptable, dewiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Polish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM plwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM plwiki_p.user_properties AS proptable, plwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Russian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM ruwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM ruwiki_p.user_properties AS proptable, ruwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Arabian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM arwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM arwiki_p.user_properties AS proptable, arwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Hebraic' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM hewiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM hewiki_p.user_properties AS proptable, hewiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Finnish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM fiwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM fiwiki_p.user_properties AS proptable, fiwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'French' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM frwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM frwiki_p.user_properties AS proptable, frwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Danish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM dawiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM dawiki_p.user_properties AS proptable, dawiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Turkish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM trwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM trwiki_p.user_properties AS proptable, trwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'English' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM enwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM enwiki_p.user_properties AS proptable, enwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Japanese' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM jawiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM jawiki_p.user_properties AS proptable, jawiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Czech' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM cswiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM cswiki_p.user_properties AS proptable, cswiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Swedish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM svwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM svwiki_p.user_properties AS proptable, svwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Norwegian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM nowiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM nowiki_p.user_properties AS proptable, nowiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Ukrainian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM ukwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM ukwiki_p.user_properties AS proptable, ukwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Romanian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM rowiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM rowiki_p.user_properties AS proptable, rowiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Greek' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM elwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM elwiki_p.user_properties AS proptable, elwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Hungarian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM huwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM huwiki_p.user_properties AS proptable, huwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Spanish' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM eswiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM eswiki_p.user_properties AS proptable, eswiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Kroatian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM hrwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM hrwiki_p.user_properties AS proptable, hrwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Portugese' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM ptwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM ptwiki_p.user_properties AS proptable, ptwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Estonian' as 'Country',
(SELECT COUNT(DISTINCT user_id) FROM etwiki_p.user) AS 'Number of distinct user ids',
SUM(CASE WHEN (proptable.up_property = 'gender') THEN 1 ELSE 0 END) AS 'Number of user with sex information',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'male') THEN 1 ELSE 0 END) AS 'Number of male user',
SUM(CASE WHEN (proptable.up_property = 'gender'and proptable.up_value = 'female') THEN 1 ELSE 0 END) AS 'Number of female user'
FROM etwiki_p.user_properties AS proptable, etwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...