{"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":"min_max_age","compiledQueryString":"SELECT \n MAX(AGE) AS unique_max_age,\n MIN(AGE) AS unique_min_age\nFROM crashes.crashes\nWHERE SEVERITY IN ${inputs.multi_severity.value}\n AND MODE IN ${inputs.multi_mode_dd.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\n AND AGE BETWEEN \n ${inputs.min_age.value}\n AND LEAST(${inputs.max_age.value}, 119);","inputQueryString":"SELECT \n MAX(AGE) AS unique_max_age,\n MIN(AGE) AS unique_min_age\nFROM crashes.crashes\nWHERE SEVERITY IN ${inputs.multi_severity.value}\n AND MODE IN ${inputs.multi_mode_dd.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\n AND AGE BETWEEN \n ${inputs.min_age.value}\n AND LEAST(${inputs.max_age.value}, 119);","compiled":false,"inline":true},{"id":"age_mode","compiledQueryString":"WITH buckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n),\nnull_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n),\nall_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n),\n\nmode_list AS (\n -- Strip parentheses & quotes, split the comma list, then UNNEST\n SELECT\n TRIM(val) AS mode\n FROM UNNEST(\n string_split(\n REPLACE(\n TRIM(BOTH '()' FROM ${inputs.multi_mode_dd.value}),\n '''',''\n ),\n ','\n )\n ) AS t(val)\n),\n\nall_combinations AS (\n -- Every bucket × every selected mode\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n ml.mode\n FROM all_buckets ab\n CROSS JOIN mode_list ml\n),\n\nbinned_raw AS (\n -- Sum injuries per bucket × mode\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n c.MODE AS mode,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN '${inputs.date_range.start}'::DATE\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\n AND c.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 ab.bucket_order, ab.bucket_label, c.MODE\n),\n\nfinal AS (\n -- Left-join to fill in zeroes for missing bucket × mode combos\n SELECT\n ac.bucket_order,\n ac.bucket_label,\n ac.mode AS MODE,\n COALESCE(br.injuries, 0) AS Injuries\n FROM all_combinations ac\n LEFT JOIN binned_raw br\n ON ac.bucket_order = br.bucket_order\n AND ac.mode = br.mode\n)\n\nSELECT\n bucket_label,\n MODE,\n Injuries\nFROM final\nORDER BY\n bucket_order,\n CASE \n WHEN MODE = 'Pedestrian' THEN 1\n WHEN MODE = 'Other' THEN 2\n WHEN MODE = 'Bicyclist' THEN 3\n WHEN MODE = 'Scooterist*' THEN 4\n WHEN MODE = 'Motorcyclist*' THEN 5\n WHEN MODE = 'Passenger' THEN 6\n WHEN MODE = 'Driver' THEN 7\n END;","inputQueryString":"WITH buckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n),\nnull_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n),\nall_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n),\n\nmode_list AS (\n -- Strip parentheses & quotes, split the comma list, then UNNEST\n SELECT\n TRIM(val) AS mode\n FROM UNNEST(\n string_split(\n REPLACE(\n TRIM(BOTH '()' FROM ${inputs.multi_mode_dd.value}),\n '''',''\n ),\n ','\n )\n ) AS t(val)\n),\n\nall_combinations AS (\n -- Every bucket × every selected mode\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n ml.mode\n FROM all_buckets ab\n CROSS JOIN mode_list ml\n),\n\nbinned_raw AS (\n -- Sum injuries per bucket × mode\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n c.MODE AS mode,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN '${inputs.date_range.start}'::DATE\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\n AND c.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 ab.bucket_order, ab.bucket_label, c.MODE\n),\n\nfinal AS (\n -- Left-join to fill in zeroes for missing bucket × mode combos\n SELECT\n ac.bucket_order,\n ac.bucket_label,\n ac.mode AS MODE,\n COALESCE(br.injuries, 0) AS Injuries\n FROM all_combinations ac\n LEFT JOIN binned_raw br\n ON ac.bucket_order = br.bucket_order\n AND ac.mode = br.mode\n)\n\nSELECT\n bucket_label,\n MODE,\n Injuries\nFROM final\nORDER BY\n bucket_order,\n CASE \n WHEN MODE = 'Pedestrian' THEN 1\n WHEN MODE = 'Other' THEN 2\n WHEN MODE = 'Bicyclist' THEN 3\n WHEN MODE = 'Scooterist*' THEN 4\n WHEN MODE = 'Motorcyclist*' THEN 5\n WHEN MODE = 'Passenger' THEN 6\n WHEN MODE = 'Driver' THEN 7\n END;","compiled":false,"inline":true},{"id":"age_yoy","compiledQueryString":"WITH \n-- 1. Compute the “true” end_date (+1 day) and start_date\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes) + 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\ndate_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)::DATE FROM crashes.crashes)\n THEN EXTRACT(YEAR FROM end_date - INTERVAL '1 day')::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 current_period_range,\n EXTRACT(YEAR FROM end_date - INTERVAL '1 day') % 100 AS current_year_short\n FROM report_date_range\n),\n\noffset_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\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\nprior_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\nprior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', (SELECT end_date FROM date_info))\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN EXTRACT(YEAR FROM prior_end_date - INTERVAL '1 day')::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_period_range,\n EXTRACT(YEAR FROM prior_end_date - INTERVAL '1 day') % 100 AS prior_year_short\n FROM prior_date_info\n),\n\n-- Buckets\nbuckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n),\nnull_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n),\nall_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n),\n\n-- Current-period injuries\ncurrent_binned_raw AS (\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n),\n\n-- Prior-period injuries\nprior_binned_raw AS (\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT prior_start_date FROM prior_date_info)\n AND (SELECT prior_end_date FROM prior_date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n)\n\n-- Final union wrapped in subquery\nSELECT *\nFROM (\n SELECT\n bucket_order,\n bucket_label || ' (''' || (SELECT current_year_short FROM date_info) || ')' AS bucket_label,\n COALESCE(injuries, 0) AS Injuries,\n (SELECT current_period_range FROM date_info) AS YTD\n FROM current_binned_raw\n\n UNION ALL\n\n SELECT\n bucket_order,\n bucket_label || ' (''' || (SELECT prior_year_short FROM prior_date_label) || ')' AS bucket_label,\n COALESCE(injuries, 0) AS Injuries,\n (SELECT prior_period_range FROM prior_date_label) AS YTD\n FROM prior_binned_raw\n) combined\nORDER BY bucket_order, YTD;","inputQueryString":"WITH \n-- 1. Compute the “true” end_date (+1 day) and start_date\nreport_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes) + 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\ndate_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)::DATE FROM crashes.crashes)\n THEN EXTRACT(YEAR FROM end_date - INTERVAL '1 day')::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 current_period_range,\n EXTRACT(YEAR FROM end_date - INTERVAL '1 day') % 100 AS current_year_short\n FROM report_date_range\n),\n\noffset_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\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\nprior_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\nprior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', (SELECT end_date FROM date_info))\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN EXTRACT(YEAR FROM prior_end_date - INTERVAL '1 day')::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_period_range,\n EXTRACT(YEAR FROM prior_end_date - INTERVAL '1 day') % 100 AS prior_year_short\n FROM prior_date_info\n),\n\n-- Buckets\nbuckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n),\nnull_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n),\nall_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n),\n\n-- Current-period injuries\ncurrent_binned_raw AS (\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n),\n\n-- Prior-period injuries\nprior_binned_raw AS (\n SELECT\n ab.bucket_order,\n ab.bucket_label,\n SUM(c.COUNT) AS injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c\n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT prior_start_date FROM prior_date_info)\n AND (SELECT prior_end_date FROM prior_date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n)\n\n-- Final union wrapped in subquery\nSELECT *\nFROM (\n SELECT\n bucket_order,\n bucket_label || ' (''' || (SELECT current_year_short FROM date_info) || ')' AS bucket_label,\n COALESCE(injuries, 0) AS Injuries,\n (SELECT current_period_range FROM date_info) AS YTD\n FROM current_binned_raw\n\n UNION ALL\n\n SELECT\n bucket_order,\n bucket_label || ' (''' || (SELECT prior_year_short FROM prior_date_label) || ')' AS bucket_label,\n COALESCE(injuries, 0) AS Injuries,\n (SELECT prior_period_range FROM prior_date_label) AS YTD\n FROM prior_binned_raw\n) combined\nORDER BY bucket_order, YTD;","compiled":false,"inline":true},{"id":"age_comparison","compiledQueryString":"WITH \n -- 1. Compute the “true” end_date (+1 day) and start_date\n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes) + 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 -- 2. Build a single label: YTD only if start=Jan 1 of the year AND end hits max data;\n -- otherwise always “MM/DD/YY–MM/DD/YY”\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)::DATE FROM crashes.crashes)\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 current_period_range,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n\n -- 3. Figure out your “offset” to compare prior spans\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\n -- 4. Deduce the prior span’s start/end\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\n -- 5. Label that prior span with the same “YTD only-if” logic\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', (SELECT end_date FROM date_info))\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\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_period_range\n FROM prior_date_info\n ),\n\n -- 6. Define your age‐buckets (0–10, 11–20, …, >80, Null)\n buckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n ),\n null_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n ),\n all_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n ),\n\n -- 7. Aggregate current‐period injuries by bucket\n current_age AS (\n SELECT \n ab.bucket_order,\n ab.bucket_label,\n COALESCE(SUM(c.COUNT), 0) AS Injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c \n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR\n (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n ),\n\n -- 8. Aggregate prior‐period injuries by bucket\n prior_age AS (\n SELECT \n ab.bucket_order,\n ab.bucket_label,\n COALESCE(SUM(c.COUNT), 0) AS Injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c \n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR\n (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (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 c.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 ab.bucket_order, ab.bucket_label\n )\n\n-- 9. Union current/prior, attach the correct period label, and sort\nSELECT \n bucket_label,\n Injuries,\n Period_range\nFROM (\n SELECT \n ca.bucket_order,\n ca.bucket_label,\n ca.Injuries,\n (SELECT current_period_range FROM date_info) AS Period_range\n FROM current_age ca\n\n UNION ALL\n\n SELECT \n pa.bucket_order,\n pa.bucket_label,\n pa.Injuries,\n (SELECT prior_period_range FROM prior_date_label) AS Period_range\n FROM prior_age pa\n) AS combined\nORDER BY bucket_order, Period_range;","inputQueryString":"WITH \n -- 1. Compute the “true” end_date (+1 day) and start_date\n report_date_range AS (\n SELECT\n CASE \n WHEN '${inputs.date_range.end}'::DATE \n >= (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\n THEN (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes) + 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 -- 2. Build a single label: YTD only if start=Jan 1 of the year AND end hits max data;\n -- otherwise always “MM/DD/YY–MM/DD/YY”\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)::DATE FROM crashes.crashes)\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 current_period_range,\n (end_date - start_date) AS date_range_days\n FROM report_date_range\n ),\n\n -- 3. Figure out your “offset” to compare prior spans\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\n -- 4. Deduce the prior span’s start/end\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\n -- 5. Label that prior span with the same “YTD only-if” logic\n prior_date_label AS (\n SELECT\n CASE\n WHEN (SELECT start_date FROM date_info) = DATE_TRUNC('year', (SELECT end_date FROM date_info))\n AND '${inputs.date_range.end}'::DATE = (SELECT MAX(LAST_RECORD)::DATE FROM crashes.crashes)\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_period_range\n FROM prior_date_info\n ),\n\n -- 6. Define your age‐buckets (0–10, 11–20, …, >80, Null)\n buckets(bucket_order, bucket_label, lower_bound, upper_bound) AS (\n VALUES\n (0, '0-10', 0, 10),\n (11, '11-20', 11, 20),\n (21, '21-30', 21, 30),\n (31, '31-40', 31, 40),\n (41, '41-50', 41, 50),\n (51, '51-60', 51, 60),\n (61, '61-70', 61, 70),\n (71, '71-80', 71, 80),\n (81, '> 80', 81, 110)\n ),\n null_bucket AS (\n SELECT 9999 AS bucket_order, 'Null' AS bucket_label, 120 AS lower_bound, 120 AS upper_bound\n ),\n all_buckets AS (\n SELECT * FROM buckets\n UNION ALL\n SELECT * FROM null_bucket\n ),\n\n -- 7. Aggregate current‐period injuries by bucket\n current_age AS (\n SELECT \n ab.bucket_order,\n ab.bucket_label,\n COALESCE(SUM(c.COUNT), 0) AS Injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c \n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR\n (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (SELECT start_date FROM date_info)\n AND (SELECT end_date FROM date_info)\n AND c.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 ab.bucket_order, ab.bucket_label\n ),\n\n -- 8. Aggregate prior‐period injuries by bucket\n prior_age AS (\n SELECT \n ab.bucket_order,\n ab.bucket_label,\n COALESCE(SUM(c.COUNT), 0) AS Injuries\n FROM all_buckets ab\n LEFT JOIN crashes.crashes c \n ON (\n (ab.bucket_label = 'Null' AND CAST(c.AGE AS INTEGER) = ab.lower_bound)\n OR\n (ab.bucket_label <> 'Null' AND CAST(c.AGE AS INTEGER) BETWEEN ab.lower_bound AND ab.upper_bound)\n )\n AND c.MODE IN ${inputs.multi_mode_dd.value}\n AND c.SEVERITY IN ${inputs.multi_severity.value}\n AND c.REPORTDATE BETWEEN (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 c.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 ab.bucket_order, ab.bucket_label\n )\n\n-- 9. Union current/prior, attach the correct period label, and sort\nSELECT \n bucket_label,\n Injuries,\n Period_range\nFROM (\n SELECT \n ca.bucket_order,\n ca.bucket_label,\n ca.Injuries,\n (SELECT current_period_range FROM date_info) AS Period_range\n FROM current_age ca\n\n UNION ALL\n\n SELECT \n pa.bucket_order,\n pa.bucket_label,\n pa.Injuries,\n (SELECT prior_period_range FROM prior_date_label) AS Period_range\n FROM prior_age pa\n) AS combined\nORDER BY bucket_order, Period_range;","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}]}

Age Distribution