Created
February 20, 2014 01:02
-
-
Save rhelmer/9104909 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH crash AS ( | |
SELECT json_object_field_text(processed_crash, 'cpu_name') AS cpu_name, | |
trim('"' from ( | |
string_to_array( | |
json_object_field_text(processed_crash, 'cpu_info'), '|') | |
)[2] | |
) AS core_count, | |
product_version_id, | |
signature_id, | |
reports_clean.date_processed::date, | |
reports_clean.os_name | |
FROM processed_crashes | |
JOIN reports_clean ON (processed_crashes.uuid::text = reports_clean.uuid) | |
JOIN product_versions USING (product_version_id) | |
WHERE reports_clean.date_processed | |
BETWEEN '2014-02-18 00:00:00' AND '2014-02-18 00:01:00' | |
) | |
SELECT product_version_id, cpu_name, core_count, date_processed, os_name, signature_id, count(cpu_name) as total | |
FROM crash | |
GROUP BY core_count, cpu_name, product_version_id, date_processed, os_name, signature_id | |
ORDER BY total DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment