gistfile1.sql

-- addons
WITH crash AS (
    SELECT json_array_elements(processed_crash->'addons') AS addons,
           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-19 00:00:00'
    AND sunset_date > now()
)
SELECT product_version_id, (addons->0)::text as addon, date_processed, os_name, signature_id, count(*) as total
FROM crash
GROUP BY addon, product_version_id, date_processed, os_name, signature_id

-- core counts
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-19 00:00:00'
    AND sunset_date > now()
)
SELECT product_version_id, cpu_name, core_count, date_processed, os_name, signature_id, count(cpu_name) as total
FROM crash
WHERE cpu_name IS NOT null AND core_count IS NOT null
GROUP BY core_count, cpu_name, product_version_id, date_processed, os_name, signature_id

-- modules
WITH crash AS (
    SELECT json_array_elements(processed_crash->'json_dump'->'modules') AS modules,
           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-19 00:00:00'
    AND sunset_date > now()
)
SELECT product_version_id, (modules->'filename')::text as module, date_processed, os_name, signature_id, count(*) as total
FROM crash
GROUP BY module, product_version_id, date_processed, os_name, signature_id