-
Notifications
You must be signed in to change notification settings - Fork 0
/
PortfolioProject_ExploringData.sql
79 lines (66 loc) · 1.89 KB
/
PortfolioProject_ExploringData.sql
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
--2: Exploring Data
--The query explores the data in the newly created "2022_TRIP_DATA" table by checking if the table exists, viewing the table, and then performing various analyses on the data.
--We first finds the number of rides by casual and members, the number of rideable types, the number of rides ending at each docking station, the number of rides starting at each docking station, the number of round trips, and the total number of trips.
-- Checking if the new created table for the combined tables exists
SELECT
table_name
FROM
INFORMATION_SCHEMA.TABLES
WHERE
table_name =
'2022_TRIP_DATA';
-- Above query confirms that new table exits
-- View table
SELECT *
FROM
"2022_TRIP_DATA"
-- Find the number of rides by casual and members
SELECT
MEMBER_CASUAL, COUNT(*) AS number_of_rides
FROM
"2022_TRIP_DATA"
GROUP BY
MEMBER_CASUAL;
-- View number of rideable types
SELECT
rideable_type_id, COUNT(1)
FROM
"2022_TRIP_DATA"
GROUP BY
rideable_type_id;
-- View rides ending at each docking station
SELECT
END_STATION_ID, END_STATION_NAME, COUNT(1) AS rides
FROM
"2022_TRIP_DATA"
GROUP BY
END_STATION_ID, END_STATION_NAME
ORDER BY
rides DESC;
-- View rides starting at each docking station
SELECT
START_STATION_ID, START_STATION_NAME, COUNT(1) AS rides
FROM
"2022_TRIP_DATA"
GROUP BY
START_STATION_ID, START_STATION_NAME
ORDER BY
rides DESC;
-- View number of round trips
SELECT
START_STATION_ID, END_STATION_ID,RIDEABLE_TYPE_ID,MEMBER_CASUAL
FROM
"2022_TRIP_DATA"
WHERE
START_STATION_ID = END_STATION_ID;
SELECT COUNT(*)
FROM
"2022_TRIP_DATA"
WHERE
START_STATION_ID = END_STATION_ID;
-- View total number of trips
SELECT
COUNT(*)
FROM
"2022_TRIP_DATA"
--I notice 892,742 round trips have no END_STATION_ID or END_STATION_NAME, data quality checks will be needed in the next query