Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
App UUID query
DROP VIEW IF EXISTS app_uuid_view;
CREATE VIEW app_uuid_view AS
SELECT
CASE WHEN user_agent LIKE('%iPhone%') THEN 'iOS'
ELSE 'Android' END AS platform,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'appInstallID') AS uuid
FROM wmf_raw.webrequest
WHERE uri_query LIKE('%sections=0%')
AND uri_query LIKE('%action=mobileview%')
AND uri_query LIKE('%appInstallID%')
AND webrequest_source IN ('mobile','text')
AND user_agent LIKE('WikipediaApp%');
SELECT platform, COUNT(DISTINCT(uuid))
FROM app_uuid_view
GROUP BY platform;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.