Data1: bigquery-public-data.austin_bikeshare.bikeshare_stations

Data2: bigquery-public-data.austin_bikeshare.bikeshare_trips

Select * From bigquery-public-data.austin_bikeshare.bikeshare_stations Limit 500;

Count(*) as records

,Count(Distinct station_id) as stations

bigquery-public-data.austin_bikeshare.bikeshare_stations

;

Select * From bigquery-public-data.austin_bikeshare.bikeshare_trips Limit 5000

Count(*) as records

,Count(Distinct trip_id) as trips

,Count(Distinct start_station_id) as start_stations

,Count(Distinct end_station_id) as end_stations

bigquery-public-data.austin_bikeshare.bikeshare_trips

;

start_station_id

,count(distinct trip_id) as trips

bigquery-public-data.austin_bikeshare.bikeshare_trips

start_station_id

trips DESC

;

A.*

,B.name as Station_Name

,B.address as Station_Address

,B.status as Station_Status

,B.property_type as Station_Property_Type

(

Select

start_station_id

,count(distinct trip_id) as trips

From

bigquery-public-data.austin_bikeshare.bikeshare_trips

Group BY

start_station_id

Order By

trips DESC

) as A

bigquery-public-data.austin_bikeshare.bikeshare_stations as B

A.start_station_id = B.station_id

;

With Trips As

(

Select

start_station_id

,count(distinct trip_id) as trips

From

bigquery-public-data.austin_bikeshare.bikeshare_trips

Group BY

start_station_id

Order By

trips DESC

),

Stations As

(

Select

station_id

,name

,address

,status

,property_type

From

bigquery-public-data.austin_bikeshare.bikeshare_stations

)

A.*

,B.name as Station_Name

,B.address as Station_Address

,B.status as Station_Status

,B.property_type as Station_Property_Type

Trips as A

Stations as B

A.start_station_id = B.station_id

;

With Trips As

(

Select

start_station_id

,count(distinct trip_id) as trips

From

bigquery-public-data.austin_bikeshare.bikeshare_trips

Group BY

start_station_id

),

Stations As

(

Select

station_id

,name

,address

,status

,property_type

From

bigquery-public-data.austin_bikeshare.bikeshare_stations

)

A.*

,B.name as Station_Name

,B.address as Station_Address

,B.status as Station_Status

,B.property_type as Station_Property_Type

Trips as A

Stations as B

A.start_station_id = B.station_id

Order By trips

Limit 5

;

With Sequence As

(

*

,Row_Number() Over (Partition By start_station_id Order By start_time ASC) as trip_sequence_asc

,Row_Number() Over (Partition By start_station_id Order By start_time DESC) as trip_sequence_desc

bigquery-public-data.austin_bikeshare.bikeshare_trips

start_station_id in (2500,1008)

)

*

,Case

When trip_sequence_asc = 1 Then "First Trip"

When trip_sequence_desc = 1 Then "Last Trip"

End as Trip_Category

Sequence

trip_sequence_asc = 1

OR

trip_sequence_desc = 1

;