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 'Albanian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM sqwiki_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 sqwiki_p.user_properties AS proptable, sqwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Armenian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM hywiki_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 hywiki_p.user_properties AS proptable, hywiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Azerbaijani' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM azwiki_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 azwiki_p.user_properties AS proptable, azwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Belarusian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM bewiki_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 bewiki_p.user_properties AS proptable, bewiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Bosnian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM bswiki_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 bswiki_p.user_properties AS proptable, bswiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Bulgarian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM bgwiki_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 bgwiki_p.user_properties AS proptable, bgwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Croatian' as 'Language',
(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 'Czech' as 'Language',
(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 'Danish' as 'Language',
(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 'Dutch' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM nlwiki_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 nlwiki_p.user_properties AS proptable, nlwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'English' as 'Language',
(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 'Estonian' as 'Language',
(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'

UNION ALL

SELECT 'Finnish' as 'Language',
(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 'Language',
(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 'Georgian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM kawiki_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 kawiki_p.user_properties AS proptable, kawiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'German' as 'Language',
(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 'Greek' as 'Language',
(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 'Language',
(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 'Irish' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM gawiki_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 gawiki_p.user_properties AS proptable, gawiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Italian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM itwiki_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 itwiki_p.user_properties AS proptable, itwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Latvian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM lvwiki_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 lvwiki_p.user_properties AS proptable, lvwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Lithuanian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM ltwiki_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 ltwiki_p.user_properties AS proptable, ltwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Luxembourgish' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM lbwiki_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 lbwiki_p.user_properties AS proptable, lbwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Macedonian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM mkwiki_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 mkwiki_p.user_properties AS proptable, mkwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Moldovan' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM mowiki_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 mowiki_p.user_properties AS proptable, mowiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Norwegian' as 'Language',
(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 'Polish' as 'Language',
(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 'Portuguese' as 'Language',
(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 'Romanian' as 'Language',
(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 'Russian' as 'Language',
(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 'Serbian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM srwiki_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 srwiki_p.user_properties AS proptable, srwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Slovak' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM skwiki_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 skwiki_p.user_properties AS proptable, skwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Slovenian' as 'Language',
(SELECT COUNT(DISTINCT user_id) FROM slwiki_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 slwiki_p.user_properties AS proptable, slwiki_p.user AS usertable
WHERE usertable.user_id=proptable.up_user
AND proptable.up_property = 'gender'

UNION ALL

SELECT 'Spanish' as 'Language',
(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 'Swedish' as 'Language',
(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 'Turkish' as 'Language',
(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 'Ukrainian' as 'Language',
(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'

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...