How to create a Temporary table in Bigquery

I’ve been trying to build up a Temporary Table with both CREATE TEMPORARY TABLE and WITH statements in the following query. So far I couldn’t get any positive result and I don’t know what’s going on. In last line, the error alert says: “Syntax error: Expected “(” or “,” or keyword SELECT but got end of script at [28:62]”

WITH analysis_fall_2021_season AS
(SELECT
start_station_name,
end_station_name,
EXTRACT (DATE FROM started_at) AS start_date,
EXTRACT (DATE FROM ended_at) AS end_date, 
EXTRACT (TIME FROM started_at) AS start_time,
EXTRACT (TIME FROM ended_at) AS end_time,
DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
member_casual
FROM 
(SELECT
fall_analysis.ride_id, 
fall_analysis.started_at, 
fall_analysis.ended_at, 
fall_analysis.start_station_name, 
fall_analysis.end_station_name, 
fall_analysis.member_casual
FROM 
`ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
INNER JOIN 
`ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011
ON 
fall_analysis.member_casual = fall_202011.member_casual
INNER JOIN 
`ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012
ON 
fall_analysis.member_casual = fall_202012.member_casual))

Answer

As suggested by @Paul, You get this error because a CTE (i.e. the WITH statement) is only part of a query. It needs to be followed by another statement, usually a SELECT. You can also refer to this StackOverflow question where the same error has been discussed.

You can try the below code which I have modified accordingly to resolve the error.

WITH analysis_fall_2021_season AS
(SELECT
start_station_name,
end_station_name,
EXTRACT (DATE FROM started_at) AS start_date,
EXTRACT (DATE FROM ended_at) AS end_date,
EXTRACT (TIME FROM started_at) AS start_time,
EXTRACT (TIME FROM ended_at) AS end_time,
DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
member_casual
FROM
(SELECT
fall_analysis.ride_id,
fall_analysis.started_at,
fall_analysis.ended_at,
fall_analysis.start_station_name,
fall_analysis.end_station_name,
fall_analysis.member_casual
FROM
`ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
INNER JOIN
`ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011
ON
fall_analysis.member_casual = fall_202011.member_casual
INNER JOIN
`ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012
ON
fall_analysis.member_casual = fall_202012.member_casual)
)
SELECT * FROM analysis_fall_2021_season;