gistfile1.sql

WITH crash AS (
    SELECT json_object_field_text(processed_crash, 'cpu_name') AS cpu_name,
           json_object_field_text(processed_crash, 'os_name') AS os_name,
           json_object_field_text(processed_crash, 'signature') AS signature,
           trim('"' from (
               string_to_array(
                 json_object_field_text(processed_crash, 'cpu_info'), '|')
               )[2]
           ) AS core_count
    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 01:00:00'
    AND product_name = 'Firefox'
    AND version_string = '30.0a1'
)
SELECT cpu_name, core_count, signature, count(cpu_name) as total
FROM crash
GROUP BY signature, core_count, cpu_name
ORDER BY total DESC