{"queries":[{"id":"last_record","compiledQueryString":"SELECT\n strftime(LAST_RECORD, '%Y-%m-%d') AS end_date,\n LPAD(CAST(DATE_PART('month', LAST_RECORD) AS VARCHAR), 2, '0') || '/' ||\n LPAD(CAST(DATE_PART('day', LAST_RECORD) AS VARCHAR), 2, '0') || '/' ||\n RIGHT(CAST(DATE_PART('year', LAST_RECORD) AS VARCHAR), 2) || ',' AS latest_record,\n LPAD(CAST(DATE_PART('month', LAST_UPDATE) AS VARCHAR), 2, '0') || '/' ||\n LPAD(CAST(DATE_PART('day', LAST_UPDATE) AS VARCHAR), 2, '0') || '/' ||\n RIGHT(CAST(DATE_PART('year', LAST_UPDATE) AS VARCHAR), 2) || ' at ' ||\n LPAD(CAST(DATE_PART('hour', LAST_UPDATE) AS VARCHAR), 2, '0') || ':' ||\n LPAD(CAST(DATE_PART('minute', LAST_UPDATE) AS VARCHAR), 2, '0') AS latest_update\nFROM crashes.crashes\nORDER BY LAST_RECORD DESC\nLIMIT 1;","inputQueryString":"SELECT\n strftime(LAST_RECORD, '%Y-%m-%d') AS end_date,\n LPAD(CAST(DATE_PART('month', LAST_RECORD) AS VARCHAR), 2, '0') || '/' ||\n LPAD(CAST(DATE_PART('day', LAST_RECORD) AS VARCHAR), 2, '0') || '/' ||\n RIGHT(CAST(DATE_PART('year', LAST_RECORD) AS VARCHAR), 2) || ',' AS latest_record,\n LPAD(CAST(DATE_PART('month', LAST_UPDATE) AS VARCHAR), 2, '0') || '/' ||\n LPAD(CAST(DATE_PART('day', LAST_UPDATE) AS VARCHAR), 2, '0') || '/' ||\n RIGHT(CAST(DATE_PART('year', LAST_UPDATE) AS VARCHAR), 2) || ' at ' ||\n LPAD(CAST(DATE_PART('hour', LAST_UPDATE) AS VARCHAR), 2, '0') || ':' ||\n LPAD(CAST(DATE_PART('minute', LAST_UPDATE) AS VARCHAR), 2, '0') AS latest_update\nFROM crashes.crashes\nORDER BY LAST_RECORD DESC\nLIMIT 1;","compiled":false,"inline":false},{"id":"age_range","compiledQueryString":"SELECT age_int\nFROM range(0, 121, 1) AS t(age_int);","inputQueryString":"SELECT age_int\nFROM range(0, 121, 1) AS t(age_int);","compiled":false,"inline":false},{"id":"unique_mode","compiledQueryString":"select \n MODE\nfrom crashes.crashes\ngroup by 1","inputQueryString":"select \n MODE\nfrom crashes.crashes\ngroup by 1","compiled":false,"inline":true},{"id":"unique_severity","compiledQueryString":"select \n SEVERITY\nfrom crashes.crashes\ngroup by 1","inputQueryString":"select \n SEVERITY\nfrom crashes.crashes\ngroup by 1","compiled":false,"inline":true},{"id":"unique_hin","compiledQueryString":"select \n GIS_ID,\n ROUTENAME\nfrom hin.hin\ngroup by all","inputQueryString":"select \n GIS_ID,\n ROUTENAME\nfrom hin.hin\ngroup by all","compiled":false,"inline":true},{"id":"period_comp_mode","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN start_date = DATE_TRUNC('year', start_date)\n AND end_date = DATE_TRUNC('year', start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM start_date)::VARCHAR\n -- Current YTD → \"YYYY YTD\"\n WHEN start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = end_date - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM (end_date - INTERVAL '1 day'))::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n modes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n ),\n current_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info)\n AND\n (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n ),\n prior_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n ),\n total_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM current_period cp\n FULL JOIN prior_period pp USING (MODE)\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN prior_start_date = DATE_TRUNC('year', prior_start_date)\n AND prior_end_date = DATE_TRUNC('year', prior_start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM prior_start_date)::VARCHAR\n -- Prior YTD → \"YYYY YTD\"\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT end_date FROM date_info) - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT\n mas.MODE,\n CASE\n WHEN mas.MODE = 'Driver' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/driver.png'\n WHEN mas.MODE = 'Passenger' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/passenger.png'\n WHEN mas.MODE = 'Pedestrian' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/pedestrian.png'\n WHEN mas.MODE = 'Bicyclist' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/bicyclist.png'\n WHEN mas.MODE = 'Motorcyclist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/motorcyclist.png'\n WHEN mas.MODE = 'Scooterist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/scooterist.png'\n WHEN mas.MODE IN ('Unknown','Other')\n THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/unknown.png'\n ELSE NULL\n END AS ICON,\n COALESCE(cp.sum_count, 0) AS current_period_sum,\n COALESCE(pp.sum_count, 0) AS prior_period_sum,\n COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0) AS difference,\n CASE\n WHEN COALESCE(cp.sum_count, 0) = 0 THEN NULL\n WHEN COALESCE(pp.sum_count, 0) != 0 THEN\n (COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0))\n / COALESCE(pp.sum_count, 0)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT prior_date_range_label FROM prior_date_label) AS prior_period_range,\n (total_current_period - total_prior_period)\n / NULLIF(total_prior_period, 0) AS total_percentage_change,\n COALESCE(cp.sum_count, 0)\n / NULLIF(total_current_period, 0) AS current_mode_percentage,\n COALESCE(pp.sum_count, 0)\n / NULLIF(total_prior_period, 0) AS prior_mode_percentage\nFROM modes_and_severities mas\nLEFT JOIN current_period cp USING (MODE)\nLEFT JOIN prior_period pp USING (MODE),\n total_counts;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN start_date = DATE_TRUNC('year', start_date)\n AND end_date = DATE_TRUNC('year', start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM start_date)::VARCHAR\n -- Current YTD → \"YYYY YTD\"\n WHEN start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = end_date - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM (end_date - INTERVAL '1 day'))::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n modes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n ),\n current_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info)\n AND\n (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n ),\n prior_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n ),\n total_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM current_period cp\n FULL JOIN prior_period pp USING (MODE)\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN prior_start_date = DATE_TRUNC('year', prior_start_date)\n AND prior_end_date = DATE_TRUNC('year', prior_start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM prior_start_date)::VARCHAR\n -- Prior YTD → \"YYYY YTD\"\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT end_date FROM date_info) - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT\n mas.MODE,\n CASE\n WHEN mas.MODE = 'Driver' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/driver.png'\n WHEN mas.MODE = 'Passenger' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/passenger.png'\n WHEN mas.MODE = 'Pedestrian' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/pedestrian.png'\n WHEN mas.MODE = 'Bicyclist' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/bicyclist.png'\n WHEN mas.MODE = 'Motorcyclist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/motorcyclist.png'\n WHEN mas.MODE = 'Scooterist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/scooterist.png'\n WHEN mas.MODE IN ('Unknown','Other')\n THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/unknown.png'\n ELSE NULL\n END AS ICON,\n COALESCE(cp.sum_count, 0) AS current_period_sum,\n COALESCE(pp.sum_count, 0) AS prior_period_sum,\n COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0) AS difference,\n CASE\n WHEN COALESCE(cp.sum_count, 0) = 0 THEN NULL\n WHEN COALESCE(pp.sum_count, 0) != 0 THEN\n (COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0))\n / COALESCE(pp.sum_count, 0)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT prior_date_range_label FROM prior_date_label) AS prior_period_range,\n (total_current_period - total_prior_period)\n / NULLIF(total_prior_period, 0) AS total_percentage_change,\n COALESCE(cp.sum_count, 0)\n / NULLIF(total_current_period, 0) AS current_mode_percentage,\n COALESCE(pp.sum_count, 0)\n / NULLIF(total_prior_period, 0) AS prior_mode_percentage\nFROM modes_and_severities mas\nLEFT JOIN current_period cp USING (MODE)\nLEFT JOIN prior_period pp USING (MODE),\n total_counts;","compiled":false,"inline":true},{"id":"barchart_mode","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n WHEN start_date = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::VARCHAR || ' YTD'\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n modes_and_severities AS (\n SELECT DISTINCT \n MODE\n FROM \n crashes.crashes\n ), \n current_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE <= (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n MODE\n ), \n prior_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= ((SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period))\n AND REPORTDATE <= ((SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period))\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n MODE\n ), \n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM \n current_period cp\n FULL JOIN \n prior_period pp \n ON cp.MODE = pp.MODE\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\n SELECT\n mas.MODE,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\n FROM modes_and_severities mas\n LEFT JOIN current_period cp ON mas.MODE = cp.MODE\n CROSS JOIN date_info di\n\n UNION ALL\n\n SELECT\n mas.MODE,\n 'Prior Period' AS period,\n COALESCE(pp.sum_count, 0) AS period_sum,\n pdl.prior_date_range_label AS period_range\n FROM modes_and_severities mas\n LEFT JOIN prior_period pp ON mas.MODE = pp.MODE\n CROSS JOIN prior_date_label pdl\n\n ORDER BY mas.MODE, period;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n WHEN start_date = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::VARCHAR || ' YTD'\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n modes_and_severities AS (\n SELECT DISTINCT \n MODE\n FROM \n crashes.crashes\n ), \n current_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE <= (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n MODE\n ), \n prior_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= ((SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period))\n AND REPORTDATE <= ((SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period))\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n MODE\n ), \n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM \n current_period cp\n FULL JOIN \n prior_period pp \n ON cp.MODE = pp.MODE\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\n SELECT\n mas.MODE,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\n FROM modes_and_severities mas\n LEFT JOIN current_period cp ON mas.MODE = cp.MODE\n CROSS JOIN date_info di\n\n UNION ALL\n\n SELECT\n mas.MODE,\n 'Prior Period' AS period,\n COALESCE(pp.sum_count, 0) AS period_sum,\n pdl.prior_date_range_label AS period_range\n FROM modes_and_severities mas\n LEFT JOIN prior_period pp ON mas.MODE = pp.MODE\n CROSS JOIN prior_date_label pdl\n\n ORDER BY mas.MODE, period;","compiled":false,"inline":true},{"id":"barchart_mode_3ytd","compiledQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n\n-- Date info, labels, and validity joined in\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date < DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.end_date - INTERVAL '1 day') AS VARCHAR), 2) || ' YTD'\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\n\nmodes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n),\n\n-- Current period sum by mode (half-open interval)\ncurrent_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n\n-- Three prior 1-year slices (T-1, T-2, T-3) over identical day-of-year windows\nprior_years AS (\n SELECT MODE, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n\n UNION ALL\n\n SELECT MODE, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n\n UNION ALL\n\n SELECT MODE, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n\n-- Average across T-1, T-2, T-3 (always divide by 3; yields NULL if range invalid because prior_years is empty)\nprior_avg AS (\n SELECT\n MODE,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY MODE\n),\n\n-- Label \"'YY-'YY YTD Avg\" for the 3-year average band\nprior_period_label AS (\n SELECT\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n ELSE NULL\n END AS label\n FROM date_info\n)\n\n-- Current period rows\nSELECT\n mas.MODE,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM modes_and_severities mas\nLEFT JOIN current_period cp ON mas.MODE = cp.MODE\nCROSS JOIN date_info di\n\nUNION ALL\n\n-- 3-year YTD average rows (nulls if invalid selection)\nSELECT\n mas.MODE,\n '3-Year Avg' AS period,\n ROUND(pa.avg_sum_count, 1) AS period_sum,\n ppl.label AS period_range\nFROM modes_and_severities mas\nLEFT JOIN prior_avg pa ON mas.MODE = pa.MODE\nCROSS JOIN prior_period_label ppl\n\nORDER BY MODE, period;","inputQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n\n-- Date info, labels, and validity joined in\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date < DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.end_date - INTERVAL '1 day') AS VARCHAR), 2) || ' YTD'\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\n\nmodes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n),\n\n-- Current period sum by mode (half-open interval)\ncurrent_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n\n-- Three prior 1-year slices (T-1, T-2, T-3) over identical day-of-year windows\nprior_years AS (\n SELECT MODE, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n\n UNION ALL\n\n SELECT MODE, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n\n UNION ALL\n\n SELECT MODE, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n\n-- Average across T-1, T-2, T-3 (always divide by 3; yields NULL if range invalid because prior_years is empty)\nprior_avg AS (\n SELECT\n MODE,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY MODE\n),\n\n-- Label \"'YY-'YY YTD Avg\" for the 3-year average band\nprior_period_label AS (\n SELECT\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n ELSE NULL\n END AS label\n FROM date_info\n)\n\n-- Current period rows\nSELECT\n mas.MODE,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM modes_and_severities mas\nLEFT JOIN current_period cp ON mas.MODE = cp.MODE\nCROSS JOIN date_info di\n\nUNION ALL\n\n-- 3-year YTD average rows (nulls if invalid selection)\nSELECT\n mas.MODE,\n '3-Year Avg' AS period,\n ROUND(pa.avg_sum_count, 1) AS period_sum,\n ppl.label AS period_range\nFROM modes_and_severities mas\nLEFT JOIN prior_avg pa ON mas.MODE = pa.MODE\nCROSS JOIN prior_period_label ppl\n\nORDER BY MODE, period;","compiled":false,"inline":true},{"id":"period_comp_mode_3ytd","compiledQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n-- Date info with full-year/YTD detection and compact formatting\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n -- Full calendar year → \"'YY\"\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2)\n -- YTD → \"'YY YTD\"\n WHEN r.start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = r.end_date - INTERVAL '1 day'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM (r.end_date - INTERVAL '1 day')) AS VARCHAR), 2)\n || ' YTD'\n -- Default formatted range\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n -- These labels stay compact\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid,\n -- Full-year flag\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN 1 ELSE 0\n END AS is_full_year\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\nmodes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n),\n-- Current period sum by mode (half-open interval)\ncurrent_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n-- Three prior 1-year slices (T-1, T-2, T-3)\nprior_years AS (\n SELECT MODE, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n UNION ALL\n SELECT MODE, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n UNION ALL\n SELECT MODE, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n),\n-- Average of those three prior years\nprior_avg AS (\n SELECT\n MODE,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY MODE\n),\n-- Totals for share and overall percentage change\ntotal_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN SUM(pa.avg_sum_count)\n ELSE NULL\n END AS total_prior_avg\n FROM current_period cp\n FULL JOIN prior_avg pa USING (MODE)\n),\n-- Prior period label: switches between \"Avg\" and \"YTD Avg\"\nprior_period_label AS (\n SELECT\n CASE\n WHEN (SELECT is_full_year FROM date_info) = 1\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' Avg'\n ELSE\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n END AS label\n FROM date_info\n)\nSELECT\n mas.MODE,\n CASE\n WHEN mas.MODE = 'Driver' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/driver.png'\n WHEN mas.MODE = 'Passenger' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/passenger.png'\n WHEN mas.MODE = 'Pedestrian' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/pedestrian.png'\n WHEN mas.MODE = 'Bicyclist' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/bicyclist.png'\n WHEN mas.MODE = 'Motorcyclist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/motorcyclist.png'\n WHEN mas.MODE = 'Scooterist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/scooterist.png'\n WHEN mas.MODE IN ('Unknown','Other')\n THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/unknown.png'\n ELSE NULL\n END AS ICON,\n COALESCE(cp.sum_count, 0) AS current_period_sum,\n ROUND(pa.avg_sum_count, 1) AS prior_3yr_avg_sum,\n CASE WHEN pa.avg_sum_count IS NOT NULL\n THEN ROUND(COALESCE(cp.sum_count, 0) - pa.avg_sum_count, 1)\n ELSE NULL\n END AS difference,\n CASE\n WHEN pa.avg_sum_count IS NOT NULL AND pa.avg_sum_count != 0\n THEN (COALESCE(cp.sum_count, 0) - ROUND(pa.avg_sum_count, 1)) / ROUND(pa.avg_sum_count, 1)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT label FROM prior_period_label) AS prior_period_range,\n (SELECT current_year_label FROM date_info) AS current_year_label,\n (SELECT prior_year_label FROM date_info) AS prior_year_label,\n CASE\n WHEN total_prior_avg IS NOT NULL AND total_prior_avg != 0\n THEN (total_current_period - ROUND(total_prior_avg, 1)) / ROUND(total_prior_avg, 1)\n ELSE NULL\n END AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_mode_percentage,\n CASE WHEN total_prior_avg IS NOT NULL\n THEN pa.avg_sum_count / NULLIF(total_prior_avg, 0)\n ELSE NULL\n END AS prior_mode_percentage\nFROM modes_and_severities mas\nLEFT JOIN current_period cp USING (MODE)\nLEFT JOIN prior_avg pa USING (MODE),\n total_counts;","inputQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n-- Date info with full-year/YTD detection and compact formatting\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n -- Full calendar year → \"'YY\"\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2)\n -- YTD → \"'YY YTD\"\n WHEN r.start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = r.end_date - INTERVAL '1 day'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM (r.end_date - INTERVAL '1 day')) AS VARCHAR), 2)\n || ' YTD'\n -- Default formatted range\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n -- These labels stay compact\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid,\n -- Full-year flag\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN 1 ELSE 0\n END AS is_full_year\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\nmodes_and_severities AS (\n SELECT DISTINCT MODE\n FROM crashes.crashes\n),\n-- Current period sum by mode (half-open interval)\ncurrent_period AS (\n SELECT \n MODE,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY MODE\n),\n-- Three prior 1-year slices (T-1, T-2, T-3)\nprior_years AS (\n SELECT MODE, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n UNION ALL\n SELECT MODE, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n UNION ALL\n SELECT MODE, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value} AND (\n CASE WHEN ${inputs.min_age.value} <> 0 AND ${inputs.max_age.value} = 120\n THEN 119 ELSE ${inputs.max_age.value} END\n )\n GROUP BY MODE\n),\n-- Average of those three prior years\nprior_avg AS (\n SELECT\n MODE,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY MODE\n),\n-- Totals for share and overall percentage change\ntotal_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN SUM(pa.avg_sum_count)\n ELSE NULL\n END AS total_prior_avg\n FROM current_period cp\n FULL JOIN prior_avg pa USING (MODE)\n),\n-- Prior period label: switches between \"Avg\" and \"YTD Avg\"\nprior_period_label AS (\n SELECT\n CASE\n WHEN (SELECT is_full_year FROM date_info) = 1\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' Avg'\n ELSE\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n END AS label\n FROM date_info\n)\nSELECT\n mas.MODE,\n CASE\n WHEN mas.MODE = 'Driver' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/driver.png'\n WHEN mas.MODE = 'Passenger' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/passenger.png'\n WHEN mas.MODE = 'Pedestrian' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/pedestrian.png'\n WHEN mas.MODE = 'Bicyclist' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/bicyclist.png'\n WHEN mas.MODE = 'Motorcyclist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/motorcyclist.png'\n WHEN mas.MODE = 'Scooterist*' THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/scooterist.png'\n WHEN mas.MODE IN ('Unknown','Other')\n THEN 'https://raw.githubusercontent.com/rafaelmorenoco/Crash-Injury-Dashboard-Backend/main/Icons/unknown.png'\n ELSE NULL\n END AS ICON,\n COALESCE(cp.sum_count, 0) AS current_period_sum,\n ROUND(pa.avg_sum_count, 1) AS prior_3yr_avg_sum,\n CASE WHEN pa.avg_sum_count IS NOT NULL\n THEN ROUND(COALESCE(cp.sum_count, 0) - pa.avg_sum_count, 1)\n ELSE NULL\n END AS difference,\n CASE\n WHEN pa.avg_sum_count IS NOT NULL AND pa.avg_sum_count != 0\n THEN (COALESCE(cp.sum_count, 0) - ROUND(pa.avg_sum_count, 1)) / ROUND(pa.avg_sum_count, 1)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT label FROM prior_period_label) AS prior_period_range,\n (SELECT current_year_label FROM date_info) AS current_year_label,\n (SELECT prior_year_label FROM date_info) AS prior_year_label,\n CASE\n WHEN total_prior_avg IS NOT NULL AND total_prior_avg != 0\n THEN (total_current_period - ROUND(total_prior_avg, 1)) / ROUND(total_prior_avg, 1)\n ELSE NULL\n END AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_mode_percentage,\n CASE WHEN total_prior_avg IS NOT NULL\n THEN pa.avg_sum_count / NULLIF(total_prior_avg, 0)\n ELSE NULL\n END AS prior_mode_percentage\nFROM modes_and_severities mas\nLEFT JOIN current_period cp USING (MODE)\nLEFT JOIN prior_avg pa USING (MODE),\n total_counts;","compiled":false,"inline":true},{"id":"period_comp_severity","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN start_date = DATE_TRUNC('year', start_date)\n AND end_date = DATE_TRUNC('year', start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM start_date)::VARCHAR\n -- Current YTD → \"YYYY YTD\"\n WHEN start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = end_date - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM (end_date - INTERVAL '1 day'))::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0)\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n severities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n ),\n current_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n ),\n prior_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n ),\n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM current_period cp\n FULL JOIN prior_period pp USING (SEVERITY)\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN prior_start_date = DATE_TRUNC('year', prior_start_date)\n AND prior_end_date = DATE_TRUNC('year', prior_start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM prior_start_date)::VARCHAR\n -- Prior YTD → \"YYYY YTD\"\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT end_date FROM date_info) - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT \n s.SEVERITY,\n COALESCE(cp.sum_count, 0) AS current_period_sum, \n COALESCE(pp.sum_count, 0) AS prior_period_sum, \n COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0) AS difference,\n CASE \n WHEN COALESCE(cp.sum_count, 0) = 0 THEN NULL\n WHEN COALESCE(pp.sum_count, 0) <> 0\n THEN (COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0)) / COALESCE(pp.sum_count, 0)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT prior_date_range_label FROM prior_date_label) AS prior_period_range,\n (total_current_period - total_prior_period)\n / NULLIF(total_prior_period, 0) AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_severity_percentage,\n COALESCE(pp.sum_count, 0) / NULLIF(total_prior_period, 0) AS prior_severity_percentage\nFROM severities s\nLEFT JOIN current_period cp USING (SEVERITY)\nLEFT JOIN prior_period pp USING (SEVERITY),\n total_counts;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN start_date = DATE_TRUNC('year', start_date)\n AND end_date = DATE_TRUNC('year', start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM start_date)::VARCHAR\n -- Current YTD → \"YYYY YTD\"\n WHEN start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = end_date - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM (end_date - INTERVAL '1 day'))::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0)\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n severities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n ),\n current_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n ),\n prior_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE BETWEEN\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n ),\n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM current_period cp\n FULL JOIN prior_period pp USING (SEVERITY)\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n -- Full calendar year → \"YYYY\"\n WHEN prior_start_date = DATE_TRUNC('year', prior_start_date)\n AND prior_end_date = DATE_TRUNC('year', prior_start_date) + INTERVAL '1 year'\n THEN EXTRACT(YEAR FROM prior_start_date)::VARCHAR\n -- Prior YTD → \"YYYY YTD\"\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT end_date FROM date_info) - INTERVAL '1 day'\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n -- Default formatted range\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT \n s.SEVERITY,\n COALESCE(cp.sum_count, 0) AS current_period_sum, \n COALESCE(pp.sum_count, 0) AS prior_period_sum, \n COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0) AS difference,\n CASE \n WHEN COALESCE(cp.sum_count, 0) = 0 THEN NULL\n WHEN COALESCE(pp.sum_count, 0) <> 0\n THEN (COALESCE(cp.sum_count, 0) - COALESCE(pp.sum_count, 0)) / COALESCE(pp.sum_count, 0)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT prior_date_range_label FROM prior_date_label) AS prior_period_range,\n (total_current_period - total_prior_period)\n / NULLIF(total_prior_period, 0) AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_severity_percentage,\n COALESCE(pp.sum_count, 0) / NULLIF(total_prior_period, 0) AS prior_severity_percentage\nFROM severities s\nLEFT JOIN current_period cp USING (SEVERITY)\nLEFT JOIN prior_period pp USING (SEVERITY),\n total_counts;","compiled":false,"inline":true},{"id":"period_comp_severity_3ytd","compiledQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0\n ELSE 1\n END AS is_valid\n FROM report_date_range\n),\n-- Date info with full-year/YTD detection and compact formatting\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n -- Full calendar year → \"'YY\"\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2)\n -- YTD → \"'YY YTD\"\n WHEN r.start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = r.end_date - INTERVAL '1 day'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM (r.end_date - INTERVAL '1 day')) AS VARCHAR), 2)\n || ' YTD'\n -- Default formatted range\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n -- Compact year labels\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid,\n -- Full-year flag\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN 1 ELSE 0\n END AS is_full_year\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\nseverities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n),\n-- Current period sum by severity\ncurrent_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n-- Three prior 1-year slices (T-1, T-2, T-3)\nprior_years AS (\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n UNION ALL\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n UNION ALL\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n-- Average across T-1, T-2, T-3\nprior_avg AS (\n SELECT\n SEVERITY,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY SEVERITY\n),\n-- Totals for share and overall percentage change\ntotal_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN SUM(pa.avg_sum_count)\n ELSE NULL\n END AS total_prior_avg\n FROM current_period cp\n FULL JOIN prior_avg pa USING (SEVERITY)\n),\n-- Prior period label: switches between \"Avg\" and \"YTD Avg\"\nprior_period_label AS (\n SELECT\n CASE\n WHEN (SELECT is_full_year FROM date_info) = 1\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' Avg'\n ELSE\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n END AS label\n FROM date_info\n)\nSELECT \n s.SEVERITY,\n COALESCE(cp.sum_count, 0) AS current_period_sum, \n ROUND(pa.avg_sum_count, 1) AS prior_3yr_avg_sum, \n CASE WHEN pa.avg_sum_count IS NOT NULL\n THEN ROUND(COALESCE(cp.sum_count, 0) - pa.avg_sum_count, 1)\n ELSE NULL\n END AS difference,\n CASE\n WHEN pa.avg_sum_count IS NOT NULL AND pa.avg_sum_count != 0\n THEN (COALESCE(cp.sum_count, 0) - ROUND(pa.avg_sum_count,1)) / ROUND(pa.avg_sum_count, 1)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT label FROM prior_period_label) AS prior_period_range,\n CASE\n WHEN total_prior_avg IS NOT NULL AND total_prior_avg != 0\n THEN (total_current_period - ROUND(total_prior_avg, 1)) / ROUND(total_prior_avg, 1)\n ELSE NULL\n END AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_severity_percentage,\n CASE WHEN total_prior_avg IS NOT NULL\n THEN pa.avg_sum_count / NULLIF(total_prior_avg, 0)\n ELSE NULL\n END AS prior_severity_percentage\nFROM \n severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nLEFT JOIN prior_avg pa ON s.SEVERITY = pa.SEVERITY,\n total_counts;","inputQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0\n ELSE 1\n END AS is_valid\n FROM report_date_range\n),\n-- Date info with full-year/YTD detection and compact formatting\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n -- Full calendar year → \"'YY\"\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2)\n -- YTD → \"'YY YTD\"\n WHEN r.start_date = DATE_TRUNC('year', CURRENT_DATE)\n AND '${inputs.date_range.end}'::DATE = r.end_date - INTERVAL '1 day'\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM (r.end_date - INTERVAL '1 day')) AS VARCHAR), 2)\n || ' YTD'\n -- Default formatted range\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n -- Compact year labels\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid,\n -- Full-year flag\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date = DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN 1 ELSE 0\n END AS is_full_year\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\nseverities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n),\n-- Current period sum by severity\ncurrent_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n-- Three prior 1-year slices (T-1, T-2, T-3)\nprior_years AS (\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n UNION ALL\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n UNION ALL\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n-- Average across T-1, T-2, T-3\nprior_avg AS (\n SELECT\n SEVERITY,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY SEVERITY\n),\n-- Totals for share and overall percentage change\ntotal_counts AS (\n SELECT\n SUM(cp.sum_count) AS total_current_period,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN SUM(pa.avg_sum_count)\n ELSE NULL\n END AS total_prior_avg\n FROM current_period cp\n FULL JOIN prior_avg pa USING (SEVERITY)\n),\n-- Prior period label: switches between \"Avg\" and \"YTD Avg\"\nprior_period_label AS (\n SELECT\n CASE\n WHEN (SELECT is_full_year FROM date_info) = 1\n THEN\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' Avg'\n ELSE\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '⁃' ||\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n END AS label\n FROM date_info\n)\nSELECT \n s.SEVERITY,\n COALESCE(cp.sum_count, 0) AS current_period_sum, \n ROUND(pa.avg_sum_count, 1) AS prior_3yr_avg_sum, \n CASE WHEN pa.avg_sum_count IS NOT NULL\n THEN ROUND(COALESCE(cp.sum_count, 0) - pa.avg_sum_count, 1)\n ELSE NULL\n END AS difference,\n CASE\n WHEN pa.avg_sum_count IS NOT NULL AND pa.avg_sum_count != 0\n THEN (COALESCE(cp.sum_count, 0) - ROUND(pa.avg_sum_count,1)) / ROUND(pa.avg_sum_count, 1)\n ELSE NULL\n END AS percentage_change,\n (SELECT date_range_label FROM date_info) AS current_period_range,\n (SELECT label FROM prior_period_label) AS prior_period_range,\n CASE\n WHEN total_prior_avg IS NOT NULL AND total_prior_avg != 0\n THEN (total_current_period - ROUND(total_prior_avg, 1)) / ROUND(total_prior_avg, 1)\n ELSE NULL\n END AS total_percentage_change,\n COALESCE(cp.sum_count, 0) / NULLIF(total_current_period, 0) AS current_severity_percentage,\n CASE WHEN total_prior_avg IS NOT NULL\n THEN pa.avg_sum_count / NULLIF(total_prior_avg, 0)\n ELSE NULL\n END AS prior_severity_percentage\nFROM \n severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nLEFT JOIN prior_avg pa ON s.SEVERITY = pa.SEVERITY,\n total_counts;","compiled":false,"inline":true},{"id":"barchart_severity","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n WHEN start_date = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::VARCHAR || ' YTD'\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n severities AS (\n SELECT DISTINCT \n SEVERITY\n FROM \n crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value} \n ), \n current_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE <= (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n SEVERITY\n ), \n prior_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (\n (SELECT start_date FROM offset_period) - (SELECT interval_offset FROM offset_period)\n )\n AND REPORTDATE <= (\n (SELECT end_date FROM offset_period) - (SELECT interval_offset FROM offset_period)\n )\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n SEVERITY\n ), \n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM \n current_period cp\n FULL JOIN \n prior_period pp \n ON cp.SEVERITY = pp.SEVERITY\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT\n s.SEVERITY,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nCROSS JOIN date_info di\n\nUNION ALL\n\nSELECT\n s.SEVERITY,\n 'Prior Period' AS period,\n COALESCE(pp.sum_count, 0) AS period_sum,\n pdl.prior_date_range_label AS period_range\nFROM severities s\nLEFT JOIN prior_period pp ON s.SEVERITY = pp.SEVERITY\nCROSS JOIN prior_date_label pdl\n\nORDER BY s.SEVERITY, period;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n ),\n date_info AS (\n SELECT\n start_date,\n end_date,\n CASE\n WHEN start_date = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::VARCHAR || ' YTD'\n ELSE\n strftime(start_date, '%m/%d/%y')\n || '-'\n || strftime(end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n offset_period AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '5 year' THEN (SELECT 1/0) -- guard: >5 yrs\n WHEN end_date > start_date + INTERVAL '4 year' THEN INTERVAL '5 year'\n WHEN end_date > start_date + INTERVAL '3 year' THEN INTERVAL '4 year'\n WHEN end_date > start_date + INTERVAL '2 year' THEN INTERVAL '3 year'\n WHEN end_date > start_date + INTERVAL '1 year' THEN INTERVAL '2 year'\n ELSE INTERVAL '1 year'\n END AS interval_offset\n FROM date_info\n ),\n severities AS (\n SELECT DISTINCT \n SEVERITY\n FROM \n crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value} \n ), \n current_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE <= (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n SEVERITY\n ), \n prior_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM \n crashes.crashes \n WHERE \n SEVERITY IN ${inputs.multi_severity.value} \n AND REPORTDATE >= (\n (SELECT start_date FROM offset_period) - (SELECT interval_offset FROM offset_period)\n )\n AND REPORTDATE <= (\n (SELECT end_date FROM offset_period) - (SELECT interval_offset FROM offset_period)\n )\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0 \n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY \n SEVERITY\n ), \n total_counts AS (\n SELECT \n SUM(cp.sum_count) AS total_current_period,\n SUM(pp.sum_count) AS total_prior_period\n FROM \n current_period cp\n FULL JOIN \n prior_period pp \n ON cp.SEVERITY = pp.SEVERITY\n ),\n prior_date_info AS (\n SELECT\n (SELECT start_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_start_date,\n (SELECT end_date FROM date_info) - (SELECT interval_offset FROM offset_period) AS prior_end_date\n ),\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', '${inputs.date_range.end}'::DATE)\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN EXTRACT(YEAR FROM prior_end_date)::VARCHAR || ' YTD'\n ELSE\n strftime(prior_start_date, '%m/%d/%y')\n || '-'\n || strftime(prior_end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS prior_date_range_label\n FROM prior_date_info\n )\nSELECT\n s.SEVERITY,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nCROSS JOIN date_info di\n\nUNION ALL\n\nSELECT\n s.SEVERITY,\n 'Prior Period' AS period,\n COALESCE(pp.sum_count, 0) AS period_sum,\n pdl.prior_date_range_label AS period_range\nFROM severities s\nLEFT JOIN prior_period pp ON s.SEVERITY = pp.SEVERITY\nCROSS JOIN prior_date_label pdl\n\nORDER BY s.SEVERITY, period;","compiled":false,"inline":true},{"id":"barchart_severity_3ytd","compiledQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n\n-- Date info, labels, and validity joined in\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date < DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.end_date - INTERVAL '1 day') AS VARCHAR), 2) || ' YTD'\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\n\nseverities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n),\n\n-- Current period sum by severity (half-open interval)\ncurrent_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n\n-- Three prior 1-year slices (T-1, T-2, T-3) over identical day-of-year windows\nprior_years AS (\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n\n UNION ALL\n\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n\n UNION ALL\n\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n\n-- Average across T-1, T-2, T-3 (always divide by 3; yields NULL if range invalid)\nprior_avg AS (\n SELECT\n SEVERITY,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY SEVERITY\n),\n\n-- Label \"'YY-'YY YTD Avg\" for the 3-year average band\nprior_period_label AS (\n SELECT\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n ELSE NULL\n END AS label\n FROM date_info\n)\n\n-- Current period rows\nSELECT\n s.SEVERITY,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nCROSS JOIN date_info di\n\nUNION ALL\n\n-- 3-year YTD average rows (nulls if invalid selection)\nSELECT\n s.SEVERITY,\n '3-Year Avg' AS period,\n ROUND(pa.avg_sum_count, 0) AS period_sum,\n ppl.label AS period_range\nFROM severities s\nLEFT JOIN prior_avg pa ON s.SEVERITY = pa.SEVERITY\nCROSS JOIN prior_period_label ppl\n\nORDER BY SEVERITY, period;","inputQueryString":"WITH \n-- Normalize the user-provided dates to an exclusive end_date (half-open window)\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE\n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)::DATE + INTERVAL '1 day'\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS end_date,\n '${inputs.date_range.start}'::DATE AS start_date\n),\n\n-- Validation flag: 1 = valid, 0 = invalid\nvalidate_range AS (\n SELECT\n start_date,\n end_date,\n CASE \n WHEN end_date > start_date + INTERVAL '1 year' THEN 0 -- exceeds 1 year span\n WHEN EXTRACT(YEAR FROM start_date) <> EXTRACT(YEAR FROM end_date - INTERVAL '1 day')\n THEN 0 -- crosses calendar years\n ELSE 1 -- valid\n END AS is_valid\n FROM report_date_range\n),\n\n-- Date info, labels, and validity joined in\ndate_info AS (\n SELECT\n r.start_date,\n r.end_date,\n CASE\n WHEN r.start_date = DATE_TRUNC('year', r.start_date)\n AND r.end_date < DATE_TRUNC('year', r.start_date) + INTERVAL '1 year'\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.end_date - INTERVAL '1 day') AS VARCHAR), 2) || ' YTD'\n ELSE\n strftime(r.start_date, '%m/%d/%y')\n || '-'\n || strftime(r.end_date - INTERVAL '1 day', '%m/%d/%y')\n END AS date_range_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(EXTRACT(YEAR FROM r.start_date) - 1 AS VARCHAR), 2) AS prior_year_label,\n (r.end_date - r.start_date) AS date_range_days,\n v.is_valid\n FROM report_date_range r\n JOIN validate_range v ON 1=1\n),\n\nseverities AS (\n SELECT DISTINCT SEVERITY\n FROM crashes.crashes\n WHERE SEVERITY IN ${inputs.multi_severity.value}\n),\n\n-- Current period sum by severity (half-open interval)\ncurrent_period AS (\n SELECT \n SEVERITY,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= (SELECT start_date FROM date_info)\n AND REPORTDATE < (SELECT end_date FROM date_info)\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n\n-- Three prior 1-year slices (T-1, T-2, T-3) over identical day-of-year windows\nprior_years AS (\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 1 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '1 year'\n AND REPORTDATE < di.end_date - INTERVAL '1 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n\n UNION ALL\n\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 2 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '2 year'\n AND REPORTDATE < di.end_date - INTERVAL '2 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n\n UNION ALL\n\n SELECT SEVERITY, SUM(COUNT) AS sum_count, 3 AS yr_offset\n FROM crashes.crashes\n JOIN date_info di ON 1=1\n WHERE di.is_valid = 1\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE >= di.start_date - INTERVAL '3 year'\n AND REPORTDATE < di.end_date - INTERVAL '3 year'\n AND AGE BETWEEN ${inputs.min_age.value}\n AND (\n CASE \n WHEN ${inputs.min_age.value} <> 0\n AND ${inputs.max_age.value} = 120\n THEN 119\n ELSE ${inputs.max_age.value}\n END\n )\n GROUP BY SEVERITY\n),\n\n-- Average across T-1, T-2, T-3 (always divide by 3; yields NULL if range invalid)\nprior_avg AS (\n SELECT\n SEVERITY,\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN (\n COALESCE(MAX(CASE WHEN yr_offset=1 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=2 THEN sum_count END),0) +\n COALESCE(MAX(CASE WHEN yr_offset=3 THEN sum_count END),0)\n ) / 3.0\n ELSE NULL\n END AS avg_sum_count\n FROM prior_years\n GROUP BY SEVERITY\n),\n\n-- Label \"'YY-'YY YTD Avg\" for the 3-year average band\nprior_period_label AS (\n SELECT\n CASE WHEN (SELECT is_valid FROM date_info) = 1\n THEN '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(EXTRACT(YEAR FROM start_date) - 1 AS VARCHAR), 2)\n || ' YTD Avg'\n ELSE NULL\n END AS label\n FROM date_info\n)\n\n-- Current period rows\nSELECT\n s.SEVERITY,\n 'Current Period' AS period,\n COALESCE(cp.sum_count, 0) AS period_sum,\n di.date_range_label AS period_range\nFROM severities s\nLEFT JOIN current_period cp ON s.SEVERITY = cp.SEVERITY\nCROSS JOIN date_info di\n\nUNION ALL\n\n-- 3-year YTD average rows (nulls if invalid selection)\nSELECT\n s.SEVERITY,\n '3-Year Avg' AS period,\n ROUND(pa.avg_sum_count, 0) AS period_sum,\n ppl.label AS period_range\nFROM severities s\nLEFT JOIN prior_avg pa ON s.SEVERITY = pa.SEVERITY\nCROSS JOIN prior_period_label ppl\n\nORDER BY SEVERITY, period;","compiled":false,"inline":true},{"id":"yoy_text_fatal","compiledQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Jan 1–9 freeze: exclusive end = Jan 1 of current year\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current period: Jan 1 of the year before the exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Prior period: shift back one full year\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS prior_year_start,\n (dr.max_report_date_excl - INTERVAL '1 year') AS prior_year_end_excl,\n -- Year labels\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS year_prior,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\nyearly_counts AS (\n SELECT\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS current_year_sum,\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.prior_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS prior_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n)\nSELECT\n 'Fatal' AS severity,\n yc.current_year_sum,\n yc.prior_year_sum,\n ABS(yc.current_year_sum - yc.prior_year_sum) AS difference,\n CASE WHEN yc.prior_year_sum <> 0\n THEN ((yc.current_year_sum - yc.prior_year_sum)::numeric / yc.prior_year_sum)\n ELSE NULL END AS percentage_change,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'an increase of'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'more'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n p.year_prior,\n CASE WHEN yc.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN yc.current_year_sum = 1 THEN 'fatality' ELSE 'fatalities' END AS fatality,\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM yearly_counts yc\nCROSS JOIN params p;","inputQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Jan 1–9 freeze: exclusive end = Jan 1 of current year\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current period: Jan 1 of the year before the exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Prior period: shift back one full year\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS prior_year_start,\n (dr.max_report_date_excl - INTERVAL '1 year') AS prior_year_end_excl,\n -- Year labels\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS year_prior,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\nyearly_counts AS (\n SELECT\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS current_year_sum,\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.prior_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS prior_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n)\nSELECT\n 'Fatal' AS severity,\n yc.current_year_sum,\n yc.prior_year_sum,\n ABS(yc.current_year_sum - yc.prior_year_sum) AS difference,\n CASE WHEN yc.prior_year_sum <> 0\n THEN ((yc.current_year_sum - yc.prior_year_sum)::numeric / yc.prior_year_sum)\n ELSE NULL END AS percentage_change,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'an increase of'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'more'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n p.year_prior,\n CASE WHEN yc.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN yc.current_year_sum = 1 THEN 'fatality' ELSE 'fatalities' END AS fatality,\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM yearly_counts yc\nCROSS JOIN params p;","compiled":false,"inline":true},{"id":"yoy_text_fatal_3ytd","compiledQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Freeze exclusive end to Jan 1 during Jan 1–9\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current YTD window: Jan 1 of the year before exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Year label for current year\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\n-- Current YTD fatal count\ncurrent_ytd AS (\n SELECT\n SUM(COUNT) AS current_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n),\n-- Three prior YTD periods (shift exclusive window back 1, 2, 3 years)\nprior_years AS (\n SELECT\n 1 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '1 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '1 year'\n UNION ALL\n SELECT\n 2 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '2 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '2 year'\n UNION ALL\n SELECT\n 3 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '3 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '3 year'\n),\n-- Average of the three prior YTDs\nprior_avg AS (\n SELECT\n (COALESCE(MAX(CASE WHEN yr_offset = 1 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 2 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 3 THEN sum_count END), 0)\n ) / 3.0 AS prior_3yr_avg_sum\n FROM prior_years\n)\nSELECT\n 'Fatal' AS severity,\n cy.current_year_sum,\n pa.prior_3yr_avg_sum,\n ABS(cy.current_year_sum - pa.prior_3yr_avg_sum) AS difference,\n CASE WHEN pa.prior_3yr_avg_sum <> 0\n THEN (cy.current_year_sum - pa.prior_3yr_avg_sum) / pa.prior_3yr_avg_sum\n ELSE NULL END AS percentage_change,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'an increase of'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'more'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n '''' || RIGHT(CAST(p.current_year AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(p.current_year - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2)\n || ' YTD Avg' AS prior_period_label,\n '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2) AS last_year_label,\n CASE WHEN cy.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN cy.current_year_sum = 1 THEN 'fatality' ELSE 'fatalities' END AS fatality,\n -- Display last included date (exclusive end minus 1 day)\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM current_ytd cy\nCROSS JOIN prior_avg pa\nCROSS JOIN params p;","inputQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Freeze exclusive end to Jan 1 during Jan 1–9\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current YTD window: Jan 1 of the year before exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Year label for current year\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\n-- Current YTD fatal count\ncurrent_ytd AS (\n SELECT\n SUM(COUNT) AS current_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n),\n-- Three prior YTD periods (shift exclusive window back 1, 2, 3 years)\nprior_years AS (\n SELECT\n 1 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '1 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '1 year'\n UNION ALL\n SELECT\n 2 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '2 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '2 year'\n UNION ALL\n SELECT\n 3 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Fatal'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '3 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '3 year'\n),\n-- Average of the three prior YTDs\nprior_avg AS (\n SELECT\n (COALESCE(MAX(CASE WHEN yr_offset = 1 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 2 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 3 THEN sum_count END), 0)\n ) / 3.0 AS prior_3yr_avg_sum\n FROM prior_years\n)\nSELECT\n 'Fatal' AS severity,\n cy.current_year_sum,\n pa.prior_3yr_avg_sum,\n ABS(cy.current_year_sum - pa.prior_3yr_avg_sum) AS difference,\n CASE WHEN pa.prior_3yr_avg_sum <> 0\n THEN (cy.current_year_sum - pa.prior_3yr_avg_sum) / pa.prior_3yr_avg_sum\n ELSE NULL END AS percentage_change,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'an increase of'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'more'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n '''' || RIGHT(CAST(p.current_year AS VARCHAR), 2) AS current_year_label,\n '''' || RIGHT(CAST(p.current_year - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2)\n || ' YTD Avg' AS prior_period_label,\n '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2) AS last_year_label,\n CASE WHEN cy.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN cy.current_year_sum = 1 THEN 'fatality' ELSE 'fatalities' END AS fatality,\n -- Display last included date (exclusive end minus 1 day)\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM current_ytd cy\nCROSS JOIN prior_avg pa\nCROSS JOIN params p;","compiled":false,"inline":true},{"id":"yoy_text_major_injury","compiledQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- During Jan 1–9, freeze exclusive end to Jan 1 of current year\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current period: Jan 1 of the year before the exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Prior period: shift back one full year\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS prior_year_start,\n (dr.max_report_date_excl - INTERVAL '1 year') AS prior_year_end_excl,\n -- Year labels\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS year_prior,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\nyearly_counts AS (\n SELECT\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS current_year_sum,\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.prior_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS prior_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n)\nSELECT\n 'Major' AS severity,\n yc.current_year_sum,\n yc.prior_year_sum,\n ABS(yc.current_year_sum - yc.prior_year_sum) AS difference,\n CASE WHEN yc.prior_year_sum <> 0\n THEN ((yc.current_year_sum - yc.prior_year_sum)::numeric / yc.prior_year_sum)\n ELSE NULL END AS percentage_change,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'an increase of'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'more'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n p.year_prior,\n CASE WHEN yc.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN yc.current_year_sum = 1 THEN 'major injury' ELSE 'major injuries' END AS major_injury,\n -- Display last included date\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM yearly_counts yc\nCROSS JOIN params p;","inputQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- During Jan 1–9, freeze exclusive end to Jan 1 of current year\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current period: Jan 1 of the year before the exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Prior period: shift back one full year\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS prior_year_start,\n (dr.max_report_date_excl - INTERVAL '1 year') AS prior_year_end_excl,\n -- Year labels\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 year' - INTERVAL '1 day') AS year_prior,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\nyearly_counts AS (\n SELECT\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS current_year_sum,\n SUM(\n CASE\n WHEN cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.prior_year_end_excl\n THEN cr.COUNT ELSE 0\n END\n ) AS prior_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.prior_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n)\nSELECT\n 'Major' AS severity,\n yc.current_year_sum,\n yc.prior_year_sum,\n ABS(yc.current_year_sum - yc.prior_year_sum) AS difference,\n CASE WHEN yc.prior_year_sum <> 0\n THEN ((yc.current_year_sum - yc.prior_year_sum)::numeric / yc.prior_year_sum)\n ELSE NULL END AS percentage_change,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'an increase of'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (yc.current_year_sum - yc.prior_year_sum) > 0 THEN 'more'\n WHEN (yc.current_year_sum - yc.prior_year_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n p.year_prior,\n CASE WHEN yc.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN yc.current_year_sum = 1 THEN 'major injury' ELSE 'major injuries' END AS major_injury,\n -- Display last included date\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM yearly_counts yc\nCROSS JOIN params p;","compiled":false,"inline":true},{"id":"yoy_text_major_injury_3ytd","compiledQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Freeze exclusive end to Jan 1 during Jan 1–9\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current YTD window: Jan 1 of the year before exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Year label for current year\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\n-- Current YTD major injury count\ncurrent_ytd AS (\n SELECT\n SUM(COUNT) AS current_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n),\n-- Three prior YTD periods (shift exclusive window back 1, 2, 3 years)\nprior_years AS (\n SELECT\n 1 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '1 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '1 year'\n UNION ALL\n SELECT\n 2 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '2 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '2 year'\n UNION ALL\n SELECT\n 3 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '3 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '3 year'\n),\n-- Average of the three prior YTDs\nprior_avg AS (\n SELECT\n (COALESCE(MAX(CASE WHEN yr_offset = 1 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 2 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 3 THEN sum_count END), 0)\n ) / 3.0 AS prior_3yr_avg_sum\n FROM prior_years\n)\nSELECT\n 'Major' AS severity,\n cy.current_year_sum,\n pa.prior_3yr_avg_sum,\n ABS(cy.current_year_sum - pa.prior_3yr_avg_sum) AS difference,\n CASE WHEN pa.prior_3yr_avg_sum <> 0\n THEN (cy.current_year_sum - pa.prior_3yr_avg_sum) / pa.prior_3yr_avg_sum\n ELSE NULL END AS percentage_change,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'an increase of'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'more'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n -- 'YY label for current year\n '''' || RIGHT(CAST(p.current_year AS VARCHAR), 2) AS current_year_label,\n -- 'YY label for last year\n '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2) AS last_year_label,\n -- Label for the prior period range, e.g. '22-'24 YTD Avg\n '''' || RIGHT(CAST(p.current_year - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2)\n || ' YTD Avg' AS prior_period_label,\n CASE WHEN cy.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN cy.current_year_sum = 1 THEN 'major injury' ELSE 'major injuries' END AS major_injury,\n -- Display last included date\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM current_ytd cy\nCROSS JOIN prior_avg pa\nCROSS JOIN params p;","inputQueryString":"WITH date_range AS (\n SELECT\n CASE\n -- Freeze exclusive end to Jan 1 during Jan 1–9\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN date_trunc('year', current_date)\n -- Otherwise: exclusive end = max(REPORTDATE) + 1 day\n ELSE MAX(LAST_RECORD)::DATE + INTERVAL '1 day'\n END AS max_report_date_excl\n FROM crashes.crashes\n),\nparams AS (\n SELECT\n -- Current YTD window: Jan 1 of the year before exclusive end\n date_trunc('year', dr.max_report_date_excl - INTERVAL '1 day') AS current_year_start,\n dr.max_report_date_excl AS current_year_end_excl,\n -- Year label for current year\n extract(year FROM dr.max_report_date_excl - INTERVAL '1 day') AS current_year,\n -- Freeze flag\n CASE\n WHEN extract(month FROM current_date) = 1\n AND extract(day FROM current_date) <= 9\n THEN TRUE\n ELSE FALSE\n END AS is_first_week\n FROM date_range dr\n),\n-- Current YTD major injury count\ncurrent_ytd AS (\n SELECT\n SUM(COUNT) AS current_year_sum\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start\n AND cr.REPORTDATE < p.current_year_end_excl\n),\n-- Three prior YTD periods (shift exclusive window back 1, 2, 3 years)\nprior_years AS (\n SELECT\n 1 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '1 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '1 year'\n UNION ALL\n SELECT\n 2 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '2 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '2 year'\n UNION ALL\n SELECT\n 3 AS yr_offset,\n SUM(COUNT) AS sum_count\n FROM crashes.crashes cr\n CROSS JOIN params p\n WHERE cr.SEVERITY = 'Major'\n AND cr.REPORTDATE >= p.current_year_start - INTERVAL '3 year'\n AND cr.REPORTDATE < p.current_year_end_excl - INTERVAL '3 year'\n),\n-- Average of the three prior YTDs\nprior_avg AS (\n SELECT\n (COALESCE(MAX(CASE WHEN yr_offset = 1 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 2 THEN sum_count END), 0) +\n COALESCE(MAX(CASE WHEN yr_offset = 3 THEN sum_count END), 0)\n ) / 3.0 AS prior_3yr_avg_sum\n FROM prior_years\n)\nSELECT\n 'Major' AS severity,\n cy.current_year_sum,\n pa.prior_3yr_avg_sum,\n ABS(cy.current_year_sum - pa.prior_3yr_avg_sum) AS difference,\n CASE WHEN pa.prior_3yr_avg_sum <> 0\n THEN (cy.current_year_sum - pa.prior_3yr_avg_sum) / pa.prior_3yr_avg_sum\n ELSE NULL END AS percentage_change,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'an increase of'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'a decrease of'\n ELSE NULL END AS percentage_change_text,\n CASE WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) > 0 THEN 'more'\n WHEN (cy.current_year_sum - pa.prior_3yr_avg_sum) < 0 THEN 'fewer'\n ELSE 'no change' END AS difference_text,\n p.current_year,\n -- 'YY label for current year\n '''' || RIGHT(CAST(p.current_year AS VARCHAR), 2) AS current_year_label,\n -- 'YY label for last year\n '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2) AS last_year_label,\n -- Label for the prior period range, e.g. '22-'24 YTD Avg\n '''' || RIGHT(CAST(p.current_year - 3 AS VARCHAR), 2)\n || '-' || '''' || RIGHT(CAST(p.current_year - 1 AS VARCHAR), 2)\n || ' YTD Avg' AS prior_period_label,\n CASE WHEN cy.current_year_sum = 1 THEN 'has' ELSE 'have' END AS has_have,\n CASE WHEN cy.current_year_sum = 1 THEN 'major injury' ELSE 'major injuries' END AS major_injury,\n -- Display last included date\n strftime(p.current_year_end_excl - INTERVAL '1 day', '%m/%d/%y') AS max_report_date_formatted,\n p.is_first_week\nFROM current_ytd cy\nCROSS JOIN prior_avg pa\nCROSS JOIN params p;","compiled":false,"inline":true},{"id":"severity_selection","compiledQueryString":"-- 1. Aggregate severities based on the INTERSECTION of inputs\nWITH\n severity_agg_cte AS (\n SELECT\n COUNT(DISTINCT SEVERITY) AS severity_count,\n CASE\n WHEN COUNT(DISTINCT SEVERITY) = 0 THEN ' '\n WHEN BOOL_AND(SEVERITY IN ('Fatal')) THEN 'Fatalities'\n WHEN BOOL_AND(SEVERITY IN ('Major', 'Fatal')) AND COUNT(DISTINCT SEVERITY) = 2 THEN 'Major Injuries and Fatalities'\n WHEN BOOL_AND(SEVERITY IN ('Minor', 'Major')) AND COUNT(DISTINCT SEVERITY) = 2 THEN 'Minor and Major Injuries'\n WHEN BOOL_AND(SEVERITY IN ('Minor', 'Major', 'Fatal')) AND COUNT(DISTINCT SEVERITY) = 3 THEN 'Minor and Major Injuries, Fatalities'\n ELSE STRING_AGG(\n DISTINCT CASE\n WHEN SEVERITY = 'Fatal' THEN 'Fatalities'\n WHEN SEVERITY = 'Major' THEN 'Major Injuries'\n WHEN SEVERITY = 'Minor' THEN 'Minor Injuries'\n END,\n ', '\n ORDER BY\n CASE SEVERITY\n WHEN 'Minor' THEN 1\n WHEN 'Major' THEN 2\n WHEN 'Fatal' THEN 3\n END\n )\n END AS severity_list\n FROM\n crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n )\n-- 2. Final formatting for SEVERITY_SELECTION\nSELECT\n CASE\n WHEN severity_count = 0 THEN ' '\n WHEN severity_count = 1 THEN severity_list\n WHEN severity_count = 2 THEN REPLACE(severity_list, ', ', ' and ')\n ELSE REGEXP_REPLACE(severity_list, ',([^,]+)$', ', and \\\\1')\n END AS SEVERITY_SELECTION\nFROM\n severity_agg_cte","inputQueryString":"-- 1. Aggregate severities based on the INTERSECTION of inputs\nWITH\n severity_agg_cte AS (\n SELECT\n COUNT(DISTINCT SEVERITY) AS severity_count,\n CASE\n WHEN COUNT(DISTINCT SEVERITY) = 0 THEN ' '\n WHEN BOOL_AND(SEVERITY IN ('Fatal')) THEN 'Fatalities'\n WHEN BOOL_AND(SEVERITY IN ('Major', 'Fatal')) AND COUNT(DISTINCT SEVERITY) = 2 THEN 'Major Injuries and Fatalities'\n WHEN BOOL_AND(SEVERITY IN ('Minor', 'Major')) AND COUNT(DISTINCT SEVERITY) = 2 THEN 'Minor and Major Injuries'\n WHEN BOOL_AND(SEVERITY IN ('Minor', 'Major', 'Fatal')) AND COUNT(DISTINCT SEVERITY) = 3 THEN 'Minor and Major Injuries, Fatalities'\n ELSE STRING_AGG(\n DISTINCT CASE\n WHEN SEVERITY = 'Fatal' THEN 'Fatalities'\n WHEN SEVERITY = 'Major' THEN 'Major Injuries'\n WHEN SEVERITY = 'Minor' THEN 'Minor Injuries'\n END,\n ', '\n ORDER BY\n CASE SEVERITY\n WHEN 'Minor' THEN 1\n WHEN 'Major' THEN 2\n WHEN 'Fatal' THEN 3\n END\n )\n END AS severity_list\n FROM\n crashes.crashes\n WHERE\n SEVERITY IN ${inputs.multi_severity.value}\n )\n-- 2. Final formatting for SEVERITY_SELECTION\nSELECT\n CASE\n WHEN severity_count = 0 THEN ' '\n WHEN severity_count = 1 THEN severity_list\n WHEN severity_count = 2 THEN REPLACE(severity_list, ', ', ' and ')\n ELSE REGEXP_REPLACE(severity_list, ',([^,]+)$', ', and \\\\1')\n END AS SEVERITY_SELECTION\nFROM\n severity_agg_cte","compiled":false,"inline":true}]}

DC Vision Zero Traffic Fatalities and Injuries