-- Get events information from table safety_pcp_debug_e_events in dbt_yxie.
WITH
events_per_run AS (
SELECT meta_id, count(*) as event_count FROM dbt_yxie.safety_pcp_debug_e_events GROUP BY meta_id
),
-- Get runs information from table runs in public based on safety_pcp_debug_runs in dbt_yxie.
r AS (
SELECT hr_id, autonomous_miles, tracking_category, release_full, start_date FROM runs WHERE data_id in (SELECT run_id FROM dbt_yxie.safety_pcp_debug_runs)
)
-- Calculate number of events per miles.
SELECT start_date, 1.0 * sum(coalesce(event_count,0))/nullif(sum(autonomous_miles),0) as event_rate, sum(autonomous_miles) as miles, sum(coalesce(event_count,0)) as events
FROM r
LEFT JOIN events_per_run
ON r.hr_id = events_per_run.meta_id
GROUP BY 1 -- Group by column 1, release_full.
HAVING sum(autonomous_miles) > 0.1;
