I have an error in BigQuery Sql while following teacher's post to make a temp table, it doesn't like the parentheses and I don't know why?

Question

WITH trips_over_1_hr AS 
(
    SELECT *
    FROM
        `bigquery-public-data.new_york_citibike.citibike_trips`
    WHERE 
        tripduration >= 60
)

I typed this as the teacher demonstrated and then got this error

Syntax error: Unexpected end of script at [8:2]

So I don't know what to do about it.

I tried deleting the parenthesis, moving the parentheses, rewrote the whole thing, and I don't know what else to try.

Answer 1

The “WITH CTE_Name AS ()” is a CTE. Using CTE requires a SELECT to follow after (). Just add a SELECT from CTE_Name.

However, usage of CTE is for preparing dataset that you will eventually join or filter down in your final select. So you use it in something like below:

WITH CTE_Name AS
(
    SELECT Columns 
    FROM Table1 
    WHERE Column = Criteria
)
SELECT *
FROM Table2 t2
  JOIN CTE_Name CTE on CTE.ColumnX=t2.ColumnX;

Answer 2

I'm new to data analytics too and ran into the same error when I was running the 'WITH' clause example after ')'.

I understand how this is confusing. A video tutorial for the 'WITH' clause had a demo that returned results, so I also got confused about why I was getting an error instead of a temp table.

SOLUTION:

Your instructions probably has a "part 2" that you need to continue after ')'. I ran the example below and the error wasn't coming up anymore.

---

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

YOU NEED TO CONTINUE WITH THE REST OF THE QUERY SIMILAR TO THE QUERY BELOW

SELECT Trips.start_station_id, COUNT (*) AS trip_ct FROM longest_used_bike AS longest INNER JOIN bigquery-public-data.austin_bikeshare.bikeshare_trips AS Trips ON longest.bike_id = trips.bike_id GROUP BY trips.start_station_id ORDER BY trip_ct DESC LIMIT 1