733489 - Add daily crash ratio views to support metrics.

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[[:jberkus] Josh Berkus]

Per request of Metrics:

Since the daily_crashes matview is historically structured and hard to use, and will be getting refactored in the near future, we need to add dynamic views to present the daily crash data for Metrics' reports in a more comprehensible and future-proof way.

As such, I am creating two views, product_crash_ratio and product_os_crash_ratio, per Paula's specification.

These views have been created on StageDB and are awaiting her verification that they contain the data she needs.

Comment 1

[Paula Clemente [:paula]]

Sample queries to get the values we see on 'https://crash-stats.mozilla.com/daily?p=Firefox&v[]='


select 
  adu_date, product_name as Product, version_string as Version, adjusted_crashes as Crashes, adu_count as ADU, crash_ratio as Ratio
from product_crash_ratio 
WHERE product_name = 'Firefox'
and version_string = '10.0.2'
and adu_date between '2012-03-01' and '2012-03-07'
ORDER BY adu_date desc;



select 
  adu_date, product_name as Product, version_string as Version, os_name as OS, adjusted_crashes as Crashes, adu_count as ADU, crash_ratio as Ratio
from product_os_crash_ratio 
WHERE product_name = 'Firefox'
and version_string = '10.0.2'
and adu_date between '2012-03-01' and '2012-03-07'
and os_name in ('Windows', 'Mac OS X', 'Linux')
ORDER BY os_name desc, adu_date desc ;

Comment 2

[[:jberkus] Josh Berkus]

This is done for 2.5.1.  I don't know why it got bumped.