proompt

[{"column_name":"unique_id","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"cnn_intrsctn_fkey","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"cnn_sgmt_fkey","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"case_id_pkey","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"tb_latitude","column_type":"DOUBLE","null":"YES","key":null,"default":null,"extra":null},{"column_name":"tb_longitude","column_type":"DOUBLE","null":"YES","key":null,"default":null,"extra":null},{"column_name":"geocode_source","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"geocode_location","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"collision_datetime","column_type":"TIMESTAMP","null":"YES","key":null,"default":null,"extra":null},{"column_name":"collision_date","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"collision_time","column_type":"TIME","null":"YES","key":null,"default":null,"extra":null},{"column_name":"accident_year","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"month","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"day_of_week","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"time_cat","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"juris","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"officer_id","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"reporting_district","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"beat_number","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"primary_rd","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"secondary_rd","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"distance","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"direction","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"weather_1","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"weather_2","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"collision_severity","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"type_of_collision","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"mviw","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"ped_action","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"road_surface","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"road_cond_1","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"road_cond_2","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"lighting","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"control_device","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"intersection","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"vz_pcf_code","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"vz_pcf_group","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"vz_pcf_description","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"vz_pcf_link","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"number_killed","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"number_injured","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"street_view","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"dph_col_grp","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"dph_col_grp_description","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party_at_fault","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party1_type","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party1_dir_of_travel","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party1_move_pre_acc","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party2_type","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party2_dir_of_travel","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"party2_move_pre_acc","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"point","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"data_as_of","column_type":"TIMESTAMP","null":"YES","key":null,"default":null,"extra":null},{"column_name":"data_updated_at","column_type":"TIMESTAMP","null":"YES","key":null,"default":null,"extra":null},{"column_name":"data_loaded_at","column_type":"TIMESTAMP","null":"YES","key":null,"default":null,"extra":null},{"column_name":"analysis_neighborhood","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"supervisor_district","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"police_district","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},{"column_name":"Current Police Districts","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"Current Supervisor Districts","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"Analysis Neighborhoods","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"Neighborhoods","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null},{"column_name":"SF Find Neighborhoods","column_type":"BIGINT","null":"YES","key":null,"default":null,"extra":null}]

    Here are some example values from each column:

    Column "unique_id" example values:
53681, 21421, 33405, 42048, 15015

Column "cnn_intrsctn_fkey" example values:
24590000, 33540000, 50065000, 24448000, 26976000

Column "cnn_sgmt_fkey" example values:
182000, 12666000, 10557000, 12878000, 7037000

Column "case_id_pkey" example values:
5410299, 190956816, 170502514, 140443465, 140641562

Column "tb_latitude" example values:
37.78354183902543, 37.7176320597972, 37.776746108794995, 37.797504944749285, 37.77555816292355

Column "tb_longitude" example values:
-122.46221250299998, -122.45926013301812, -122.42847787748761, -122.43129180731593, -122.4065094377171

Column "geocode_source" example values:
SFPD-INTERIM DB, UCB-TIMS, SFPD-CROSSROADS

Column "geocode_location" example values:
CITY STREET

Column "collision_datetime" example values:
Tue May 19 2009 04:31:00 GMT-0700 (Pacific Daylight Time), Thu May 21 2009 07:20:00 GMT-0700 (Pacific Daylight Time), Wed Aug 25 2010 09:34:00 GMT-0700 (Pacific Daylight Time), Tue Mar 23 2010 13:30:00 GMT-0700 (Pacific Daylight Time), Thu May 12 2011 06:33:00 GMT-0700 (Pacific Daylight Time)

Column "collision_date" example values:
2017 July 03, 2008 November 26, 2009 August 09, 2010 March 23, 2020 November 25

Column "collision_time" example values:
16:22:00, 09:01:00, 10:00:00, 15:53:00, 02:18:00

Column "accident_year" example values:
2013, 2012, 2024, 2018, 2020, 2021, 2016, 2005, 2006, 2026, 2019, 2022, 2010, 2014, 2011, 2017, 2008, 2025, 2015, 2023, 2007, 2009

Column "month" example values:
April, October, December, March, November, July, May, January, June, September, August, February

Column "day_of_week" example values:
Friday, Saturday, Tuesday, Monday, Wednesday, Sunday, Thursday

Column "time_cat" example values:
10:01 pm to 2:00 am, 6:01 pm to 10:00 pm, 2:01 am to 6:00 am, 10:01 am to 2:00 pm, 6:01 am to 10:00 am, 2:01 pm to 6:00 pm

Column "juris" example values:
2419, 1688, 2181, WATTS, 9350, 195, 1398, 3801, A22327, 9335, 4180

Column "officer_id" example values:
1693, 4246, 821, 1253, 1356

Column "reporting_district" example values:
Traffic, SANFR, CO F, BAYVI, BAYVIEW

Column "beat_number" example values:
1, SOUTHE, 3E14D, 3F13D, B1

Column "primary_rd" example values:
STANYAN ST, APPLETON AVE, GENEVA AVE, OAKDALE AVE, JOHN F KENNEDY DR

Column "secondary_rd" example values:
KENSINGTON WAY, BERRY ST, 14TH ST, SUTTER ST, JOHN F KENNEDY DR

Column "distance" example values:
0, 97, 16, 26, 110

Column "direction" example values:
South, Not Stated, North Nor, North, West, East

Column "weather_1" example values:
Fog, Not Stated, Wind, Other: NOT ON SCENE, Cloudy, Other, Clear, Snowing, Other: MISTING, Fog / Visibility, Fog / Visibility: 10 ft, Fog / Visibility: 800 ft, Other: NOT AT SCENE, Raining, Other: Unknown

Column "weather_2" example values:
Other: Misty, Fog, Other: Light drizzle (rain), Other: MIST, Other: HEAVY MIST, Other: SUN GLARING, Other: light drizzle, Other: SMOKEY SKIES, Other: SUNSET, Other: LIGHT MIST, Other: Sunset, Cloudy, Other, Other: FALLING ASH, Other: Mist, Not Stated, Wind, Other: NOT ON SCENE, Fog / Visibility: 15 ft, Raining, Other: smoke cloud, Other: Drizzling, Other: SUN IN WB VIEW, Other: SUNRISE, Other: Overcast, Other: Sunny, Other: SUNNY, Other: Night, Fog / Visibility, Other: MISTING, Other: SMOKEY, Fog / Visibility: 30 ft, Fog / Visibility: 10 ft, Fog / Visibility: 500 ft, Other: drizzle, Fog / Visibility: 100 ft, Other: LIGHT SPRINKLE, Other: WET PAVEMENT, Snowing, Other: SLIGHT MIST

Column "collision_severity" example values:
Fatal, Medical, Injury (Severe), Injury (Other Visible), Injury (Complaint of Pain)

Column "type_of_collision" example values:
Head-On, Not Stated, Rear End, Hit Object, Other, Broadside, Overturned, Sideswipe, Vehicle/Pedestrian

Column "mviw" example values:
Not Stated, Motor Vehicle on Other Roadway, Train, Bicycle, Fixed Object, Non-Collision, Animal, Parked Motor Vehicle, Pedestrian, Other Object, Other Motor Vehicle

Column "ped_action" example values:
Not Stated, Not in Road, In Road, Including Shoulder, Crossing in Crosswalk at Intersection, Crossing Not in Crosswalk, Approaching/Leaving School Bus, Not In Road, No Pedestrian Involved, Crossing in Crosswalk Not at Intersection

Column "road_surface" example values:
Not Stated, Snowy or Icy, Wet, Dry, Slippery

Column "road_cond_1" example values:
Construction or Repair Zone, Not Stated, Holes, Deep Rut, No Unusual Condition, Reduced Roadway Width, Holes, Deep Ruts, Other, Flooded, Loose Material on Roadway, Obstruction on Roadway

Column "road_cond_2" example values:
No Unusual Condition, Reduced Roadway Width, Not Stated, Obstruction on Roadway, Loose Material on Roadway, Other, Flooded, Construction or Repair Zone

Column "lighting" example values:
Dusk - Dawn, Daylight, Not Stated, Dark - Street Lights Not Functioning, Dark - Street Lights, Dark - No Street Lights

Column "control_device" example values:
Not Functioning, Functioning, None, Obscured, Not Stated

Column "intersection" example values:
Intersection <= 20ft, Intersection Rear End <= 150ft, Midblock > 20ft

Column "vz_pcf_code" example values:
Other improper driving, Not Stated, 21658(a), 22101(d), 21956(a)

Column "vz_pcf_group" example values:
Not Stated, 21801(a,b), 22101(d), 21956(a), 22109

Column "vz_pcf_description" example values:
Unsafe turn or lane change prohibited, Not Stated, Failure to keep to right side of road, Red signal - pedestrian responsibilities, Driving under influence causing injury

Column "vz_pcf_link" example values:
http://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=VEH&sectionNum=22100, http://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=VEH&sectionNum=21456, http://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=VEH&sectionNum=21802, http://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=VEH&sectionNum=21663, http://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=VEH&sectionNum=21055

Column "number_killed" example values:
2, 1, 4, 0, 3

Column "number_injured" example values:
5, 17, 9, 14, 7, 8, 13, 19, 0, 4, 2, 18, 10, 3, 6, 12, 1

Column "street_view" example values:
https://maps.google.com/maps?q=&layer=c&cbll=37.74043559871381,-122.45926013301815, https://maps.google.com/maps?q=&layer=c&cbll=37.7709296593946,-122.455289746292, https://maps.google.com/maps?q=&layer=c&cbll=37.7690849912358,-122.453520763401, https://maps.google.com/maps?q=&layer=c&cbll=37.7538386347279,-122.481820320509, https://maps.google.com/maps?q=&layer=c&cbll=37.776746108794995,-122.40784206069482

Column "dph_col_grp" example values:
GG, AA, BB, DD, BB CC, HH, II, FF, CC, EE

Column "dph_col_grp_description" example values:
Vehicle-Bicycle, Pedestrian Only or Pedestrian-Parked Car, Bicycle Only, Bicycle-Parked Car, Vehicle-Bicycle-Pedestrian, Vehicle-Pedestrian, Vehicle(s) Only Involved, Bicycle-Unknown/Not Stated, Unknown/Not Stated, Bicycle-Pedestrian

Column "party_at_fault" example values:
3, 1, 2, 5, 4

Column "party1_type" example values:
Pedestrian, Parked Vehicle, Driver, Not Stated, Bicycle, Bicyclist, Other

Column "party1_dir_of_travel" example values:
East, North, West, South, Not Stated

Column "party1_move_pre_acc" example values:
Not Stated, Crossed Into Opposing Lane, Stopped, Entering Traffic, Proceeding Straight, North, Other, Slowing/Stopping, Parking Maneuver, Passing Other Vehicle, Merging, Other Unsafe Turning, Ran Off Road, Making U Turn, Backing, Making Right Turn, Parked, Making Left Turn, Crossed Into Opposing Lane - Unplanned, Xing into Opposing Lane, Stopped In Road, Changing Lanes, Traveling Wrong Way

Column "party2_type" example values:
Bicyclist, Parked Vehicle, Other, Driver, Not Stated, Pedestrian

Column "party2_dir_of_travel" example values:
East, Not Stated, North, West, South

Column "party2_move_pre_acc" example values:
Not Stated, Crossed Into Opposing Lane, Entering Traffic, Stopped, Making U Turn, Ran Off Road, Other, Passing Other Vehicle, Slowing/Stopping, Merging, Parking Maneuver, Other Unsafe Turning, Stopped In Road, Changing Lanes, Traveling Wrong Way, Parked, Crossed Into Opposing Lane - Unplanned, Backing, Making Left Turn, Making Right Turn, Proceeding Straight

Column "point" example values:
POINT (-122.382239151 37.72906098), POINT (-122.390368468 37.776928365), POINT (-122.433449653 37.770270297), POINT (-122.465016987 37.78091065), POINT (-122.400981568 37.774808776)

Column "data_as_of" example values:
Mon May 16 2022 17:00:00 GMT-0700 (Pacific Daylight Time), Wed Jan 07 2015 16:00:00 GMT-0800 (Pacific Standard Time), Wed Feb 18 2009 16:00:00 GMT-0800 (Pacific Standard Time), Sat Aug 08 2009 17:00:00 GMT-0700 (Pacific Daylight Time), Thu Aug 25 2011 17:00:00 GMT-0700 (Pacific Daylight Time)

Column "data_updated_at" example values:
Sun Apr 28 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Wed Apr 26 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Thu Apr 25 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Mon Sep 16 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Tue Apr 25 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Thu Apr 06 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Sun Mar 27 2022 17:00:00 GMT-0700 (Pacific Daylight Time), Tue May 02 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Mon Apr 15 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Mon Dec 11 2023 16:00:00 GMT-0800 (Pacific Standard Time), Tue Oct 01 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Thu May 04 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Thu Apr 18 2024 17:00:00 GMT-0700 (Pacific Daylight Time), Wed Apr 05 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Mon May 01 2023 17:00:00 GMT-0700 (Pacific Daylight Time), Wed Oct 02 2024 17:00:00 GMT-0700 (Pacific Daylight Time)

Column "data_loaded_at" example values:
Thu Oct 03 2024 06:51:28 GMT-0700 (Pacific Daylight Time)

Column "analysis_neighborhood" example values:
Haight Ashbury, Tenderloin, Inner Richmond, Marina, North Beach, McLaren Park, Bayview Hunters Point, Outer Mission, Pacific Heights, Twin Peaks, Mission Bay, Japantown, Hayes Valley, Potrero Hill, Treasure Island, Lone Mountain/USF, Sunset/Parkside, Lakeshore, Presidio, South of Market, Castro/Upper Market, Presidio Heights, Outer Richmond, Oceanview/Merced/Ingleside, Nob Hill, Mission, Bernal Heights, Noe Valley, Russian Hill, Visitacion Valley, Inner Sunset, Seacliff, Golden Gate Park, Portola, Western Addition, Lincoln Park, Financial District/South Beach, West of Twin Peaks, Chinatown, Glen Park, Excelsior

Column "supervisor_district" example values:
6, 10, 5, 1, 2, 11, 3, 7, 4, 8, 9

Column "police_district" example values:
TARAVAL, BAYVIEW, MISSION, PARK, TENDERLOIN, SOUTHERN, NORTHERN, RICHMOND, CENTRAL, INGLESIDE

Column "Current Police Districts" example values:
1, 9, 8, 3, 4, 7, 2, 10, 6, 5

Column "Current Supervisor Districts" example values:
10, 6, 5, 3, 11, 4, 7, 1, 2, 8, 9

Column "Analysis Neighborhoods" example values:
3, 11, 35, 30, 40, 29, 19, 15, 31, 36, 6, 10, 18, 38, 34, 24, 41, 5, 25, 8, 28, 9, 20, 14, 1, 12, 16, 23, 32, 33, 17, 27, 26, 2, 22, 4, 21, 13, 39, 7, 37

Column "Neighborhoods" example values:
51, 108, 43, 75, 109

Column "SF Find Neighborhoods" example values:
32, 68, 33, 82, 56
    
    The table is named temp_table. 
    
    Generate a SQL query that answers the question: pedestrian accidents deaths safety mission district san francisco
    
    Apply aliases to the columns in the query to make it more readable.
    i.e accident_count -> Accident Count