Unveiling Cyclistic Bike-Share Insights Through Data Analysis

-- Appending all year table into one
Create Table combined_data 
(
 [ride_id] varchar(50) Primary Key,
    [rideable_type] varchar(50),
    [started_at] datetime,
    [ended_at] datetime,
    [start_station_name] varchar(100),
    [start_station_id] varchar(50),
    [end_station_name] varchar(100),
    [end_station_id] varchar(50),
    [start_lat] float,
    [start_lng] float,
    [end_lat] float,
    [end_lng] float,
    [member_casual] varchar(50)
)

Insert Into combined_data 
(
 [ride_id],
    [rideable_type],
    [started_at],
    [ended_at],
    [start_station_name],
    [start_station_id],
    [end_station_name],
    [end_station_id],
    [start_lat],
    [start_lng],
    [end_lat],
    [end_lng],
    [member_casual]
)

Select * from Cyclistic.dbo.[2022-01-tripdata]
Union All

-- Getting rid of unwanted columns - Data Cleaning
Create Table TripData
(
 ride_id varchar(50) Primary Key,
 rideable_type varchar(50),
 started_at datetime,
 ended_at datetime,
 start_station_name varchar(100),
 start_station_id varchar(50),
 end_station_name varchar(100),
 end_station_id varchar(50),
 user_cat varchar(50)
)

Insert Into TripData
(
 ride_id,
 rideable_type,
 started_at,
 ended_at,
 start_station_name,
 start_station_id,
 end_station_name,
 end_station_id,
 user_cat
)

Select
 ride_id,
 rideable_type,
 started_at,
 ended_at,
 start_station_name,
 start_station_id,
 end_station_name,
 end_station_id,
 user_cat = member_casual
From
 combined_data

-- Joining tables all together
Select
 a.ride_id,
 b.rideable_type_id,
 a.rideable_type,
 h.day_of_week_name,
 h.day_of_week_number,
 h.day__of_month_number,
 h.weekend,
 h.month_number,
 h.year_number,
 c.start_date,
 c.start_time,
 c.end_date,
 c.end_time,
 c.start_station_id,
 c.start_station_name,
 c.end_station_id,
 c.end_station_name,
 j.user_cat as user_category

Into joined_tables
From master_table a
 Left Join rideable_type b
 On a.rideable_type = b.distinct_rideable_type

 Left Join ride_data c
 On a.ride_id = c.ride_id

 Left Join calendar h
 On c.start_date = h.date_value

 Left Join user_cat_data j
 On a.user_cat = j.user_cat

-- Aggregated Summary for exportation & Visualization
Select
 a.user_category,
 a.rideable_type,
 DATEFROMPARTS(2022, a.month_number, a.day__of_month_number) as date,
 DATEPART(QUARTER, a.start_date) as quarter,
 DATENAME(MONTH, a.start_date) as month,
 a.day__of_month_number,
 a.day_of_week_number,
 a.day_of_week_name,
 DATEPART(hour,(CAST(a.start_time as time))) as hour,
 SUM(a.min_diff) as total_lengths,
 avg(a.min_diff) AS average_ride_length,
 count(a.ride_id) as ride_id_count,
 COUNT(case when a.min_diff <= 10 then 'short ride' end) as short_rides,
 count(case when a.min_diff > 10 and a.min_diff <= 15 then 'average ride' end) as average_rides,
 COUNT(case when a.min_diff > 15 then 'long ride' end) as long_rides

From 
 joined_tables a
Group by
 a.user_category,
 a.rideable_type,
 a.month_number,
 DATEPART(QUARTER, a.start_date),
 DATENAME(MONTH, a.start_date),
 a.day__of_month_number,
 a.day_of_week_number,
 a.day_of_week_name,
 DATEPART(hour,(CAST(a.start_time as time)))

Order by 
 a.month_number,
 DATEPART(

QUARTER, a.start_date),
 a.day__of_month_number,
 a.day_of_week_number,
 DATEPART(hour,(CAST(a.start_time as time)))