{"queries":[{"id":"station_all","compiledQueryString":"WITH cleaned_stations AS (\n -- Extract station name, coordinates, and ridership for all stations\n SELECT DISTINCT\n link_friendly_id, -- Extract station name before '('\n Origin_Point,\n Origin_Station_Complex_Name as Station_Full_Name,\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude,\n SUM(Estimated_Average_Ridership) AS Total_Ridership -- Aggregate ridership for each station\n FROM \n mta_d.origin_dest_ridership_daily\n --where Origin_Station_Complex_Name NOT LIKE '%/%'\n GROUP BY \n Origin_Point, Station_Full_Name, link_friendly_id\n),\n\ncrime_data AS (\n -- Aggregate crimes by severity for all stations\n SELECT \n c.LAW_CAT_CD,\n CASE \n WHEN c.LAW_CAT_CD = 'FELONY' THEN 3 -- Assign weight 3 for felonies\n WHEN c.LAW_CAT_CD = 'MISDEMEANOR' THEN 2 -- Assign weight 2 for misdemeanors\n WHEN c.LAW_CAT_CD = 'VIOLATION' THEN 1 -- Assign weight 1 for violations\n ELSE 0\n END AS crime_weight,\n c.Latitude AS Cr_Lat, \n c.Longitude AS Cr_Long,\n c.CMPLNT_FR_DT as complaint_date\n FROM \n mta.nypd_subway_crimes_report c\n WHERE \n c.CMPLNT_FR_DT BETWEEN '2024-01-01' AND '2024-08-31' -- Date range for crimes\n),\n\ncrime_per_capita AS (\n -- Calculate crime per capita for each station\n SELECT \n \n s.Station_Full_Name,\n s.link_friendly_id,\n s.Latitude,\n s.Longitude,\n SUM(cd.crime_weight) AS Total_Crime_Weight, -- Total crime severity score for each station\n s.Total_Ridership, -- Total ridership for each station\n SUM(cd.crime_weight) / s.Total_Ridership AS Crime_Per_Capita -- Crime score per rider\n FROM \n crime_data cd\n JOIN \n cleaned_stations s \n ON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(cd.Cr_Lat)) * \n COS(RADIANS(cd.Cr_Long) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(cd.Cr_Lat))\n )\n ) <= 0.2 -- \n GROUP BY \n s.Station_Full_Name, s.link_friendly_id, s.Latitude, s.Longitude, s.Total_Ridership\n)\n\n-- Assign letter grade based on the calculated safety grade\nSELECT\n Station_Full_Name as Station_Name,\n link_friendly_id,\n Latitude,\n Longitude,\n Total_Crime_Weight,\n Total_Ridership,\n Crime_Per_Capita,\n -- Rank crime per capita and scale to a 100-point grade\n 100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC) AS Safety_Grade,\n -- Assign letter grades based on safety grade thresholds\n CASE \n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 90 THEN 'A'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 75 THEN 'B'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 50 THEN 'C'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 25 THEN 'D'\n ELSE 'F'\n END AS Grade -- Assign letter grade based on the percentile\nFROM \n crime_per_capita\nORDER BY \n Safety_Grade","inputQueryString":"WITH cleaned_stations AS (\n -- Extract station name, coordinates, and ridership for all stations\n SELECT DISTINCT\n link_friendly_id, -- Extract station name before '('\n Origin_Point,\n Origin_Station_Complex_Name as Station_Full_Name,\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude,\n SUM(Estimated_Average_Ridership) AS Total_Ridership -- Aggregate ridership for each station\n FROM \n mta_d.origin_dest_ridership_daily\n --where Origin_Station_Complex_Name NOT LIKE '%/%'\n GROUP BY \n Origin_Point, Station_Full_Name, link_friendly_id\n),\n\ncrime_data AS (\n -- Aggregate crimes by severity for all stations\n SELECT \n c.LAW_CAT_CD,\n CASE \n WHEN c.LAW_CAT_CD = 'FELONY' THEN 3 -- Assign weight 3 for felonies\n WHEN c.LAW_CAT_CD = 'MISDEMEANOR' THEN 2 -- Assign weight 2 for misdemeanors\n WHEN c.LAW_CAT_CD = 'VIOLATION' THEN 1 -- Assign weight 1 for violations\n ELSE 0\n END AS crime_weight,\n c.Latitude AS Cr_Lat, \n c.Longitude AS Cr_Long,\n c.CMPLNT_FR_DT as complaint_date\n FROM \n mta.nypd_subway_crimes_report c\n WHERE \n c.CMPLNT_FR_DT BETWEEN '2024-01-01' AND '2024-08-31' -- Date range for crimes\n),\n\ncrime_per_capita AS (\n -- Calculate crime per capita for each station\n SELECT \n \n s.Station_Full_Name,\n s.link_friendly_id,\n s.Latitude,\n s.Longitude,\n SUM(cd.crime_weight) AS Total_Crime_Weight, -- Total crime severity score for each station\n s.Total_Ridership, -- Total ridership for each station\n SUM(cd.crime_weight) / s.Total_Ridership AS Crime_Per_Capita -- Crime score per rider\n FROM \n crime_data cd\n JOIN \n cleaned_stations s \n ON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(cd.Cr_Lat)) * \n COS(RADIANS(cd.Cr_Long) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(cd.Cr_Lat))\n )\n ) <= 0.2 -- \n GROUP BY \n s.Station_Full_Name, s.link_friendly_id, s.Latitude, s.Longitude, s.Total_Ridership\n)\n\n-- Assign letter grade based on the calculated safety grade\nSELECT\n Station_Full_Name as Station_Name,\n link_friendly_id,\n Latitude,\n Longitude,\n Total_Crime_Weight,\n Total_Ridership,\n Crime_Per_Capita,\n -- Rank crime per capita and scale to a 100-point grade\n 100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC) AS Safety_Grade,\n -- Assign letter grades based on safety grade thresholds\n CASE \n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 90 THEN 'A'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 75 THEN 'B'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 50 THEN 'C'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 25 THEN 'D'\n ELSE 'F'\n END AS Grade -- Assign letter grade based on the percentile\nFROM \n crime_per_capita\nORDER BY \n Safety_Grade","compiled":false,"inline":true},{"id":"station_info","compiledQueryString":"Select * from \n(WITH cleaned_stations AS (\n -- Extract station name, coordinates, and ridership for all stations\n SELECT DISTINCT\n link_friendly_id, -- Extract station name before '('\n Origin_Point,\n Origin_Station_Complex_Name as Station_Full_Name,\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude,\n SUM(Estimated_Average_Ridership) AS Total_Ridership -- Aggregate ridership for each station\n FROM \n mta_d.origin_dest_ridership_daily\n --where Origin_Station_Complex_Name NOT LIKE '%/%'\n GROUP BY \n Origin_Point, Station_Full_Name, link_friendly_id\n),\n\ncrime_data AS (\n -- Aggregate crimes by severity for all stations\n SELECT \n c.LAW_CAT_CD,\n CASE \n WHEN c.LAW_CAT_CD = 'FELONY' THEN 3 -- Assign weight 3 for felonies\n WHEN c.LAW_CAT_CD = 'MISDEMEANOR' THEN 2 -- Assign weight 2 for misdemeanors\n WHEN c.LAW_CAT_CD = 'VIOLATION' THEN 1 -- Assign weight 1 for violations\n ELSE 0\n END AS crime_weight,\n c.Latitude AS Cr_Lat, \n c.Longitude AS Cr_Long,\n c.CMPLNT_FR_DT as complaint_date\n FROM \n mta.nypd_subway_crimes_report c\n WHERE \n c.CMPLNT_FR_DT BETWEEN '2024-01-01' AND '2024-08-31' -- Date range for crimes\n),\n\ncrime_per_capita AS (\n -- Calculate crime per capita for each station\n SELECT \n \n s.Station_Full_Name,\n s.link_friendly_id,\n s.Latitude,\n s.Longitude,\n SUM(cd.crime_weight) AS Total_Crime_Weight, -- Total crime severity score for each station\n s.Total_Ridership, -- Total ridership for each station\n SUM(cd.crime_weight) / s.Total_Ridership AS Crime_Per_Capita -- Crime score per rider\n FROM \n crime_data cd\n JOIN \n cleaned_stations s \n ON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(cd.Cr_Lat)) * \n COS(RADIANS(cd.Cr_Long) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(cd.Cr_Lat))\n )\n ) <= 0.2 -- \n GROUP BY \n s.Station_Full_Name, s.link_friendly_id, s.Latitude, s.Longitude, s.Total_Ridership\n)\n\n-- Assign letter grade based on the calculated safety grade\nSELECT\n Station_Full_Name as Station_Name,\n link_friendly_id,\n Latitude,\n Longitude,\n Total_Crime_Weight,\n Total_Ridership,\n Crime_Per_Capita,\n -- Rank crime per capita and scale to a 100-point grade\n 100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC) AS Safety_Grade,\n -- Assign letter grades based on safety grade thresholds\n CASE \n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 90 THEN 'A'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 75 THEN 'B'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 50 THEN 'C'\n WHEN (100 - NTILE(100) OVER (ORDER BY Crime_Per_Capita ASC)) >= 25 THEN 'D'\n ELSE 'F'\n END AS Grade -- Assign letter grade based on the percentile\nFROM \n crime_per_capita\nORDER BY \n Safety_Grade)\nwhere link_friendly_id = '${params.station}'","inputQueryString":"Select * from \n${station_all}\nwhere link_friendly_id = '${params.station}'","compiled":true,"inline":true},{"id":"crimes_by_hour","compiledQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates for the specific station\n SELECT DISTINCT\n Origin_Station_Complex_Name AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE \n link_friendly_id = '${params.station}' -- Filter for the specific station\n),\n\ncrime_data AS (\n -- Select crimes that occurred within 500 meters of the specific station\n SELECT \n c.CMPLNT_FR_TM AS complaint_time, -- Crime complaint time\n c.Latitude AS Cr_Lat, \n c.Longitude AS Cr_Long\n FROM \n mta.nypd_subway_crimes_report c, \n cleaned_station s\n WHERE \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 -- Filter crimes within 500 meters (0.5 kilometers)\n)\n\n-- Aggregate crimes by hour of day\nSELECT \n EXTRACT(HOUR FROM CAST(complaint_time AS TIME)) AS hour_of_day,\n COUNT(*) AS total_crimes\nFROM \n crime_data\nGROUP BY \n hour_of_day\nORDER BY \n hour_of_day","inputQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates for the specific station\n SELECT DISTINCT\n Origin_Station_Complex_Name AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE \n link_friendly_id = '${params.station}' -- Filter for the specific station\n),\n\ncrime_data AS (\n -- Select crimes that occurred within 500 meters of the specific station\n SELECT \n c.CMPLNT_FR_TM AS complaint_time, -- Crime complaint time\n c.Latitude AS Cr_Lat, \n c.Longitude AS Cr_Long\n FROM \n mta.nypd_subway_crimes_report c, \n cleaned_station s\n WHERE \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 -- Filter crimes within 500 meters (0.5 kilometers)\n)\n\n-- Aggregate crimes by hour of day\nSELECT \n EXTRACT(HOUR FROM CAST(complaint_time AS TIME)) AS hour_of_day,\n COUNT(*) AS total_crimes\nFROM \n crime_data\nGROUP BY \n hour_of_day\nORDER BY \n hour_of_day","compiled":false,"inline":true},{"id":"crimes_over_time","compiledQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n date_trunc('WEEK', c.CMPLNT_FR_DT) AS complaint_week, -- Complaint date\n COUNT(*) AS total_crimes -- Total number of crimes\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 --\nWHERE \n c.CMPLNT_FR_DT IS NOT NULL and YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists\nGROUP BY \n s.Station_Name, complaint_week -- Group by station and complaint date\nORDER BY \n complaint_week, s.Station_Name -- Order by date and station","inputQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n date_trunc('WEEK', c.CMPLNT_FR_DT) AS complaint_week, -- Complaint date\n COUNT(*) AS total_crimes -- Total number of crimes\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 --\nWHERE \n c.CMPLNT_FR_DT IS NOT NULL and YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists\nGROUP BY \n s.Station_Name, complaint_week -- Group by station and complaint date\nORDER BY \n complaint_week, s.Station_Name -- Order by date and station","compiled":false,"inline":true},{"id":"crimes_by_dow","compiledQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n CASE EXTRACT(DAYOFWEEK FROM c.CMPLNT_FR_DT)\n WHEN 0 THEN 'Sunday'\n WHEN 1 THEN 'Monday'\n WHEN 2 THEN 'Tuesday'\n WHEN 3 THEN 'Wednesday'\n WHEN 4 THEN 'Thursday'\n WHEN 5 THEN 'Friday'\n WHEN 6 THEN 'Saturday'\n END AS day_of_week, -- Day of the week as a name\n COUNT(*) AS total_crimes, -- Total number of crimes\n EXTRACT(DAYOFWEEK FROM c.CMPLNT_FR_DT) AS day_of_week_order -- Day of the week for sorting\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 -- \nWHERE \n c.CMPLNT_FR_DT IS NOT NULL AND YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists and limit to 2024\nGROUP BY \n s.Station_Name, day_of_week, day_of_week_order -- Group by station and day of the week\nORDER BY \n day_of_week_order, s.Station_Name -- Order by day of the week order and station","inputQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n CASE EXTRACT(DAYOFWEEK FROM c.CMPLNT_FR_DT)\n WHEN 0 THEN 'Sunday'\n WHEN 1 THEN 'Monday'\n WHEN 2 THEN 'Tuesday'\n WHEN 3 THEN 'Wednesday'\n WHEN 4 THEN 'Thursday'\n WHEN 5 THEN 'Friday'\n WHEN 6 THEN 'Saturday'\n END AS day_of_week, -- Day of the week as a name\n COUNT(*) AS total_crimes, -- Total number of crimes\n EXTRACT(DAYOFWEEK FROM c.CMPLNT_FR_DT) AS day_of_week_order -- Day of the week for sorting\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 -- \nWHERE \n c.CMPLNT_FR_DT IS NOT NULL AND YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists and limit to 2024\nGROUP BY \n s.Station_Name, day_of_week, day_of_week_order -- Group by station and day of the week\nORDER BY \n day_of_week_order, s.Station_Name -- Order by day of the week order and station","compiled":false,"inline":true},{"id":"top_crime_categ","compiledQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n c.OFNS_DESC as Offense,\n COUNT(*) AS total_crimes -- Total number of crimes\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 --\nWHERE \n c.CMPLNT_FR_DT IS NOT NULL and YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists\nGROUP BY \n all -- Group by station and complaint date\nORDER BY \n total_crimes desc, s.Station_Name -- Order by date and station\nlimit 10","inputQueryString":"WITH cleaned_station AS (\n -- Extract station name and coordinates\n SELECT DISTINCT\n TRIM(SPLIT_PART(Origin_Station_Complex_Name, '(', 1)) AS Station_Name, -- Extract station name before '('\n CAST(TRIM(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 1)) AS DOUBLE) AS Longitude,\n CAST(TRIM(REPLACE(SPLIT_PART(SUBSTR(Origin_Point, INSTR(Origin_Point, '(') + 1), ' ', 2), ')', '')) AS DOUBLE) AS Latitude\n FROM \n mta_d.origin_dest_ridership_daily\n WHERE link_friendly_id = '${params.station}'\n)\n\nSELECT \n s.Station_Name, -- Station name\n c.OFNS_DESC as Offense,\n COUNT(*) AS total_crimes -- Total number of crimes\nFROM \n mta.nypd_subway_crimes_report c\nJOIN \n cleaned_station s\nON \n (\n 6371 * ACOS(\n COS(RADIANS(s.Latitude)) * COS(RADIANS(c.Latitude)) * \n COS(RADIANS(c.Longitude) - RADIANS(s.Longitude)) + \n SIN(RADIANS(s.Latitude)) * SIN(RADIANS(c.Latitude))\n )\n ) <= 0.2 --\nWHERE \n c.CMPLNT_FR_DT IS NOT NULL and YEAR(c.CMPLNT_FR_DT) = 2024 -- Ensure complaint date exists\nGROUP BY \n all -- Group by station and complaint date\nORDER BY \n total_crimes desc, s.Station_Name -- Order by date and station\nlimit 10","compiled":false,"inline":true}]}