adi_tableau_query.sql

# TODO
# 
# [1] restrict to valid firefox versions


SELECT DATE(TIME_SLICE(adi.bl_date, 168, 'hour', 'start')) AS "Ping Date" ,
       adi.v_prod_major AS "Product Version" ,
       l.country_name AS "Country" ,
       adi.locale AS "Locale" ,
       adi.channel AS "Release Channel" ,
       l.continent_name AS "Continent" ,
       CASE
           WHEN lower(prod_os)='windows_nt'
                AND left(trim(v_prod_os),3)='6.1' THEN 'Windows 7'
           WHEN lower(prod_os)='windows_nt'
                AND left(trim(v_prod_os),3)='6.2' THEN 'Windows 8'
           WHEN lower(prod_os)='windows_nt'
                AND left(trim(v_prod_os),3)='6.3' THEN 'Windows 8'
           WHEN lower(prod_os)='windows_nt'
                AND left(trim(v_prod_os),3)='5.1' THEN 'Windows XP'
           WHEN lower(prod_os)='windows_nt'
                AND left(trim(v_prod_os),3)='5.0' THEN 'Windows 2K'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),5)='1.3.1' THEN 'OSX Cheetah'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='10.' THEN 'OSX Snow Leopard'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='12.' THEN 'OSX Mountain Lion'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),2)='5.' THEN 'OSX Puma'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),2)='6.' THEN 'OSX Jaguar'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),2)='7.' THEN 'OSX Panther'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),2)='8.' THEN 'OSX Tiger'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),2)='9.' THEN 'OSX Leopard'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='11.' THEN 'OSX Lion'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='13.' THEN 'OSX Mavericks'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='0.1' THEN 'OSX DP'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='0.2' THEN 'OSX DP2'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='1.0' THEN 'OSX DP3'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),3)='1.1' THEN 'OSX DP4'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),5)='1.2.1' THEN 'OSX Kodiak'
           WHEN lower(prod_os)='darwin'
                AND left(trim(v_prod_os),5)='1.4.1' THEN 'OSX Cheetah'
           WHEN lower(prod_os)='linux' THEN 'Linux (all)'
           WHEN lower(prod_os)='bsd' THEN 'BSD (all)'
           WHEN lower(prod_os)='windows' THEN 'Windows (other)'
           WHEN lower(prod_os)='darwin' THEN 'OSX (other)'
           ELSE '#Unknown'
       END AS "Platform Name" ,
       sum(adi.tot_requests_on_date) AS "ADI"
FROM copy_adi_dimensional_by_date_tmary adi
LEFT JOIN
  (SELECT DISTINCT country_code,
                   country_name,
                   continent_code,
                   continent_name
   FROM locations) l ON adi.cntry_code = l.country_code
LEFT JOIN
  (SELECT TRUE AS isCurrent,
                  max(bl_date) AS max_date
   FROM copy_adi_dimensional_by_date) cur ON bl_date = max_date
WHERE lower(product) = 'firefox'
  AND datediff('day', adi.bl_date, current_date()) <= 366
GROUP BY 1,
         2,
         3,
         4,
         5,
         6,
         7