{"queries":[{"id":"hex","compiledQueryString":"select \n GRID_ID\nfrom hexgrid.crash_hexgrid\ngroup by all","inputQueryString":"select \n GRID_ID\nfrom hexgrid.crash_hexgrid\ngroup by all","compiled":false,"inline":false},{"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_hex","compiledQueryString":"SELECT \n GRID_ID\nFROM hexgrid.crash_hexgrid\nGROUP BY 1","inputQueryString":"SELECT \n GRID_ID\nFROM hexgrid.crash_hexgrid\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":"day_time","compiledQueryString":"WITH reference AS (\n SELECT\n dow.day_of_week,\n dow.day_number,\n hr.hour_number\n FROM \n (VALUES \n ('Sun', 0), \n ('Mon', 1), \n ('Tue', 2), \n ('Wed', 3), \n ('Thu', 4), \n ('Fri', 5), \n ('Sat', 6)\n ) AS dow(day_of_week, day_number),\n GENERATE_SERIES(0, 23) AS hr(hour_number)\n),\ncount_data AS (\n SELECT\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 'Sun'\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 'Mon'\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 'Tue'\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 'Wed'\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 'Thu'\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 'Fri'\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 'Sat'\n END AS day_of_week,\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 0\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 1\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 2\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 3\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 4\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 5\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 6\n END AS day_number,\n LPAD(CAST(DATE_PART('hour', REPORTDATE) AS VARCHAR), 2, '0') AS hour_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 day_of_week, day_number, hour_number\n)\nSELECT\n r.day_of_week,\n r.day_number,\n LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') AS hour_number,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON r.day_of_week = cd.day_of_week\n AND LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') = cd.hour_number\nORDER BY r.day_number, r.hour_number;","inputQueryString":"WITH reference AS (\n SELECT\n dow.day_of_week,\n dow.day_number,\n hr.hour_number\n FROM \n (VALUES \n ('Sun', 0), \n ('Mon', 1), \n ('Tue', 2), \n ('Wed', 3), \n ('Thu', 4), \n ('Fri', 5), \n ('Sat', 6)\n ) AS dow(day_of_week, day_number),\n GENERATE_SERIES(0, 23) AS hr(hour_number)\n),\ncount_data AS (\n SELECT\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 'Sun'\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 'Mon'\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 'Tue'\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 'Wed'\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 'Thu'\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 'Fri'\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 'Sat'\n END AS day_of_week,\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 0\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 1\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 2\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 3\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 4\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 5\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 6\n END AS day_number,\n LPAD(CAST(DATE_PART('hour', REPORTDATE) AS VARCHAR), 2, '0') AS hour_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 day_of_week, day_number, hour_number\n)\nSELECT\n r.day_of_week,\n r.day_number,\n LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') AS hour_number,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON r.day_of_week = cd.day_of_week\n AND LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') = cd.hour_number\nORDER BY r.day_number, r.hour_number;","compiled":false,"inline":true},{"id":"time","compiledQueryString":"WITH reference AS (\n SELECT hr.hour_number\n FROM GENERATE_SERIES(0, 23) AS hr(hour_number)\n),\ncount_data AS (\n SELECT\n LPAD(CAST(DATE_PART('hour', REPORTDATE) AS VARCHAR), 2, '0') AS hour_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 hour_number\n)\nSELECT\n 'Total' AS Total,\n LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') AS hour_number,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') = cd.hour_number\nORDER BY r.hour_number;","inputQueryString":"WITH reference AS (\n SELECT hr.hour_number\n FROM GENERATE_SERIES(0, 23) AS hr(hour_number)\n),\ncount_data AS (\n SELECT\n LPAD(CAST(DATE_PART('hour', REPORTDATE) AS VARCHAR), 2, '0') AS hour_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 hour_number\n)\nSELECT\n 'Total' AS Total,\n LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') AS hour_number,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON LPAD(CAST(r.hour_number AS VARCHAR), 2, '0') = cd.hour_number\nORDER BY r.hour_number;","compiled":false,"inline":true},{"id":"day","compiledQueryString":"WITH reference AS (\n SELECT\n dow.day_of_week,\n dow.day_number,\n 'Total' AS total\n FROM \n (VALUES \n ('Sun', 0), \n ('Mon', 1), \n ('Tue', 2), \n ('Wed', 3), \n ('Thu', 4), \n ('Fri', 5), \n ('Sat', 6)\n ) AS dow(day_of_week, day_number)\n),\ncount_data AS (\n SELECT\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 'Sun'\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 'Mon'\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 'Tue'\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 'Wed'\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 'Thu'\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 'Fri'\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 'Sat'\n END AS day_of_week,\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 0\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 1\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 2\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 3\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 4\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 5\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 6\n END AS day_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 day_of_week, day_number\n)\nSELECT\n r.day_of_week,\n r.day_number,\n r.total,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON r.day_of_week = cd.day_of_week\nORDER BY r.day_number;","inputQueryString":"WITH reference AS (\n SELECT\n dow.day_of_week,\n dow.day_number,\n 'Total' AS total\n FROM \n (VALUES \n ('Sun', 0), \n ('Mon', 1), \n ('Tue', 2), \n ('Wed', 3), \n ('Thu', 4), \n ('Fri', 5), \n ('Sat', 6)\n ) AS dow(day_of_week, day_number)\n),\ncount_data AS (\n SELECT\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 'Sun'\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 'Mon'\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 'Tue'\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 'Wed'\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 'Thu'\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 'Fri'\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 'Sat'\n END AS day_of_week,\n CASE\n WHEN DATE_PART('dow', REPORTDATE) = 1 THEN 0\n WHEN DATE_PART('dow', REPORTDATE) = 2 THEN 1\n WHEN DATE_PART('dow', REPORTDATE) = 3 THEN 2\n WHEN DATE_PART('dow', REPORTDATE) = 4 THEN 3\n WHEN DATE_PART('dow', REPORTDATE) = 5 THEN 4\n WHEN DATE_PART('dow', REPORTDATE) = 6 THEN 5\n WHEN DATE_PART('dow', REPORTDATE) = 0 THEN 6\n END AS day_number,\n SUM(\"COUNT\") AS \"count\"\n FROM crashes.crashes\n WHERE \n MODE IN ${inputs.multi_mode_dd.value}\n AND SEVERITY IN ${inputs.multi_severity.value}\n AND REPORTDATE BETWEEN ('${inputs.date_range.start}'::DATE)\n AND (('${inputs.date_range.end}'::DATE) + INTERVAL '1 day')\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 day_of_week, day_number\n)\nSELECT\n r.day_of_week,\n r.day_number,\n r.total,\n COALESCE(cd.\"count\", 0) AS \"count\"\nFROM reference r\nLEFT JOIN count_data cd\n ON r.day_of_week = cd.day_of_week\nORDER BY r.day_number;","compiled":false,"inline":true},{"id":"hex_map","compiledQueryString":"SELECT\n h.GRID_ID,\n COALESCE(SUM(c.COUNT), 0) AS count,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link\nFROM hexgrid.crash_hexgrid h\nLEFT JOIN crashes.crashes c \n ON h.GRID_ID = c.GRID_ID\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 )\nGROUP BY h.GRID_ID;","inputQueryString":"SELECT\n h.GRID_ID,\n COALESCE(SUM(c.COUNT), 0) AS count,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link\nFROM hexgrid.crash_hexgrid h\nLEFT JOIN crashes.crashes c \n ON h.GRID_ID = c.GRID_ID\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 )\nGROUP BY h.GRID_ID;","compiled":false,"inline":true},{"id":"hex_with_link","compiledQueryString":"SELECT \n h.*,\n COALESCE(i.count, 0) AS count,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link\nFROM (select \n GRID_ID\nfrom hexgrid.crash_hexgrid\ngroup by all) AS h\nLEFT JOIN (\n SELECT \n c.GRID_ID,\n SUM(c.COUNT) AS count\n FROM crashes.crashes c\n WHERE \n 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 c.GRID_ID\n) AS i\nON h.GRID_ID = i.GRID_ID;","inputQueryString":"SELECT \n h.*,\n COALESCE(i.count, 0) AS count,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link\nFROM ${hex} AS h\nLEFT JOIN (\n SELECT \n c.GRID_ID,\n SUM(c.COUNT) AS count\n FROM crashes.crashes c\n WHERE \n 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 c.GRID_ID\n) AS i\nON h.GRID_ID = i.GRID_ID;","compiled":true,"inline":true},{"id":"hex_with_link","compiledQueryString":"SELECT \n h.*,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link,\n i.count,\n i.ccns\nFROM (select \n GRID_ID\nfrom hexgrid.crash_hexgrid\ngroup by all) AS h\nLEFT JOIN (\n SELECT \n c.GRID_ID,\n SUM(c.COUNT) AS count,\n string_agg(DISTINCT CAST(c.CCN AS VARCHAR), ', ' ORDER BY c.CCN) AS ccns\n FROM crashes.crashes c\n WHERE \n 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 c.GRID_ID\n) AS i\nON h.GRID_ID = i.GRID_ID;","inputQueryString":"SELECT \n h.*,\n '/Crash-Injury-Dashboard/hexgrid/' || h.GRID_ID AS link,\n i.count,\n i.ccns\nFROM ${hex} AS h\nLEFT JOIN (\n SELECT \n c.GRID_ID,\n SUM(c.COUNT) AS count,\n string_agg(DISTINCT CAST(c.CCN AS VARCHAR), ', ' ORDER BY c.CCN) AS ccns\n FROM crashes.crashes c\n WHERE \n 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 c.GRID_ID\n) AS i\nON h.GRID_ID = i.GRID_ID;","compiled":true,"inline":true},{"id":"roadsegment_dropdown_a","compiledQueryString":"SELECT DISTINCT roadsegment\nFROM intersections.intersections\nCROSS JOIN UNNEST(split(INTERSECTIONNAME, ' & ')) AS t(roadsegment);","inputQueryString":"SELECT DISTINCT roadsegment\nFROM intersections.intersections\nCROSS JOIN UNNEST(split(INTERSECTIONNAME, ' & ')) AS t(roadsegment);","compiled":false,"inline":true},{"id":"roadsegment_dropdown_b","compiledQueryString":"SELECT DISTINCT t.roadsegment\nFROM intersections.intersections\nCROSS JOIN UNNEST(split(INTERSECTIONNAME, ' & ')) AS t(roadsegment)\nWHERE INTERSECTIONNAME LIKE '%${inputs.roadsegment_a.value}%';","inputQueryString":"SELECT DISTINCT t.roadsegment\nFROM intersections.intersections\nCROSS JOIN UNNEST(split(INTERSECTIONNAME, ' & ')) AS t(roadsegment)\nWHERE INTERSECTIONNAME LIKE '%${inputs.roadsegment_a.value}%';","compiled":false,"inline":true},{"id":"intersections_table","compiledQueryString":"SELECT\n INTERSECTIONNAME,\n '/hexgrid/' || GRID_ID AS link,\n split(INTERSECTIONNAME, ' & ') AS ROADSEGMENT\nFROM intersections.intersections\nWHERE array_position(split(INTERSECTIONNAME, ' & '), '${inputs.roadsegment_a.value}') IS NOT NULL\n AND array_position(split(INTERSECTIONNAME, ' & '), '${inputs.roadsegment_b.value}') IS NOT NULL\nGROUP BY INTERSECTIONNAME, GRID_ID\nLIMIT 5;","inputQueryString":"SELECT\n INTERSECTIONNAME,\n '/hexgrid/' || GRID_ID AS link,\n split(INTERSECTIONNAME, ' & ') AS ROADSEGMENT\nFROM intersections.intersections\nWHERE array_position(split(INTERSECTIONNAME, ' & '), '${inputs.roadsegment_a.value}') IS NOT NULL\n AND array_position(split(INTERSECTIONNAME, ' & '), '${inputs.roadsegment_b.value}') IS NOT NULL\nGROUP BY INTERSECTIONNAME, GRID_ID\nLIMIT 5;","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":"selected_date_range","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 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 current_period_range\n FROM report_date_range\n )\nSELECT current_period_range\nFROM date_info;","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 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 current_period_range\n FROM report_date_range\n )\nSELECT current_period_range\nFROM date_info;","compiled":false,"inline":true}]}

Heatmap