Extension:UniversalLanguageSelector/EventLogging queries
From mediawiki.org
Some data from 2013 is available at http://etherpad.wikimedia.org/p/czjQFgQLJr
Schema available at meta:Schema:UniversalLanguageSelector
Number of samples
[edit ]selectcount(webHost) from`UniversalLanguageSelector_7327441` wherewebHostlike'%.org'
Top 10 wikis with most logged events
[edit ]selectwebHost,count(webHost) from`UniversalLanguageSelector_7327441` wherewebHostlike'%.org' groupbywebHost orderbycount(webHost)DESC limit10
Logged event types
[edit ]selectevent_action,count(event_action)u from`UniversalLanguageSelector_7327441` wherewebHostlike'%.org' groupbyevent_action orderbycount(event_action)DESC
Most used input methods
[edit ]select`event_inputMethod`,count(event_inputMethod) from`UniversalLanguageSelector_7327441` whereevent_action='ime-change'AND webHostlike'%.org' groupbyevent_inputMethod orderbycount(event_inputMethod)DESC limit20
Most used fonts
[edit ]select`event_interfaceFont`,count(event_interfaceFont) from`UniversalLanguageSelector_7327441` whereevent_action='font-change'AND webHostlike'%.org' groupbyevent_interfaceFont orderbycount(event_interfaceFont)DESC limit20
Most used input methods per wiki
[edit ]select`webHost`,`event_inputMethod`,count(event_inputMethod)ascount from`UniversalLanguageSelector_7327441` whereevent_action='ime-change'AND event_inputMethod<>'system'AND webHostlike'%.org' groupbywebHost,event_inputMethod orderbycount(event_inputMethod)DESC limit20
All wikis, sorted by wiki
[edit ]select`webHost`,`event_inputMethod`,count(event_inputMethod)ascount from`UniversalLanguageSelector_7327441` whereevent_action='ime-change'AND event_inputMethod<>'system'AND webHostlike'%.org' groupbywebHost,event_inputMethod orderbywebHostASC,countDESC;
Most used fonts per wiki
[edit ]select`webHost`,`event_interfaceFont`,count(event_interfaceFont)ascount from`UniversalLanguageSelector_7327441` whereevent_action='font-change'AND webHostlike'%.org' groupbywebHost,event_interfaceFont orderbycount(event_interfaceFont)DESC limit20
Top 25 language search missses
[edit ]WARNING! FOR PRIVACY REASONS THIS DATA CANNOT BE MADE AVAILABLE TO PEOPLE THAT HAVE NOT SIGNED AN NDA WITH WIKIMEDIA FOUNDATION BEFORE SANITIZING IT!
selectevent_context,count(event_context) from`UniversalLanguageSelector_7327441` wherewebHostlike'%.org'AND event_action='no-search-results' groupbyevent_context orderbycount(event_context)DESC,event_context limit25
On which hosts was the language not found most frequently
[edit ]selectevent_context,webHost,count(event_context) from`UniversalLanguageSelector_7327441` wherewebHostlike'%.org'AND event_action='no-search-results'AND event_context='XXXX' groupbywebHost orderbycount(event_context)
Event counts in a single wiki
[edit ]Ordered by count, descending
selectevent_action,count(event_action) from`UniversalLanguageSelector_7327441` wherewebHost='te.wiktionary.org' groupbyevent_action orderbycount(event_action)DESC
Event counts by user in a single wiki
[edit ]Ordered by count, descending
selectevent_action,count(event_action),event_token,count(event_token) from`UniversalLanguageSelector_7327441` wherewebHost='te.wiktionary.org' groupbyevent_action,event_token orderbycount(event_action)DESC
Event counts by projects in a single language
[edit ]selectwebHost,count(webHost) from`UniversalLanguageSelector_7327441` wherewebHostLIKE'gu.%' groupbywebHost orderbycount(webHost)DESC
Input method switches by a single user in a single wiki
[edit ]select`event_inputMethod`,count(event_inputMethod) from`UniversalLanguageSelector_7327441` wherewebHost='te.wiktionary.org'AND event_token='xxxxx' groupbyevent_inputMethod
Most often changed UI languages per wiki
[edit ]select`webHost`,`event_interfaceLanguage`,count(event_interfaceLanguage)ascount from`UniversalLanguageSelector_7327441` whereevent_action='language-change'AND webHostlike'%.org' groupbywebHost,event_interfaceLanguage orderbycount(event_interfaceLanguage)DESC limit20
Most often changed UI languages per wiki - all wikis, sorted by wiki
[edit ]select`webHost`,`event_interfaceLanguage`,count(event_interfaceLanguage)ascount from`UniversalLanguageSelector_7327441` whereevent_action='language-change'AND webHostlike'%.org' groupbywebHost,event_interfaceLanguage orderbywebHostASC,countDESC;
Language selection methods
[edit ]SELECTevent_languageSelectionMethod,COUNT(event_languageSelectionMethod)count FROM ( SELECTevent_languageSelectionMethod FROM`UniversalLanguageSelector_5729800` WHEREevent_action='language-change'AND webHostLIKE'%.org' UNIONALL SELECTevent_languageSelectionMethod FROM`UniversalLanguageSelector_7327441` WHEREevent_action='language-change'AND webHostLIKE'%.org' )unitedTables GROUPBYevent_languageSelectionMethod ORDERBYcountDESC;