use wmf;
set hive.mapred.mode=nonstrict;

ADD JAR /home/qchris/kraken-hive-0.0.2-SNAPSHOT-06ef59a.jar;
CREATE TEMPORARY FUNCTION ua1 AS 'org.wikimedia.analytics.kraken.hive.UAParserUDF';
INSERT OVERWRITE LOCAL DIRECTORY '/home/qchris/results-family/ua_device2'
SELECT final.device_family, final.c as cnt, round((final.c/total.t) * 100, 2) as perc
FROM (
    SELECT
           device_family,  count(*) as c
    FROM(
                   SELECT
                      COALESCE( ((partial1.browser_data)["device_family"]),"")  AS device_family
                   FROM (
                        SELECT ua1(user_agent) AS browser_data
                        FROM webrequest_mobile
                        WHERE year = '2014' AND month = '03' AND day in ( '17', '18', '19', '20', '21', '22', '23' )
                   ) partial1
    ) partial3
    GROUP BY device_family
) final
INNER JOIN
(SELECT COUNT(*) as t from webrequest_mobile
WHERE year = '2014' AND month = '03' AND day in ( '17', '18', '19', '20', '21', '22', '23' )
) total
ORDER BY cnt DESC;