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_rev.rev_user AS User_ID, COUNT(en_rev.rev_user) AS Contributions_in_English
  	FROM 		enwiki_p.revision AS en_rev
  	GROUP BY 	User_ID
) AS English USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ceb_rev.rev_user AS User_ID, COUNT(ceb_rev.rev_user) AS Contributions_in_Cebuano
  	FROM 		cebwiki_p.revision AS ceb_rev
  	GROUP BY 	User_ID
) AS Cebuano USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		sv_rev.rev_user AS User_ID, COUNT(sv_rev.rev_user) AS Contributions_in_Swedish
  	FROM 		svwiki_p.revision AS sv_rev
  	GROUP BY 	User_ID
) AS Swedish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		de_rev.rev_user AS User_ID, COUNT(de_rev.rev_user) AS Contributions_in_German
  	FROM 		dewiki_p.revision AS de_rev
  	GROUP BY 	User_ID
) AS German USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		fr_rev.rev_user AS User_ID, COUNT(fr_rev.rev_user) AS Contributions_in_French
  	FROM 		frwiki_p.revision AS fr_rev
  	GROUP BY 	User_ID
) AS French USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		nl_rev.rev_user AS User_ID, COUNT(nl_rev.rev_user) AS Contributions_in_Dutch
  	FROM 		nlwiki_p.revision AS nl_rev
  	GROUP BY 	User_ID
) AS Dutch USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ru_rev.rev_user AS User_ID, COUNT(ru_rev.rev_user) AS Contributions_in_Russian
  	FROM 		ruwiki_p.revision AS ru_rev
  	GROUP BY 	User_ID
) AS Russian USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		es_rev.rev_user AS User_ID, COUNT(es_rev.rev_user) AS Contributions_in_Spanish
  	FROM 		eswiki_p.revision AS es_rev
  	GROUP BY 	User_ID
) AS Spanish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		it_rev.rev_user AS User_ID, COUNT(it_rev.rev_user) AS Contributions_in_Italian
  	FROM 		itwiki_p.revision AS it_rev
  	GROUP BY 	User_ID
) AS Italian USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		pl_rev.rev_user AS User_ID, COUNT(pl_rev.rev_user) AS Contributions_in_Polish
  	FROM 		plwiki_p.revision AS pl_rev
  	GROUP BY 	User_ID
) AS Polish USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		war_rev.rev_user AS User_ID, COUNT(war_rev.rev_user) AS Contributions_in_Waray
  	FROM 		warwiki_p.revision AS war_rev
  	GROUP BY 	User_ID
) AS Waray USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		vi_rev.rev_user AS User_ID, COUNT(vi_rev.rev_user) AS Contributions_in_Vietnamese
  	FROM 		viwiki_p.revision AS vi_rev
  	GROUP BY 	User_ID
) AS Vietnamese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		ja_rev.rev_user AS User_ID, COUNT(ja_rev.rev_user) AS Contributions_in_Japanese
  	FROM 		jawiki_p.revision AS ja_rev
  	GROUP BY 	User_ID
) AS Japanese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		zh_rev.rev_user AS User_ID, COUNT(zh_rev.rev_user) AS Contributions_in_Chinese
  	FROM 		zhwiki_p.revision AS zh_rev
  	GROUP BY 	User_ID
) AS Chinese USING (User_ID)
LEFT OUTER JOIN
( 
  	SELECT 		pt_rev.rev_user AS User_ID, COUNT(pt_rev.rev_user) AS Contributions_in_Portuguese
  	FROM 		ptwiki_p.revision AS pt_rev
  	GROUP BY 	User_ID
) 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...