-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcityBikeLanes.sql
More file actions
109 lines (99 loc) · 6.12 KB
/
cityBikeLanes.sql
File metadata and controls
109 lines (99 loc) · 6.12 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
/*City Bike Lanes*/
/*Create Table*/
CREATE TABLE BIT_DB.CityBikeLanes (
id integer primary key,
year_installed year not null,
year_updated year not null,
street varchar not null,
width_feet integer not null,
safetyrating integer not null,
protected varchar not null);
/*Insert data*/
INSERT INTO BIT_DB.CityBikeLanes values(1,2012, 2020, "Chestnut",4,4,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(2,2016, 2020,"Walnut",4,3.8,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(3,2011, 2020, "Market", 3.5, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(4,2008, 2020,"Locust",4,5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(5,2002, 2020,"South",4.5,4.3,"no");
INSERT INTO BIT_DB.CityBikeLanes values(6,2012, 2021, "18th",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(7,2016, 2021,"2nd",4,3.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(8,2011, 2021, "Lombard", 3.5, 2.2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(9,2008, 2021,"Pine",4,4.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(10,2002, 2021,"Tasker",4.5,4.8,"no");
INSERT INTO BIT_DB.CityBikeLanes values(11,2012, 2020, "Earp",4,4.1,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(12,2016, 2020,"Titan",4,3.8,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(13,2011, 2020, "Manning", 3.4, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(14,2008, 2020,"Fieldcrest",4,4.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(15,2002, 2020,"York",4.5,4.5,"no");
INSERT INTO BIT_DB.CityBikeLanes values(16,2012, 2021, "Race",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(17,2016, 2021,"Museum",4,3.8,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(18,2011, 2021, "Altin", 3.5, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(19,2008, 2021,"Fred",4,4.5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(20,2002, 2021,"Morris",4.5,4.7,"no");
INSERT INTO BIT_DB.CityBikeLanes values(21,2012, 2020, "Jameson",4,3.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(22,2016, 2020,"MLK",4,3.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(23,2011, 2020, "Parker", 3.6, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(24,2008, 2020,"Thomas",4,4.8,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(25,2002, 2020,"Running",4.5,4.3,"no");
INSERT INTO BIT_DB.CityBikeLanes values(26,2012, 2021, "Waverly",4,4.5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(27,2016, 2021,"Addison",4,3.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(28,2011, 2021, "Beaver", 3.5, 2.1, "no");
INSERT INTO BIT_DB.CityBikeLanes values(29,2008, 2021,"Kensington",4,5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(30,2002, 2021,"Mouse",4.5,4.5,"no");
INSERT INTO BIT_DB.CityBikeLanes values(31,2012, 2020, "Chestnut",4,4.5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(32,2016, 2020,"Walnut",4,3.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(33,2011, 2020, "Market", 3.8, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(34,2008, 2020,"Locust",4,4.8,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(35,2002, 2020,"South",4.5,4.7,"no");
INSERT INTO BIT_DB.CityBikeLanes values(36,2012, 2021, "18th",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(37,2016, 2021,"2nd",4,3.2,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(38,2011, 2021, "Lombard", 3.5, 2.5, "no");
INSERT INTO BIT_DB.CityBikeLanes values(39,2008, 2021,"Pine",4,5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(40,2002, 2021,"Tasker",4.5,4.3,"no");
INSERT INTO BIT_DB.CityBikeLanes values(41,2012, 2020, "Earp",4,4.5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(42,2016, 2020,"Titan",4,3.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(43,2011, 2020, "Manning", 3.4, 2.7, "no");
INSERT INTO BIT_DB.CityBikeLanes values(44,2008, 2020,"Fieldcrest",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(45,2002, 2020,"York",4.5,4.4,"no");
INSERT INTO BIT_DB.CityBikeLanes values(46,2012, 2021, "Race",4,4.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(47,2016, 2021,"Museum",4,3.4,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(48,2011, 2021, "Altin", 3.7, 2, "no");
INSERT INTO BIT_DB.CityBikeLanes values(49,2008, 2021,"Fred",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(50,2002, 2021,"Morris",4.5,4.4,"no");
INSERT INTO BIT_DB.CityBikeLanes values(51,2012, 2020, "Jameson",4,4,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(52,2016, 2020,"MLK",4,4,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(53,2011, 2020, "Parker", 3.6, 2.3, "no");
INSERT INTO BIT_DB.CityBikeLanes values(54,2008, 2020,"Thomas",4,4.5,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(55,2002, 2020,"Running",4.5,4.5,"no");
INSERT INTO BIT_DB.CityBikeLanes values(56,2012, 2021, "Waverly",4,4.7,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(57,2016, 2021,"Addison",4,3.6,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(58,2011, 2021, "Beaver", 3.5, 2.5, "no");
INSERT INTO BIT_DB.CityBikeLanes values(59,2008, 2021,"Kensington",4,4.9,"yes");
INSERT INTO BIT_DB.CityBikeLanes values(60,2002, 2021,"Mouse",4.5,4.3,"no");
-- Get an overview of the data.
SELECT * FROM CityBikeLanes LIMIT 10;
-- Run this SQL and make note of what you see. Why are there two rows in the results?
SELECT * FROM CityBikeLanes WHERE street='Walnut';
/*A list of all the bike lanes that have an average safety rating of 4.0 or higher and the average safety rating for each of those bike lanes, and a label that says "Safe Lane*/
-- Query made With CTE
WITH safety_average_cte AS (
SELECT street, AVG(safetyrating) AS avg_safety_rating
FROM CityBikeLanes
GROUP BY street)
SELECT street,avg_safety_rating,'Safe Lane' AS 'label'
FROM safety_average_cte
WHERE avg_safety_rating >= 4;
-- Query without CTE
SELECT street, AVG(safetyrating) as 'rating', 'Safe Lane' AS 'label'
FROM Citybikelanes
GROUP BY street
having rating >=4
/*a list of all the bike lanes, both safety ratings for each lane, the average safety rating for each lane,
and a label with the recommendation as "Remove", "Leave As-Is", or "Improvements Needed".*/
SELECT street, safetyrating,
AVG(safetyrating) OVER (PARTITION BY street) AS 'Average_safety_rating',
CASE
WHEN AVG(safetyrating) OVER (PARTITION BY street) >= 4 THEN 'Leave As-Is'
WHEN AVG(safetyrating) OVER (PARTITION BY street) <= 2.5 THEN 'Remove'
ELSE 'Improvements Needed'
END AS 'Recommendation'
FROM CityBikeLanes;