SELECT
    year,
    month,
    day,
    user_agent LIKE '%iOS%' as ios,
    user_agent LIKE '%iPhone%' as iphone,
    user_agent LIKE '%Android%' as android,
    count(1) as c
FROM wmf.webrequest
WHERE user_agent LIKE('WikipediaApp%')
    AND ((parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'action') = 'mobileview' AND uri_path == '/w/api.php')
        OR (uri_path LIKE '/api/rest_v1%' AND uri_query == ''))
    AND COALESCE(x_analytics_map['wmfuuid'],
                 parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'appInstallID')) IS NOT NULL
    AND webrequest_source IN ('text')
    AND year=2017
    AND month=4
    AND day=19
GROUP BY year,
    month,
    day,
    user_agent LIKE '%iOS%',
    user_agent LIKE '%iPhone%',
    user_agent LIKE '%Android%'
ORDER BY c DESC
LIMIT 100;



year  month day ios iphone android count
2017  4 19  false false true  14683943
2017  4 19  true  false false 4957512
2017  4 19  false true  false 380654