-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql
76 lines (65 loc) · 2.6 KB
/
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
-- Creating a table of first three months of data for 2022
CREATE TABLE biketrips2022Q1 AS
SELECT * FROM `biketrips2022-01`
UNION
SELECT * FROM `biketrips2022-02`
UNION
SELECT * FROM `biketrips2022-03`;
-- Compare the number of trips made by casual riders vs. member riders
SELECT COUNT(ride_id) AS number_of_users, member_casual AS type_of_user
FROM biketrips2022q1
GROUP BY member_casual;
-- Compare the number of electric, docked and classic bikes used for trips
SELECT COUNT(ride_id) AS number_of_bike_type, rideable_type AS bike_type
FROM biketrips2022q1
GROUP BY bike_type;
-- 10 Most used stations to start a trip
SELECT COUNT(ride_id) AS number_of_trips_started_at_station, start_station_name
FROM biketrips2022q1
GROUP BY start_station_name
ORDER BY number_of_trips_started_at_station DESC
LIMIT 10;
-- 10 Most used stations to finish a trip
SELECT COUNT(ride_id) AS number_of_trips_finished_at_station, end_station_name
FROM biketrips2022q1
GROUP BY end_station_name
ORDER BY number_of_trips_finished_at_station DESC
LIMIT 10;
-- 10 Most used stations to finish a trip using DOCKED bike
SELECT COUNT(ride_id) AS Number_End_Stations_Docked_Bike, end_station_name AS Name_10_End_Stations_Docked_Bike
FROM biketrips2022q1
WHERE rideable_type = "docked_bike"
GROUP BY end_station_name
ORDER BY Number_End_Stations_Docked_Bike DESC
LIMIT 10;
-- 10 Most used stations to start a trip using DOCKED bike
SELECT COUNT(ride_id) AS Number_Start_Stations_Docked_Bike, start_station_name AS Name_10_Start_Stations_Docked_Bike
FROM biketrips2022q1
WHERE rideable_type = "docked_bike"
GROUP BY start_station_name
ORDER BY Number_Start_Stations_Docked_Bike DESC
LIMIT 10;
-- 10 Most used stations to finish a trip using CLASSIC bike
SELECT COUNT(ride_id) AS number_of_trips_finished_at_station, end_station_name
FROM biketrips2022q1
WHERE rideable_type = "classic_bike"
GROUP BY end_station_name
ORDER BY number_of_trips_finished_at_station DESC
LIMIT 10;
-- 10 Most used stations to finish a trip using ELECTRIC bike
SELECT COUNT(ride_id) AS number_of_trips_finished_at_station, end_station_name
FROM biketrips2022q1
WHERE rideable_type = "electric_bike"
GROUP BY end_station_name
ORDER BY number_of_trips_finished_at_station DESC
LIMIT 10;
SELECT COUNT(ride_id) AS Number_of_Usertype, member_casual AS Usertype
FROM biketrips2022q1
WHERE rideable_type = "docked_bike" && start_station_name = "Streeter Dr & Grand Ave"
GROUP BY member_casual;
SELECT COUNT(ride_id) AS Number_of_Usertype, member_casual AS Usertype
FROM biketrips2022q1
WHERE rideable_type = "docked_bike"
GROUP BY member_casual;
SELECT COUNT(ride_id) AS number_of_trips
FROM biketrips2022q1