App UUID query

gistfile1.txt

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;