IALs editors that also edit in other IALs.

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

For each wiki user we want to see how many edits they've done in an IAL and in any of the top Wikipedia languages.

SQL

SELECT * FROM
( 
  	SELECT 		simple_rev.rev_user AS User_ID, COUNT(simple_rev.rev_user) AS Contributions_in_Simple_English
  	FROM 		simplewiki_p.revision AS simple_rev
  	GROUP BY 	User_ID
) AS Simple_English
LEFT OUTER JOIN
( 
	SELECT 		eo_rev.rev_user AS User_ID, COUNT(eo_rev.rev_user) AS Contributions_in_Esperanto
	FROM 		eowiki_p.revision AS eo_rev
  	GROUP BY 	User_ID
) AS Esperanto USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		io_rev.rev_user AS User_ID, COUNT(io_rev.rev_user) AS Contributions_in_Ido
  	FROM 		iowiki_p.revision AS io_rev
  	GROUP BY 	User_ID
) AS Ido USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ia_rev.rev_user AS User_ID, COUNT(ia_rev.rev_user) AS Contributions_in_Interlingua
  	FROM 		iawiki_p.revision AS ia_rev
  	GROUP BY 	User_ID
) AS Interlingua USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		nov_rev.rev_user AS User_ID, COUNT(nov_rev.rev_user) AS Contributions_in_Novial
  	FROM 		novwiki_p.revision AS nov_rev
  	GROUP BY 	User_ID
) AS Novial USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ie_rev.rev_user AS User_ID, COUNT(ie_rev.rev_user) AS Contributions_in_Interlingue
  	FROM 		iewiki_p.revision AS ie_rev
  	GROUP BY 	User_ID
) AS Interlingue USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		vo_rev.rev_user AS User_ID, COUNT(vo_rev.rev_user) AS Contributions_in_Volapük
  	FROM 		vowiki_p.revision AS vo_rev
  	GROUP BY 	User_ID
) AS Volapük USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		en_user.user_id AS User_ID, en_user.user_editcount AS Contributions_in_English
  	FROM 		enwiki_p.user AS en_user
  	WHERE		en_user.user_id <= 775072
) AS English USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ceb_user.user_id AS User_ID, ceb_user.user_editcount AS Contributions_in_Cebuano
  	FROM 		cebwiki_p.user AS ceb_user
  	WHERE		ceb_user.user_id <= 775072
) AS Cebuano USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		sv_user.user_id AS User_ID, sv_user.user_editcount AS Contributions_in_Swedish
  	FROM 		svwiki_p.user AS sv_user
  	WHERE		sv_user.user_id <= 775072
) AS Swedish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		de_user.user_id AS User_ID, de_user.user_editcount AS Contributions_in_German
  	FROM 		dewiki_p.revision AS de_user
  	WHERE		de_user.user_id <= 775072
) AS German USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		fr_user.user_id AS User_ID, fr_user.user_editcount AS Contributions_in_French
  	FROM 		frwiki_p.revision AS fr_user
  	WHERE		fr_user.user_id <= 775072
) AS French USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		nl_user.user_id AS User_ID, nl_user.user_editcount AS Contributions_in_Dutch
  	FROM 		nlwiki_p.revision AS nl_user
  	WHERE		nl_user.user_id <= 775072
) AS Dutch USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ru_user.user_id AS User_ID, ru_user.user_editcount AS Contributions_in_Russian
  	FROM 		ruwiki_p.revision AS ru_user
  	WHERE		ru_user.user_id <= 775072
) AS Russian USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		es_user.user_id AS User_ID, es_user.user_editcount AS Contributions_in_Spanish
  	FROM 		eswiki_p.revision AS es_user
  	WHERE		es_user.user_id <= 775072
) AS Spanish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		it_user.user_id AS User_ID, it_user.user_editcount AS Contributions_in_Italian
  	FROM 		itwiki_p.revision AS it_user
  	WHERE		it_user.user_id <= 775072
) AS Italian USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		pl_user.user_id AS User_ID, pl_user.user_editcount AS Contributions_in_Polish
  	FROM 		plwiki_p.revision AS pl_user
  	WHERE		pl_user.user_id <= 775072
) AS Polish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		war_user.user_id AS User_ID, war_user.user_editcount AS Contributions_in_Waray
  	FROM 		warwiki_p.revision AS war_user
  	WHERE		war_user.user_id <= 775072
) AS Waray USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		vi_user.user_id AS User_ID, vi_user.user_editcount AS Contributions_in_Vietnamese
  	FROM 		viwiki_p.revision AS vi_user
  	WHERE		vi_user.user_id <= 775072
) AS Vietnamese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ja_user.user_id AS User_ID, ja_user.user_editcount AS Contributions_in_Japanese
  	FROM 		jawiki_p.revision AS ja_user
  	WHERE		ja_user.user_id <= 775072
) AS Japanese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		zh_user.user_id AS User_ID, zh_user.user_editcount AS Contributions_in_Chinese
  	FROM 		zhwiki_p.revision AS zh_user
  	WHERE		zh_user.user_id <= 775072
) AS Chinese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		pt_user.user_id AS User_ID, pt_user.user_editcount AS Contributions_in_Portuguese
  	FROM 		ptwiki_p.revision AS pt_user
  	WHERE		pt_user.user_id <= 775072
) AS Portuguese USING (User_ID);

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