Jump to content

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]
select count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'

Top 10 wikis with most logged events

[edit]
select webHost, count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
group by webHost
order by count( webHost ) DESC
limit 10

Logged event types

[edit]
select event_action, count( event_action )u
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
group by event_action
order by count( event_action ) DESC

Most used input methods

[edit]
select `event_inputMethod`, count(event_inputMethod)
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
webHost like '%.org'
group by event_inputMethod
order by count( event_inputMethod ) DESC
limit 20

Most used fonts

[edit]
select `event_interfaceFont`, count(event_interfaceFont)
from `UniversalLanguageSelector_7327441`
where event_action = 'font-change' AND
webHost like '%.org'
group by event_interfaceFont
order by count( event_interfaceFont ) DESC
limit 20

Most used input methods per wiki

[edit]
select `webHost`, `event_inputMethod`, count(event_inputMethod) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
event_inputMethod <> 'system' AND
webHost like '%.org'
group by webHost, event_inputMethod
order by count( event_inputMethod ) DESC
limit 20

All wikis, sorted by wiki

[edit]
select `webHost`, `event_inputMethod`, count(event_inputMethod) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
event_inputMethod <> 'system' AND
webHost like '%.org'
group by webHost, event_inputMethod
order by webHost ASC, count DESC;

Most used fonts per wiki

[edit]
select `webHost`, `event_interfaceFont`, count(event_interfaceFont) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'font-change' AND
webHost like '%.org'
group by webHost, event_interfaceFont
order by count( event_interfaceFont ) DESC
limit 20

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!

select event_context, count( event_context )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org' AND
event_action = 'no-search-results'
group by event_context
order by count( event_context ) DESC, event_context
limit 25

On which hosts was the language not found most frequently

[edit]
select event_context, webHost, count(event_context)
from `UniversalLanguageSelector_7327441`
where webHost like '%.org' AND
event_action = 'no-search-results' AND
event_context = 'XXXX'
group by webHost
order by count(event_context)

Event counts in a single wiki

[edit]

Ordered by count, descending

select event_action, count( event_action )
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org'
group by event_action
order by count( event_action ) DESC

Event counts by user in a single wiki

[edit]

Ordered by count, descending

select event_action, count( event_action ), event_token, count(event_token)
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org'
group by event_action, event_token
order by count( event_action ) DESC

Event counts by projects in a single language

[edit]
select webHost, count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost LIKE 'gu.%'
group by webHost
order by count( webHost ) DESC

Input method switches by a single user in a single wiki

[edit]
select `event_inputMethod`, count(event_inputMethod)
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org' AND
event_token = 'xxxxx'
group by event_inputMethod

Most often changed UI languages per wiki

[edit]
select `webHost`, `event_interfaceLanguage`, count(event_interfaceLanguage) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'language-change' AND
webHost like '%.org'
group by webHost, event_interfaceLanguage
order by count( event_interfaceLanguage ) DESC
limit 20

Most often changed UI languages per wiki - all wikis, sorted by wiki

[edit]
select `webHost`, `event_interfaceLanguage`, count(event_interfaceLanguage) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'language-change' AND
webHost like '%.org'
group by webHost, event_interfaceLanguage
order by webHost ASC, count DESC;

Language selection methods

[edit]
SELECT event_languageSelectionMethod, COUNT(event_languageSelectionMethod) count
FROM
(
	SELECT event_languageSelectionMethod
	FROM `UniversalLanguageSelector_5729800`
	WHERE event_action = 'language-change' AND
	webHost LIKE '%.org'
UNION ALL
	SELECT event_languageSelectionMethod
	FROM `UniversalLanguageSelector_7327441`
	WHERE event_action = 'language-change' AND
	webHost LIKE '%.org'
) unitedTables
GROUP BY event_languageSelectionMethod
ORDER BY count DESC;