<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Heuristic Benchmark
This notebook demonstrates the Heuristic Benchmark design pattern
1. Regression on poorly understood features
Problem
Step1:  Find the error metric of always predicting that it will take 2120 seconds to get an answer. This the baseline metric against which to report model performance.
Step2:  2. Classification on poorly understood features
Problem
Step3:  Problem
Step4:  3. Regression with one good numeric feature
Problem
Step5:  4. Regression with one or two important features
Problem
Step6:  Now, use this benchmark to compute the overall RMSE, so that you can compare with the model.
<ASSISTANT_TASK:>
Python Code:
%%bigquery
SELECT 
  bqutil.fn.median(ARRAY_AGG(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND))) AS time_to_answer
FROM `bigquery-public-data.stackoverflow.posts_questions` q
JOIN `bigquery-public-data.stackoverflow.posts_answers` a
ON q.accepted_answer_id = a.id
Explanation: Heuristic Benchmark
This notebook demonstrates the Heuristic Benchmark design pattern
1. Regression on poorly understood features
Problem: Time interval before a question on Stack Overflow is answered.
Benchmark: Median time to first answer over the entire training dataset, so 2120 seconds.
End of explanation
%%bigquery
WITH benchmark_eval AS (
SELECT 
  2120 - TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND) AS error
FROM `bigquery-public-data.stackoverflow.posts_questions` q
JOIN `bigquery-public-data.stackoverflow.posts_answers` a
ON q.accepted_answer_id = a.id
)
SELECT
   AVG(ABS(error)) AS mean_absolute_error
FROM
   benchmark_eval
Explanation: Find the error metric of always predicting that it will take 2120 seconds to get an answer. This the baseline metric against which to report model performance.
End of explanation
%%bigquery
SELECT 
  AVG(IF(a.last_edit_date IS NULL, 0, 1)) AS prob_edited
FROM `bigquery-public-data.stackoverflow.posts_questions` q
JOIN `bigquery-public-data.stackoverflow.posts_answers` a
ON q.accepted_answer_id = a.id
Explanation: 2. Classification on poorly understood features
Problem: Whether or not an accepted answer will be edited.
Benchmark: Probability distribution of accepted answers that are edited.
End of explanation
%%bigquery
SELECT 
  COUNTIF(ENDS_WITH(u.location, 'France')) / COUNT(u.location) AS from_france,
  COUNTIF(ENDS_WITH(u.location, 'India')) / COUNT(u.location) AS from_india
FROM `bigquery-public-data.stackoverflow.posts_questions` q
JOIN `bigquery-public-data.stackoverflow.posts_answers` a
ON q.accepted_answer_id = a.id
JOIN `bigquery-public-data.stackoverflow.users` u
ON u.id = a.owner_user_id
Explanation: Problem: Country from which a Stack Overflow question will be answered.
Benchmark: Fractions of answers written by people from France, India, and so on.
End of explanation
%%bigquery
With trips AS (
SELECT
  total_amount,
  ST_Distance(ST_GeogPoint(pickup_longitude, pickup_latitude),
              ST_GeogPoint(dropoff_longitude, dropoff_latitude))/1000 AS dist
FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE pickup_latitude BETWEEN 35 and 45
AND dropoff_latitude BETWEEN 35 and 45
AND pickup_longitude BETWEEN -80 and -70
AND dropoff_longitude BETWEEN -80 and -70
AND total_amount IS NOT NULL
)
SELECT AVG(total_amount)/AVG(dist)
FROM trips
Explanation: 3. Regression with one good numeric feature
Problem: Predict taxi fare amount given pickup and dropoff locations.
The distance between the two points is, intuitively, a key feature.
Benchmark: linear regression based on this feature
End of explanation
%%bigquery
CREATE TEMPORARY FUNCTION is_peak_hour(start_date TIMESTAMP) aS
(EXTRACT(DAYOFWEEK FROM start_date) BETWEEN 2 AND 6 -- weekday
    AND (
       EXTRACT(HOUR FROM start_date) BETWEEN 6 AND 10
       OR
       EXTRACT(HOUR FROM start_date) BETWEEN 15 AND 18))
;
SELECT 
   start_station_name,
   is_peak_hour(start_date) AS is_peak,
   AVG(duration) AS predicted_duration,
FROM `bigquery-public-data.london_bicycles.cycle_hire`
GROUP BY 1, 2
ORDER BY predicted_duration DESC
LIMIT 10
Explanation: 4. Regression with one or two important features
Problem: Predict duration of bicycle rental.
Benchmark: Lookup table
End of explanation
%%bigquery
CREATE TEMPORARY FUNCTION is_peak_hour(start_date TIMESTAMP) aS
(EXTRACT(DAYOFWEEK FROM start_date) BETWEEN 2 AND 6 -- weekday
    AND (
       EXTRACT(HOUR FROM start_date) BETWEEN 6 AND 10
       OR
       EXTRACT(HOUR FROM start_date) BETWEEN 15 AND 18))
;
WITH benchmark AS (
SELECT 
   start_station_name,
   is_peak_hour(start_date) AS is_peak,
   AVG(duration) AS predicted_duration,
FROM `bigquery-public-data.london_bicycles.cycle_hire`
GROUP BY 1, 2
)
SELECT
   SQRT( SUM( (duration - predicted_duration)*(duration - predicted_duration)) / COUNT(duration) ) AS rmse
FROM `bigquery-public-data.london_bicycles.cycle_hire` c
JOIN benchmark b
ON c.start_station_name = b.start_station_name AND is_peak_hour(c.start_date) = b.is_peak
Explanation: Now, use this benchmark to compute the overall RMSE, so that you can compare with the model.
End of explanation
<END_TASK>