{"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_year","compiledQueryString":"SELECT DISTINCT strftime('%Y', REPORTDATE) AS year_string\nFROM crashes.crashes\nWHERE strftime('%Y', REPORTDATE) BETWEEN '2017' \n AND (SELECT strftime('%Y', MAX(LAST_RECORD)) FROM crashes.crashes)\nORDER BY year_string DESC;","inputQueryString":"SELECT DISTINCT strftime('%Y', REPORTDATE) AS year_string\nFROM crashes.crashes\nWHERE strftime('%Y', REPORTDATE) BETWEEN '2017' \n AND (SELECT strftime('%Y', MAX(LAST_RECORD)) FROM crashes.crashes)\nORDER BY year_string DESC;","compiled":false,"inline":true},{"id":"unique_cy","compiledQueryString":"SELECT DISTINCT CAST(DATE_PART('year', REPORTDATE) AS INTEGER) AS year_integer\nFROM crashes.crashes\nWHERE DATE_PART('year', REPORTDATE) BETWEEN 2017\n AND (SELECT CAST(DATE_PART('year', MAX(LAST_RECORD)) AS INTEGER) FROM crashes.crashes)\n AND DATE_PART('year', REPORTDATE) <> DATE_PART('year', CURRENT_DATE)\nORDER BY year_integer DESC;","inputQueryString":"SELECT DISTINCT CAST(DATE_PART('year', REPORTDATE) AS INTEGER) AS year_integer\nFROM crashes.crashes\nWHERE DATE_PART('year', REPORTDATE) BETWEEN 2017\n AND (SELECT CAST(DATE_PART('year', MAX(LAST_RECORD)) AS INTEGER) FROM crashes.crashes)\n AND DATE_PART('year', REPORTDATE) <> DATE_PART('year', CURRENT_DATE)\nORDER BY year_integer DESC;","compiled":false,"inline":true},{"id":"mode_severity_selection","compiledQueryString":"WITH\n -- 1. Get the total number of unique modes in the entire table\n total_modes_cte AS (\n SELECT\n COUNT(DISTINCT MODE) AS total_mode_count\n FROM\n crashes.crashes\n ),\n -- 2. Aggregate the modes, applying pluralization before aggregating\n mode_agg_cte AS (\n SELECT\n STRING_AGG(\n DISTINCT CASE\n -- If the mode ends with '*', insert 's' before it\n WHEN MODE LIKE '%*' THEN REPLACE(MODE, '*', 's*')\n -- Otherwise, just append 's'\n ELSE MODE || 's'\n END,\n ', '\n ORDER BY\n MODE ASC\n ) AS mode_list,\n COUNT(DISTINCT MODE) AS mode_count\n FROM\n crashes.crashes\n WHERE\n MODE IN ${inputs.multi_mode_dd.value}\n ),\n -- 3. Aggregate severities based on the INTERSECTION of both inputs\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 MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n )\n-- 4. Combine results and apply final formatting logic to each column\nSELECT\n CASE\n WHEN mode_count = 0 THEN ' '\n WHEN mode_count = total_mode_count THEN 'All Road Users'\n WHEN mode_count = 1 THEN mode_list\n WHEN mode_count = 2 THEN REPLACE(mode_list, ', ', ' and ')\n ELSE REGEXP_REPLACE(mode_list, ',([^,]+)$', ', and \\\\1')\n END AS MODE_SELECTION,\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 mode_agg_cte,\n severity_agg_cte,\n total_modes_cte;","inputQueryString":"WITH\n -- 1. Get the total number of unique modes in the entire table\n total_modes_cte AS (\n SELECT\n COUNT(DISTINCT MODE) AS total_mode_count\n FROM\n crashes.crashes\n ),\n -- 2. Aggregate the modes, applying pluralization before aggregating\n mode_agg_cte AS (\n SELECT\n STRING_AGG(\n DISTINCT CASE\n -- If the mode ends with '*', insert 's' before it\n WHEN MODE LIKE '%*' THEN REPLACE(MODE, '*', 's*')\n -- Otherwise, just append 's'\n ELSE MODE || 's'\n END,\n ', '\n ORDER BY\n MODE ASC\n ) AS mode_list,\n COUNT(DISTINCT MODE) AS mode_count\n FROM\n crashes.crashes\n WHERE\n MODE IN ${inputs.multi_mode_dd.value}\n ),\n -- 3. Aggregate severities based on the INTERSECTION of both inputs\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 MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n )\n-- 4. Combine results and apply final formatting logic to each column\nSELECT\n CASE\n WHEN mode_count = 0 THEN ' '\n WHEN mode_count = total_mode_count THEN 'All Road Users'\n WHEN mode_count = 1 THEN mode_list\n WHEN mode_count = 2 THEN REPLACE(mode_list, ', ', ' and ')\n ELSE REGEXP_REPLACE(mode_list, ',([^,]+)$', ', and \\\\1')\n END AS MODE_SELECTION,\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 mode_agg_cte,\n severity_agg_cte,\n total_modes_cte;","compiled":false,"inline":true},{"id":"ytd_month","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE >= \n (SELECT CAST(MAX(LAST_RECORD) AS DATE) FROM crashes.crashes) \n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)\n -- If user selected a full calendar year, do NOT add a day\n WHEN strftime('${inputs.date_range.end}'::DATE, '%m-%d') = '12-31'\n THEN '${inputs.date_range.end}'::DATE\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS current_end_date,\n '${inputs.date_range.start}'::DATE AS current_start_date\n ),\n date_info AS (\n SELECT\n current_start_date AS start_date,\n current_end_date AS end_date,\n -- Always derive reporting year from user-selected end date\n EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE) AS current_year,\n -- Month-day cutoff depends on full-year vs YTD mode\n CASE \n WHEN '${inputs.date_range.start}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 1, 1)\n AND '${inputs.date_range.end}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 12, 31)\n THEN '12-31'\n ELSE strftime(current_end_date, '%m-%d')\n END AS month_day_end,\n CASE \n WHEN '${inputs.date_range.start}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 1, 1)\n AND '${inputs.date_range.end}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 12, 31)\n THEN TRUE\n ELSE FALSE\n END AS is_full_year\n FROM report_date_range\n ),\n years AS (\n SELECT gs AS yr, d.current_year\n FROM date_info d, generate_series(current_year - 3, current_year) AS t(gs)\n ),\n months AS (\n SELECT gs AS mo\n FROM generate_series(1, 12) AS t(gs)\n ),\n year_month_grid AS (\n SELECT y.yr, y.current_year, m.mo\n FROM years y\n CROSS JOIN months m\n ),\n monthly_counts AS (\n SELECT\n CAST(strftime('%Y', REPORTDATE) AS BIGINT) AS yr,\n CAST(strftime('%m', REPORTDATE) AS BIGINT) AS mo,\n SUM(\"COUNT\") AS month_count,\n d.current_year\n FROM crashes.crashes, date_info d\n WHERE CAST(strftime('%Y', REPORTDATE) AS BIGINT)\n BETWEEN (current_year - 3) AND current_year\n AND crashes.SEVERITY IN ${inputs.multi_severity.value}\n AND crashes.MODE IN ${inputs.multi_mode_dd.value}\n AND crashes.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 AND (\n d.is_full_year\n OR strftime(REPORTDATE, '%m-%d') <= d.month_day_end\n )\n GROUP BY yr, mo, d.current_year\n ),\n base AS (\n SELECT \n g.yr AS Year,\n g.mo AS Month,\n strftime(make_date(g.yr, g.mo, 1), '%b') AS Month_Name,\n COALESCE(mc.month_count, 0) AS Count,\n g.current_year\n FROM year_month_grid g\n LEFT JOIN monthly_counts mc\n ON g.yr = mc.yr AND g.mo = mc.mo\n ),\n avg_row AS (\n SELECT\n printf('%02d–%02d Avg', MIN(Year) % 100, MAX(Year) % 100) AS Year,\n Month,\n Month_Name,\n ROUND(AVG(Count),0) AS Count,\n current_year\n FROM base\n WHERE Year BETWEEN current_year - 3 AND current_year - 1\n GROUP BY Month, Month_Name, current_year\n )\nSELECT *\nFROM (\n SELECT CAST(Year AS VARCHAR) AS Year, Month, Month_Name, Count, current_year\n FROM base\n UNION ALL\n SELECT Year, Month, Month_Name, Count, current_year\n FROM avg_row\n)\nORDER BY \n Month,\n CASE \n WHEN Year = CAST((current_year - 3) AS VARCHAR) THEN 1\n WHEN Year = CAST((current_year - 2) AS VARCHAR) THEN 2\n WHEN Year = CAST((current_year - 1) AS VARCHAR) THEN 3\n WHEN Year LIKE '% Avg' THEN 4\n WHEN Year = CAST(current_year AS VARCHAR) THEN 5\n END;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE >= \n (SELECT CAST(MAX(LAST_RECORD) AS DATE) FROM crashes.crashes) \n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)\n -- If user selected a full calendar year, do NOT add a day\n WHEN strftime('${inputs.date_range.end}'::DATE, '%m-%d') = '12-31'\n THEN '${inputs.date_range.end}'::DATE\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS current_end_date,\n '${inputs.date_range.start}'::DATE AS current_start_date\n ),\n date_info AS (\n SELECT\n current_start_date AS start_date,\n current_end_date AS end_date,\n -- Always derive reporting year from user-selected end date\n EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE) AS current_year,\n -- Month-day cutoff depends on full-year vs YTD mode\n CASE \n WHEN '${inputs.date_range.start}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 1, 1)\n AND '${inputs.date_range.end}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 12, 31)\n THEN '12-31'\n ELSE strftime(current_end_date, '%m-%d')\n END AS month_day_end,\n CASE \n WHEN '${inputs.date_range.start}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 1, 1)\n AND '${inputs.date_range.end}'::DATE = make_date(EXTRACT(YEAR FROM '${inputs.date_range.end}'::DATE)::INT, 12, 31)\n THEN TRUE\n ELSE FALSE\n END AS is_full_year\n FROM report_date_range\n ),\n years AS (\n SELECT gs AS yr, d.current_year\n FROM date_info d, generate_series(current_year - 3, current_year) AS t(gs)\n ),\n months AS (\n SELECT gs AS mo\n FROM generate_series(1, 12) AS t(gs)\n ),\n year_month_grid AS (\n SELECT y.yr, y.current_year, m.mo\n FROM years y\n CROSS JOIN months m\n ),\n monthly_counts AS (\n SELECT\n CAST(strftime('%Y', REPORTDATE) AS BIGINT) AS yr,\n CAST(strftime('%m', REPORTDATE) AS BIGINT) AS mo,\n SUM(\"COUNT\") AS month_count,\n d.current_year\n FROM crashes.crashes, date_info d\n WHERE CAST(strftime('%Y', REPORTDATE) AS BIGINT)\n BETWEEN (current_year - 3) AND current_year\n AND crashes.SEVERITY IN ${inputs.multi_severity.value}\n AND crashes.MODE IN ${inputs.multi_mode_dd.value}\n AND crashes.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 AND (\n d.is_full_year\n OR strftime(REPORTDATE, '%m-%d') <= d.month_day_end\n )\n GROUP BY yr, mo, d.current_year\n ),\n base AS (\n SELECT \n g.yr AS Year,\n g.mo AS Month,\n strftime(make_date(g.yr, g.mo, 1), '%b') AS Month_Name,\n COALESCE(mc.month_count, 0) AS Count,\n g.current_year\n FROM year_month_grid g\n LEFT JOIN monthly_counts mc\n ON g.yr = mc.yr AND g.mo = mc.mo\n ),\n avg_row AS (\n SELECT\n printf('%02d–%02d Avg', MIN(Year) % 100, MAX(Year) % 100) AS Year,\n Month,\n Month_Name,\n ROUND(AVG(Count),0) AS Count,\n current_year\n FROM base\n WHERE Year BETWEEN current_year - 3 AND current_year - 1\n GROUP BY Month, Month_Name, current_year\n )\nSELECT *\nFROM (\n SELECT CAST(Year AS VARCHAR) AS Year, Month, Month_Name, Count, current_year\n FROM base\n UNION ALL\n SELECT Year, Month, Month_Name, Count, current_year\n FROM avg_row\n)\nORDER BY \n Month,\n CASE \n WHEN Year = CAST((current_year - 3) AS VARCHAR) THEN 1\n WHEN Year = CAST((current_year - 2) AS VARCHAR) THEN 2\n WHEN Year = CAST((current_year - 1) AS VARCHAR) THEN 3\n WHEN Year LIKE '% Avg' THEN 4\n WHEN Year = CAST(current_year AS VARCHAR) THEN 5\n END;","compiled":false,"inline":true},{"id":"ytd_month_avg","compiledQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE >= \n (SELECT CAST(MAX(LAST_RECORD) AS DATE) FROM crashes.crashes) \n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS current_end_date,\n '${inputs.date_range.start}'::DATE AS current_start_date\n ),\n date_info AS (\n SELECT\n current_start_date AS start_date,\n current_end_date AS end_date,\n EXTRACT(YEAR FROM current_end_date) AS current_year,\n strftime(current_end_date, '%m-%d') AS month_day_end\n FROM report_date_range\n ),\n monthly_counts AS (\n SELECT\n CAST(strftime('%Y', REPORTDATE) AS BIGINT) AS yr,\n CAST(strftime('%m', REPORTDATE) AS BIGINT) AS mo,\n SUM(\"COUNT\") AS month_count\n FROM crashes.crashes, date_info\n WHERE CAST(strftime('%Y', REPORTDATE) AS BIGINT) BETWEEN (current_year - 4) AND current_year\n AND crashes.SEVERITY IN ${inputs.multi_severity.value}\n AND crashes.MODE IN ${inputs.multi_mode_dd.value}\n AND crashes.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 AND strftime(REPORTDATE, '%m-%d') <= month_day_end\n GROUP BY yr, mo\n ),\n filtered AS (\n SELECT *\n FROM monthly_counts, date_info\n WHERE yr <> current_year\n ),\n avg_by_month AS (\n SELECT\n mo,\n ROUND(AVG(month_count),0) AS Avg_Count,\n MIN(yr) AS min_yr,\n MAX(yr) AS max_yr\n FROM filtered\n GROUP BY mo\n ),\n months AS (\n SELECT gs AS mo\n FROM generate_series(1,12) t(gs)\n )\nSELECT\n m.mo AS Month,\n strftime(make_date(2000, m.mo, 1), '%b') AS Month_Name,\n COALESCE(a.Avg_Count, 0) AS Avg_Count,\n printf('%02d–%02d YTD Avg', MIN(a.min_yr) % 100, MAX(a.max_yr) % 100) AS Year_Range_Label\nFROM months m\nLEFT JOIN avg_by_month a ON m.mo = a.mo\nGROUP BY m.mo, a.Avg_Count, a.min_yr, a.max_yr\nORDER BY m.mo;","inputQueryString":"WITH \n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE >= \n (SELECT CAST(MAX(LAST_RECORD) AS DATE) FROM crashes.crashes) \n THEN (SELECT MAX(LAST_RECORD) FROM crashes.crashes)\n ELSE '${inputs.date_range.end}'::DATE + INTERVAL '1 day'\n END AS current_end_date,\n '${inputs.date_range.start}'::DATE AS current_start_date\n ),\n date_info AS (\n SELECT\n current_start_date AS start_date,\n current_end_date AS end_date,\n EXTRACT(YEAR FROM current_end_date) AS current_year,\n strftime(current_end_date, '%m-%d') AS month_day_end\n FROM report_date_range\n ),\n monthly_counts AS (\n SELECT\n CAST(strftime('%Y', REPORTDATE) AS BIGINT) AS yr,\n CAST(strftime('%m', REPORTDATE) AS BIGINT) AS mo,\n SUM(\"COUNT\") AS month_count\n FROM crashes.crashes, date_info\n WHERE CAST(strftime('%Y', REPORTDATE) AS BIGINT) BETWEEN (current_year - 4) AND current_year\n AND crashes.SEVERITY IN ${inputs.multi_severity.value}\n AND crashes.MODE IN ${inputs.multi_mode_dd.value}\n AND crashes.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 AND strftime(REPORTDATE, '%m-%d') <= month_day_end\n GROUP BY yr, mo\n ),\n filtered AS (\n SELECT *\n FROM monthly_counts, date_info\n WHERE yr <> current_year\n ),\n avg_by_month AS (\n SELECT\n mo,\n ROUND(AVG(month_count),0) AS Avg_Count,\n MIN(yr) AS min_yr,\n MAX(yr) AS max_yr\n FROM filtered\n GROUP BY mo\n ),\n months AS (\n SELECT gs AS mo\n FROM generate_series(1,12) t(gs)\n )\nSELECT\n m.mo AS Month,\n strftime(make_date(2000, m.mo, 1), '%b') AS Month_Name,\n COALESCE(a.Avg_Count, 0) AS Avg_Count,\n printf('%02d–%02d YTD Avg', MIN(a.min_yr) % 100, MAX(a.max_yr) % 100) AS Year_Range_Label\nFROM months m\nLEFT JOIN avg_by_month a ON m.mo = a.mo\nGROUP BY m.mo, a.Avg_Count, a.min_yr, a.max_yr\nORDER BY m.mo;","compiled":false,"inline":true}]}

Monthly Trend