Unrecognized name: trips at [17:3]

Question

This is the code I want to execute but keep on getting this error;

With 
longest_used_bike AS (
    SELECT 
        Bikeid,
        SUM(duration_minutes) AS trip_duration
    FROM 
        bigquery-public-data.austin_bikeshare.bikeshare_trips
    GROUP BY 
        Bikeid
    ORDER BY 
        Trip_duration DESC
    LIMIT 1
)

## find station at which the longest-used bike leaves most often
SELECT 
  trips.start_station_id,
  COUNT (*) AS trip_ct
FROM longest_used_bike AS longest

Answer 1

longest_used_bike table doesn't have any information other than a bikeid which has been used most, so you need to find start_stataion_id and calculate the count of it from the original table by joining two tables or simply filtering in WHERE clause like below:

With longest_used_bike AS (
  SELECT bikeid, SUM(duration_minutes) AS trip_duration 
    FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   GROUP BY bikeid ORDER BY trip_duration DESC LIMIT 1
)
SELECT start_station_id, COUNT (*) AS trip_ct 
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
 WHERE bikeid = (SELECT bikeid FROM longest_used_bike)
 GROUP BY 1 ORDER BY 2 DESC;

output:
+------------------+---------+
| start_station_id | trip_ct |
+------------------+---------+
|             3798 |     102 |
|             2575 |      95 |
|             2707 |      85 |
|             2498 |      80 |
|             2494 |      78 |
|             2574 |      74 |
|             2501 |      74 |
|              ... |     ... |
+------------------+---------+