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_browser'
SELECT final.browser_family, final.browser_major, final.c as cnt, round((final.c/total.t) * 100, 2) as perc
FROM (
    SELECT
           browser_family, browser_major, count(*) as c
    FROM(
        SELECT partial2.browser_family, partial2.browser_major
        FROM  (
                   SELECT
                      COALESCE( ((partial1.browser_data)["browser_family"]),"")  as browser_family,
                      COALESCE( ((partial1.browser_data)["browser_major"]),"") as browser_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 browser_family, browser_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;