Engineer Data in Google Cloud: Challenge Lab

// Task 1 //

CREATE OR REPLACE TABLE
taxirides.<Table_Name_as_mention_in_lab> AS
SELECT
(tolls_amount + fare_amount) AS <Fare Amount_as_mention_in_lab>,
pickup_datetime,
pickup_longitude AS pickuplon,
pickup_latitude AS pickuplat,
dropoff_longitude AS dropofflon,
dropoff_latitude AS dropofflat,
passenger_count AS passengers,
FROM
taxirides.historical_taxi_rides_raw
WHERE
RAND() < 0.001
AND trip_distance > 3          {Change_as_mention_in_lab}
AND fare_amount >= 2.0     {Change_as_mention_in_lab}
AND pickup_longitude > -78
AND pickup_longitude < -70
AND dropoff_longitude > -78
AND dropoff_longitude < -70
AND pickup_latitude > 37
AND pickup_latitude < 45
AND dropoff_latitude > 37
AND dropoff_latitude < 45
AND passenger_count > 3    {Change_as_mention_in_lab}


// Task 2 //

CREATE OR REPLACE MODEL taxirides.<Model Name_as_mention_in_lab>
TRANSFORM(
* EXCEPT(pickup_datetime)
 
, ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) AS euclidean
, CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek
, CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday
)
OPTIONS(input_label_cols=['<Fare Amount_as_mention_in_lab>'], model_type='linear_reg')
AS
 
SELECT * FROM taxirides.<Table_Name_as_mention_in_lab>


// Task 3 //

CREATE OR REPLACE TABLE taxirides.2015_fare_amount_predictions
AS
SELECT * FROM ML.PREDICT(MODEL taxirides.<Model Name_as_mention_in_lab>,(
SELECT * FROM taxirides.report_prediction_data)
)