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_os'
SELECT final.os_family, final.os_major, final.os_minor, final.c as cnt, round((final.c/total.t) * 100, 2) as perc
FROM (
    SELECT
           os_family, os_minor, os_major, count(*) as c
    FROM(
        SELECT partial2.os_family, partial2.os_minor, partial2.os_major
        FROM  (
                   SELECT
                      COALESCE( ((partial1.browser_data)["os_family"]),"")  as os_family,
                      COALESCE( ((partial1.browser_data)["os_minor"]),"") as os_minor,
                      COALESCE( ((partial1.browser_data)["os_major"]),"") as os_major
                   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
        ) partial2
    ) partial3
    GROUP BY os_family, os_minor, os_major
) 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;