Evaluation Query

[varshasahasrabuddhe]

Hi Mike,
Have a question regarding the evaluation query.
When we are trying to Calculate the time between TIME_COLUMN and prediction_on{TIME_COLUMN} and keep the rows that are 0 = same day,
can the below query
WITH
FORECASTS AS (
SELECT
DATE(starttime) as starttime,
DATE(predicted_on_starttime) as predicted_on_starttime,
CAST(num_trips as INT64) AS num_trips,
start_station_name,
predicted_num_trips.value as predicted_num_trips
FROM *******__forecasting.automl-python_eval
),
LEAD_DAYS AS (
SELECT *,
DATE_DIFF(starttime, predicted_on_starttime, DAY) as prediction_lead_days
FROM FORECASTS
),
LATEST AS (
SELECT start_station_name, starttime, min(prediction_lead_days) as prediction_lead_days
FROM LEAD_DAYS
GROUP BY start_station_name, starttime
)
SELECT *
FROM LATEST
LEFT OUTER JOIN LEAD_DAYS
USING (start_station_name, starttime, prediction_lead_days)
ORDER BY start_station_name, starttime

simply be replaced by

SELECT * FROM *******__forecasting.automl-python_eval
where starttime = predicted_on_starttime

[statmike]

This brings up an excellent point. The shape of the evaluation data is a bit different and easier to work compared to when I originally wrote these sections of the examples. This idea does work but will need a bit more than SELECT * due to the variable types changing. I will work a change and submit this week. Once I submit it I will comment back here with a link to the commit for reference.
Thank You @varshasahasrabuddhe

[statmike]

The queries related to the evaluation tables are now simplified. The changes were pushed in this commit.

Thank you again for bringing this up!

[varshasahasrabuddhe]

Thank you mike for such a prompt response.
That brings me with more questions on the structure of the Evaluation data. I didn't understand why there are multiple rows for starttime.
May be you can explain that in a video series for 'Applied Forecasting'